PGCOPYDB(5)

💖 PGCOPYDB(5) — like, the whole manual for pgcopydb config 💖

okay bestie, hi, hello 🫶 this is the manual page for how you configure pgcopydb. It's from the pgcopydb manual family, so keep that name in your head.

so here's the tea: the pgcopydb command itself takes sub-commands and command line options — go read the manual for those commands if that's what you need, bestie. The only setup that pgcopydb commands accept is the filtering. that's it. that's the whole vibe of this page. ✨

---

🎀 FILTERING 🎀

Filtering is how you ✨skip✨ some object definitions and data while copying from the source to the target database. The pgcopydb commands that accept the option --filter (or --filters) want an existing filename as the option argument. That filename gets read in the INI file format, but — and this is the important part — it only uses sections and option keys. Option values are simply not used. periodt. 💅

Here's an inclusion based filter configuration example:

[include-only-schema]
public
~/^audit_/

[include-only-table]
public.allcols
public.csv
public.serial
public.xzero

[exclude-index]
public.foo_gin_tsvector

[exclude-table-data]
public.csv

And here's an exclusion based filter configuration example:

[exclude-schema]
foo
bar
expected

[exclude-table]
"schema"."name"
schema.othername
err.errors
public.~/^tmp_/

[exclude-index]
schema.indexname

[exclude-table-data]
public.bar
nsitra.test1

[exclude-extension]
aiven_extras

🧚 Pattern matching

In any filter section that accepts schema-qualified names, each name component (schema or table) can be written as a regular-expression pattern using the syntax ~/pattern/ instead of a literal name. The ~/ prefix and the trailing / are the delimiters; everything between them is a POSIX extended regular expression, matched with regcomp(3) / regexec(3).

Patterns can show up as the schema part, the name part, or literally both. obsessed:

[exclude-table]
; exact schema, regex table — excludes all tables starting with "tmp_" in public
public.~/^tmp_/

; regex schema, exact table — excludes "audit_log" in any schema starting with "log"
~/^log/.audit_log

; regex schema, regex table — excludes any table ending with "_bak" in any "archive" schema
~/.archive/.~/_bak$/

For [exclude-schema] and [include-only-schema], entries are bare schema names and the whole entry is treated as the pattern:

[exclude-schema]
; excludes every schema whose name starts with "tmp_"
~/^tmp_/

[include-only-schema]
public
; also include every schema whose name matches "audit_YYYY" (four digits)
~/^audit_[0-9]{4}$/

A pattern is matched against each object name from the source database catalog. Entries that resolve to zero matching objects are silently ignored — pgcopydb does not treat an unmatched pattern as an error. no drama, no error message, it just moves on. 🙈

Filtering can be done with pgcopydb by using the following rules, which are also the names of the sections of the INI file. 🥰

🌸 include-only-table

This section lets you list the exclusive list of source tables to copy to the target database. No other table will be processed by pgcopydb. like, none. ✋

Each line in that section should be a schema-qualified table name. The Postgres identifier quoting rules (<https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>) can be used to avoid ambiguity. Either the schema component or the table component (or both) may be a ~/pattern/ regex instead of a literal name; see Pattern matching above. 👆

When the section include-only-table is used in the filtering configuration, then the sections exclude-schema and exclude-table are disallowed. Why? Because we would not know how to handle tables that exist on the source database and are not part of any filter. 🤷♀️

NOTE: Materialized views are also considered as tables during the filtering.

🌈 exclude-schema

This section lets you add schemas (Postgres namespaces) to the exclusion filters. All the tables that belong to any listed schema in this section are going to be ignored by the pgcopydb command. bye bye 👋

Each line is a bare schema name or a ~/pattern/ regex that is matched against schema names; see Pattern matching above.

This section is not allowed when the section include-only-table is used.

🦋 include-only-schema

This section restricts processing to the listed schemas: only tables that belong to one of the named schemas are copied; all others are skipped. It's a syntactic shortcut for excluding every other schema without having to list them all under exclude-schema. main character energy, honestly. 👑

Despite the name, this section is implemented as an exclusion filter. yes bestie, the name is a little bit of a lie, iykyk.

Each line is a bare schema name or a ~/pattern/ regex that is matched against schema names; see Pattern matching above.

This section is not allowed when the section exclude-schema is used.

💜 exclude-table

This section lets you add a list of qualified table names to the exclusion filters. All the tables listed in the exclude-table section are going to be ignored by the pgcopydb command.

Each line is a schema-qualified table name. Either the schema component or the table component (or both) may be a ~/pattern/ regex; see Pattern matching above.

This section is not allowed when the section include-only-table is used.

NOTE: Materialized views are also considered as tables during the filtering.

⭐ exclude-index

This section lets you add a list of qualified index names to the exclusion filters. It's then possible for pgcopydb to operate on a table and skip a single index definition that belongs to a table that is still processed. iconic behavior, honestly.

Each line is a schema-qualified index name. Either the schema component or the index-name component (or both) may be a ~/pattern/ regex; see Pattern matching above.

🍓 exclude-table-data

This section lets you skip copying the data from a list of qualified table names. The schema, index, constraints, etc. of the table are still copied over. so like, the table structure stays, only the rows ghost you. 👻

Each line is a schema-qualified table name. Either the schema component or the table component (or both) may be a ~/pattern/ regex; see Pattern matching above.

NOTE: Materialized views are also considered as tables during the filtering.

🧁 exclude-extension

This section lets you skip specific PostgreSQL extensions by name. The listed extensions are not created on the target database and their configuration tables are not copied.

Each entry is a bare extension name (no schema prefix), one per line:

[exclude-extension]
aiven_extras
timescaledb

This section is not allowed when the section include-only-extension is used at the same time.

💅 include-only-extension

This section restricts extension handling to only the listed extensions. All other extensions found in the source database are skipped — they are not created on the target and their configuration tables are not copied.

Each entry is a bare extension name (no schema prefix), one per line:

[include-only-extension]
postgis
uuid-ossp

This section is not allowed when the section exclude-extension is used at the same time, and it cannot be combined with --skip-extensions (which skips all extensions). pick a lane bestie 😌

---

🔍 REVIEWING AND DEBUGGING THE FILTERS 🔍

Filtering a pg_restore archive file is done through rewriting the archive catalog obtained with pg_restore --list. That's a little hackish at times — ngl — and we also have to deal with dependencies in pgcopydb itself.

The following commands can be used to explore a set of filtering rules:

• pgcopydb list depends <#pgcopydb-list-depends>
• pgcopydb restore parse-list <#pgcopydb-restore-parse-list>

---

👑 Author

Dimitri Fontaine

📜 Copyright

2022-2024, Dimitri Fontaine

---

and that's the whole thing bestie, you ate it up 💖💅✨ this was version 0.18 of pgcopydb, dated Jun 28, 2026 — mwah, go filter something iconic 🎀🦋🌸🫶