Deterministic PostgreSQL restores with TOC lists and zero-downtime cutover

This is Part 2 of the staging-first restore workflow. If you have not read Part 1, start with Why large PostgreSQL restores fail on live systems. In this article I focus on the mechanics: how to make restores deterministic, repeatable, and safe for production. The guiding constraints are simple:

• Do not touch production sequences.
• Do not assume a restore can resume.
• Do not move data twice.

Once you commit to those, the workflow becomes a set of controlled, observable steps.

Treat pg_restore as a SQL stream

The most useful feature of pg_restore is the ability to emit SQL instead of executing it. That turns it into a stream generator:

pg_restore --data-only -f - dumpfile

From that point on, you decide what gets executed and where. This is the foundation for deterministic restores.

Redirect data into staging

The SQL stream contains fully qualified COPY statements. That makes targeted rewrites safe:

pg_restore --data-only -f - dumpfile \
| sed 's/public.reading/restore_staging.reading/' \
| psql ...

This redirects all data into staging tables without modifying the dump. The stream stays reviewable and repeatable.

Why SEQUENCE SET is dangerous

A dump includes TOC entries like this:

TABLE DATA public reading
SEQUENCE SET public.reading_id_seq

If executed, SEQUENCE SET will rewind or advance production sequences. That breaks live inserts in subtle ways and is easy to miss because the restore still “succeeds.” This is a hard stop for any restore on a live system.

Use a TOC list to control exactly what runs

Generate a TOC list, edit it, and restore only TABLE DATA entries:

pg_restore -l dumpfile > toc.list

Remove all SEQUENCE SET entries. Keep only the tables you intend to restore. Then run:

pg_restore --data-only -L toc_tables_only.list -f - dumpfile \
| sed 's/public.table/restore_staging.table/' \
| psql ...

This guarantees sequences are untouched and the restore is deterministic because you control exactly what is included. I keep the filtered TOC list alongside the runbook so it is reviewable.

Validate before you promote

Once data is in staging, validate it as if it were production. I run row counts, spot-check ranges, and verify a few critical queries. The goal is not perfect proof; the goal is to catch obvious errors before cutover. If you discover a problem after promotion, the rollback path is harder.

A small, explicit validation step makes the rest of the workflow feel calm. You know whether the data is usable before you touch production tables.

Restoring multiple tables in one stream

The same pattern scales to directory-format dumps with many tables. Filter the TOC to the exact TABLE DATA entries you want and rewrite each table name in the stream. Add one -e rule per table you intend to restore:

pg_restore --data-only -L readings.list -f - dumpdir \
| sed \
  -e 's/public.reading/restore_staging.reading/' \
| psql ...

It is fast, predictable, and easy to re-run if you drop the staging schema.

Observability during COPY

During a long COPY, row counts appear as zero because rows are invisible until commit. That is expected and it trips up teams. I call it out in the runbook so no one stops a healthy restore. For progress, use pg_stat_progress_copy and relation size growth:

SELECT * FROM pg_stat_progress_copy;

If progress stalls, look at I/O and WAL throughput first, not locks.

Zero-downtime cutover

Once staging is fully restored and validated, cutover should be metadata-only:

ALTER TABLE public.reading
RENAME TO reading_tomigrate;

ALTER TABLE restore_staging.reading
SET SCHEMA public;

These operations are fast even for large tables. Heavy work happens in staging; cutover is a small, explicit step.

Sequence safety after cutover

Even though you removed SEQUENCE SET entries, you still need to confirm that production sequences are correct after cutover. I usually compare max(id) in the new table to the sequence value and advance it if needed. That is a quick check that prevents the first insert from failing at the worst possible moment.

If you keep this as a checklist item, you avoid the most annoying class of post-cutover surprises.

Legacy tables without primary keys

If the old table has no primary key or indexes, add them after cutover to avoid blocking live traffic:

CREATE UNIQUE INDEX CONCURRENTLY reading_tomigrate_id_uq
ON public.reading_tomigrate (id);

ALTER TABLE public.reading_tomigrate
ADD CONSTRAINT reading_tomigrate_pkey
PRIMARY KEY USING INDEX reading_tomigrate_id_uq;

Do this only if you have a real natural key. If you do not, solve that first.

Pitfalls to avoid

• Restoring SEQUENCE SET entries that corrupt production sequences.
• Rewriting the SQL stream too broadly and changing unintended identifiers.
• Treating invisible rows during COPY as a failure signal.
• Assuming a failed restore can be resumed without cleanup.

Checklist

• Generate a TOC list and keep only the intended TABLE DATA entries.
• Remove all SEQUENCE SET entries from the plan.
• Stream pg_restore with --data-only -f - and rewrite into restore_staging.
• Validate staging tables before cutover.
• Cut over using metadata-only operations like ALTER TABLE ... RENAME and SET SCHEMA.

Related links

• Predictable cutovers with runbooks, rehearsals, and rollback
• Observability during migrations: metrics that matter first
• Infrastructure as code conversion for existing stacks
• Why large PostgreSQL restores fail on live systems