Copying a PostgreSQL customer database to staging, how do you skip a huge log table's rows and control what pg_restore restores with --filter, --section and --list?
answer
- keep the table, drop its rows
- a filter file instead of long flags
- edit the table of contents
- pre-data, data, post-data
- pg_restore -t is narrower than pg_dump -t
basics
~20 sDump with --exclude-table-data (or a --filter file line exclude table_data) to keep the log table's definition without rows. Then edit pg_restore -l output and replay it with -L, or restore pre-data, data and post-data sections separately.
solid answer
~50 sShape the dump first. `pg_dump --exclude-table-data=crm.activity_log` keeps the table, its indexes and its constraints but no rows, so code that queries it still works. From PostgreSQL 17, the same rule can live in a `--filter` file as `exclude table_data crm.activity_log`. At restore time, `pg_restore -l` prints the archive's table of contents. You comment items out with a leading `;`, delete or reorder lines, and `pg_restore -L` restores only what is left, in that order. `--section` splits the restore into **pre-data** (definitions), **data** (rows, sequence values, and, in 18 dumps made with `--statistics`, table statistics) and **post-data** (indexes, triggers, most constraints). That lets you load rows, mask personal data, and only then build indexes and constraints. Beware `pg_restore -t`: it takes no wildcards or schema-qualified names, and it brings neither the table's indexes nor its dependencies.
code
bash · 13 lines# Dump: keep crm.activity_log's definition, drop its rows
cat > staging.filter <<'EOF'
# rows staging never needs
exclude table_data crm.activity_log
exclude schema scratch
EOF
pg_dump -h prod-pg -Fc --filter=staging.filter -f cust_harbor.dump cust_harbor
# Restore in phases, masking before indexes and constraints exist
pg_restore -d cust_harbor --section=pre-data cust_harbor.dump
pg_restore -d cust_harbor --section=data -j 4 cust_harbor.dump
psql -X -d cust_harbor -f mask_contacts.sql
pg_restore -d cust_harbor --section=post-data -j 4 cust_harbor.dumpgo deeper
Know that pg_dump can exclude a table's rows while keeping its definition, and that pg_restore can list and pick archive items.
Explain the -l/-L workflow, the three sections and what each holds, and the filter-file syntax with its include-only and exclude-only object types.
Build a staging refresh that skips bulk history, masks data between the data and post-data sections, and anticipates unique-index failures from careless masking.
Turn the refresh into a reviewed, versioned pipeline (filter file, TOC edits, masking) so that staging data is both useful and safe to hand to developers.
## The staging problem You copy the customer database `cust_harbor` from production to staging. Its `crm.activity_log` table holds years of history that staging does not need, and its contact tables hold personal data that must be masked before developers query them. `pg_dump` and `pg_restore` give you three levers: 1. what goes **into** the dump; 2. which archive **items** are restored, and in what order; 3. in which **phases** the restore runs. ## Lever 1: shape the dump - `--exclude-table-data=crm.activity_log` keeps the `CREATE TABLE`, its indexes and constraints, but no rows. Views and code that reference the table keep working. - `--exclude-table-data-and-children` does the same for a partitioned table and all its partitions. - `-T`/`--exclude-table` drops the table entirely. A view that reads it then fails to restore, so prefer excluding only the data. - `-t pattern` dumps only matching tables, with their indexes, but makes no attempt to dump the objects those tables depend on. A table-only dump is not guaranteed to restore into a clean database. Since PostgreSQL 17, a **`--filter` file** holds these rules, one per line, as `include|exclude`, an object type and a pattern. `pg_dump` accepts the types `table`, `table_and_children`, `table_data`, `table_data_and_children`, `schema`, `extension` and `foreign_data`. `table_data` and `table_data_and_children` only work with `exclude`, and `foreign_data` only with `include`. Lines starting with `#` are comments. The filter file keeps long lists out of the command line and can be versioned with the refresh script. `pg_dumpall --filter` accepts only `exclude database` lines. ## Lever 2: choose items with -l and -L Every archive carries a **table of contents** (TOC): 1. `pg_restore -l cust_harbor.dump > toc.list` prints a header plus one line per item, giving its archive ID, its type (`TABLE`, `TABLE DATA`, `SEQUENCE SET`, `INDEX`, `FK CONSTRAINT`, ...), schema, name and owner. 2. Edit the file. Put `;` at the start of a line to skip that item, delete lines, or move them. 3. `pg_restore -L toc.list -d cust_harbor cust_harbor.dump` restores only the listed items, in the order of the file. Filtering switches such as `-n` or `-t` narrow it further. This works on an archive you already have. For example, you can skip the `TABLE DATA` line of `crm.activity_log` from last night's full dump without dumping again. One limit: large objects cannot be restored selectively; you get all of them or none. `pg_restore`'s own selectors are narrower than they look: - `pg_restore -t name` takes no wildcards and no schema qualification (combine it with `-n crm`). It restores only the table's definition and data, not its indexes, and nothing the table depends on. - `pg_restore --filter` accepts `function`, `index`, `table` and `trigger` only with `include`, and `schema` with either keyword. It cannot exclude a table's data; use `-L` for that, or exclude at dump time. ## Lever 3: restore in sections Every archive item belongs to one of three sections: | Section | Contains | |---|---| | `pre-data` | all other definitions: schemas, types, functions, tables without their rows | | `data` | table rows, large-object contents, sequence values, and on 18 table statistics when the dump has them | | `post-data` | indexes, triggers, rules, constraints other than validated CHECK and NOT NULL ones, and on 18 index statistics when the dump has them | A staging refresh with masking: 1. `pg_restore --section=pre-data -d cust_harbor cust_harbor.dump` 2. `pg_restore --section=data -j 4 -d cust_harbor cust_harbor.dump` 3. Run the masking `UPDATE`s. No indexes, triggers or foreign keys exist yet, so the updates are cheaper and no audit trigger fires. 4. `pg_restore --section=post-data -j 4 -d cust_harbor cust_harbor.dump` The trade-off: unique indexes and primary keys are built only in step 4. A masking rule that maps two emails to the same value surfaces there as a failed `CREATE UNIQUE INDEX` or `ADD CONSTRAINT`, not in step 3. Mask with values that stay unique. ## What to say in an interview - Exclude **data**, not tables, unless nothing references the table. - Use `-l`/`-L` on an existing archive, and `--filter` for rules you want versioned with the script. - Use sections when work must happen between the data load and the index build.
- Why not restore one table into staging with pg_restore -t crm.accounts?`pg_restore -t` takes neither a schema-qualified name nor a wildcard, so you write `-n crm -t accounts`. It restores only the table's definition and data. Its indexes, constraints and triggers do not come along, and neither does anything it depends on. `pg_dump -t` brings the table's indexes, and a `-L` list that keeps the table's `INDEX` and `CONSTRAINT` lines restores the whole set.
- What goes wrong if you exclude the log table with -T instead of excluding only its data?The table's definition is then missing from the dump. A view or function that references it may fail to restore, and application code that writes to it fails at runtime. `--exclude-table-data` keeps the empty table, its indexes and its constraints, which is almost always what a staging copy needs.
The archive's table of contents is like a printer's job list for a book: you can strike out chapters with a semicolon, move them, and the printer prints only what remains, in the new order.
- pg_restore -t restores the table together with its indexes.
- A pg_dump --filter file can include table_data lines.
- Excluding the table with -T is the same as excluding its data.
- pg_restore can restore large objects for just one table.
- In a pg_restore -l list, a leading # comments out an item.
Assumes PostgreSQL 18; --filter for pg_dump, pg_dumpall and pg_restore arrived in 17, and the statistics entries in the data and post-data sections exist only in 18 dumps made with --statistics.