Seedfast

Seedfast

All posts

The Dump That Breaks Its Own Restore

Mikhail ShytskoBy Mikhail Shytsko, Founder at Seedfast ·

Share
Open in ChatGPT

A pg_dump data-only restore into a fresh, empty copy of the schema is the safest-sounding restore in PostgreSQL, since there is not a single row in the target for the incoming data to collide with. It stops anyway, on a table sitting plainly in \dt.

psql:/tmp/data.sql:56: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...

book_audit exists. The statement that cannot see it never appears in the dump, because it lives inside an AFTER INSERT trigger on books, and the reason a bare table name stops resolving is one line the dump wrote at the top of itself before any data moved.

SELECT pg_catalog.set_config('search_path', '', false);

That pins search_path to the empty string for the rest of the session, and every unqualified table reference inside a trigger body goes down with it. The dump breaks its own restore.

A pg_dump data-only restore failing on the pinned search_path preamble, where the trigger on books cannot resolve the unqualified name book_audit and the whole COPY rolls back

Folklore warns about a different failure here, the one where the trigger fires successfully during the books load and collides with the audit rows the dump is putting back. That failure is real. You meet it second, and only in a session whose triggers can still resolve their own tables.

Reach for one of three levers at this point. --disable-triggers bakes trigger suspension into the file, one transaction with SET CONSTRAINTS ALL DEFERRED fixes load order, and session_replication_role = replica switches enforcement off for the session. Two of the three bill you in a currency nobody mentions until the restore is half done.

Key Takeaways

  • The dump's own preamble pins search_path to the empty string, so a trigger function that names its own tables without a schema breaks the restore with relation ... does not exist, even into a target holding zero rows.
  • A trigger error rolls back the entire COPY it interrupted, leaving that table at zero rows while every other table in the file loads normally around it.
  • The trailing setval calls fire whether or not the matching table loaded, so a sequence can sit at 5 above a table holding nothing.

Everything below was run against PostgreSQL 18.6 in throwaway containers on 2026-08-27 and 28, and the outputs are pasted as they came back.

What is inside a data-only dump

Strip the DDL out and what remains is shorter than most people picture. Five tables produce a preamble of SET statements, one COPY ... FROM stdin block per table, and one setval per sequence at the bottom. The preamble's decisive line sits in the middle of ten lines of housekeeping.

SET statement_timeout = 0;
SET lock_timeout = 0;
...
SELECT pg_catalog.set_config('search_path', '', false);
...
SET row_security = off;

Table order looks alphabetical often enough that people assume it is. Build a schema where the two orders disagree and the question settles itself.

CREATE TABLE zz_parents (id bigserial PRIMARY KEY, name text);
CREATE TABLE aa_children (id bigserial PRIMARY KEY, parent_id bigint REFERENCES zz_parents(id));

pg_dump --data-only emits zz_parents first and aa_children after it, parent ahead of child, straight against the alphabet. The SEQUENCE SET statements at the foot of that same file do come out alphabetically, aa_children_id_seq before zz_parents_id_seq, since nothing connects one sequence to another for a dependency sort to work on.

Every sequence gets exactly one line, and the shape never varies.

SELECT pg_catalog.setval('public.books_id_seq', 5, true);

Nothing there is conditional and nothing consults the table. In all five the third argument came back true. Data arrives as text COPY rather than INSERT unless you ask otherwise. The export side of all this, the --table and --exclude-table-data flags that carve a slice out of a larger database, is covered in the PostgreSQL test data cookbook.

Why the pg_dump data-only restore breaks on a trigger

The test schema is three tables and one trigger, roughly what every application grows within its first year.

CREATE TABLE authors (id bigserial PRIMARY KEY, name text NOT NULL);
CREATE TABLE books (id bigserial PRIMARY KEY, author_id bigint NOT NULL REFERENCES authors(id), title text NOT NULL);
CREATE TABLE book_audit (id bigserial PRIMARY KEY, book_id bigint, note text, logged_at timestamptz DEFAULT now());

CREATE FUNCTION log_book() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen'); RETURN NEW; END $$;

CREATE TRIGGER trg_book_audit AFTER INSERT ON books FOR EACH ROW EXECUTE FUNCTION log_book();

Three authors and five books go in by hand, the trigger writes five book_audit rows of its own, and pg_dump --data-only picks up all thirteen without distinguishing which rows a person typed and which a trigger produced. It only ever sees table contents. Restored into a brand new database carrying the identical schema and no rows whatsoever, the transcript reads like this.

COPY 3
COPY 5
psql:/tmp/data.sql:56: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...
                    ^
QUERY:  INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen')
CONTEXT:  PL/pgSQL function public.log_book() line 1 at SQL statement
COPY 3
COPY 3

Afterwards authors holds 3, book_audit holds exactly the 5 dumped rows with no doubling anywhere, and books holds nothing at all. Emptiness rather than a partial load is the giveaway that a COPY is a single statement; the trigger threw on the first of the five rows and took the statement down with it. Two tables further along, gadgets and widgets loaded fine, because neither carries a trigger and neither cared.

Pinning search_path by hand reproduces the whole thing with no dump involved.

SELECT pg_catalog.set_config('search_path', '', false);
INSERT INTO books (author_id, title) VALUES (1, 'x');

ERROR:  relation "books" does not exist
LINE 1: INSERT INTO books (author_id, title) VALUES (1, 'x');
                    ^

With search_path empty a bare name resolves against nothing, and log_book() writes INSERT INTO book_audit(...) rather than INSERT INTO public.book_audit(...), a spelling no environment complains about until an empty search path arrives.

Restore the search path and the anticipated failure turns up right where the folklore says it will. This is the same instant mid-restore, sequence still un-advanced because the setval lines live at the bottom of the file.

SET search_path = public;
INSERT INTO books (author_id, title) VALUES (1, 'x');

ERROR:  duplicate key value violates unique constraint "book_audit_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen')"
PL/pgSQL function log_book() line 1 at SQL statement

A trigger function disciplined enough to schema-qualify its own tables would clear the preamble and meet this duplicate key instead. Switching the dump to --inserts or to the custom archive changes none of it either; the same trigger error comes back one row at a time instead of once per table.

Restoring into a schema that already has rows

Staging refreshes rarely run against an empty database, and a populated target changes which failure arrives first. This target starts with one author and one book already in place, which also means book_audit holds one row that its own trigger wrote. The same file goes in again with no flags at all.

psql:/tmp/data.sql:30: ERROR:  duplicate key value violates unique constraint "authors_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY authors, line 1
psql:/tmp/data.sql:43: ERROR:  duplicate key value violates unique constraint "book_audit_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY book_audit, line 1
psql:/tmp/data.sql:56: ERROR:  duplicate key value violates unique constraint "books_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY books, line 1
COPY 3
COPY 3

Three tables kept the single row they came in with, gadgets and widgets took all three of theirs because nothing stood in the way, and the database now holds a mixture matching neither the source nor the target. Each incoming COPY died on its primary-key collision before any trigger could weigh in, which is why the search_path error is absent from this transcript.

Reset to the identical starting state and add --single-transaction.

psql:/tmp/data.sql:30: ERROR:  duplicate key value violates unique constraint "authors_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY authors, line 1
psql:/tmp/data.sql:37: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:/tmp/data.sql:50: ERROR:  current transaction is aborted, commands ignored until end of transaction block

Nothing survived that run. gadgets and widgets rolled back with the rest even though both had loaded cleanly a moment earlier under the other mode, which is the outcome you actually want, since a target left exactly as it was needs no untangling by hand.

Both runs share one number worth staring at. psql exited 0 for each of them, and zero held for every plain-format run in this report that left -v ON_ERROR_STOP off, which means a CI step gating on $? will happily pass a restore that loaded nothing. Tell psql to care with -v ON_ERROR_STOP=1 and a script failing the same way stops at the first error, exiting 3 instead of 0. The custom format never needed the telling.

pg_restore: error: COPY failed for table "books": ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...
                    ^
QUERY:  INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen')
CONTEXT:  PL/pgSQL function public.log_book() line 1 at SQL statement
pg_restore: warning: errors ignored on restore: 1

pg_restore exits 1 there and reports how many errors it ignored, so its status is worth branching on. The archive format carries a second advantage that only shows up once a dump already exists. The file above was produced without --disable-triggers, and pg_restore --data-only --disable-triggers applied trigger suspension to it anyway, at restore time, with no re-dump and no access to the source database. A plain SQL script offers no equivalent, because there the ALTER TABLE ... DISABLE TRIGGER statements are static text that pg_dump either wrote or did not.

The setval lines do not check anything

Those trailing statements run with no conditions attached. Look at the sequence behind books after the failed restore from earlier, the one where the trigger emptied the table.

 books_rows
------------
          0
(1 row)

 last_value | is_called
------------+-----------
          5 | t

The table holds nothing while its sequence is confident that five ids have gone out. Nothing surfaces the disagreement, and the next insert that lets Postgres pick an id returns 6, so the table's first real row lands at 6 and the gap stays there until somebody writes a test assuming ids start at 1. Where the restore completes, the same lines land every sequence correctly, and plain inserts carry on past the loaded data with no duplicate key anywhere. The opposite failure, a load that writes explicit ids with no setval following it, leaves the sequence behind the table instead of ahead of it, and that one is the out-of-sync sequence.

Three mitigations and the price of each

Two mitigations for a pg_dump data-only restore compared side by side, --disable-triggers against a single transaction with SET CONSTRAINTS ALL DEFERRED, with what each one fixes and what it costs

--disable-triggers and the ownership it needs

pg_dump --data-only --disable-triggers wraps every table's COPY in a pair of ALTER TABLE statements.

SET SESSION AUTHORIZATION DEFAULT;

ALTER TABLE public.authors DISABLE TRIGGER ALL;

COPY public.authors (id, name) FROM stdin;
1	Ursula K. Le Guin
2	Octavia E. Butler
3	Ted Chiang
\.


ALTER TABLE public.authors ENABLE TRIGGER ALL;

SET SESSION AUTHORIZATION DEFAULT shows up once, ahead of the first DISABLE TRIGGER, not per table. Run as the superuser into an empty schema this restores cleanly, books takes all five rows, and book_audit ends with exactly the five that were dumped, because the trigger body never executes and so never gets the chance to double anything.

Run the same file as the role your pipeline actually uses and the flag comes apart quietly. The role below owns nothing and holds INSERT on all five tables.

psql:/tmp/data-dt.sql:28: ERROR:  must be owner of table authors
COPY 3
psql:/tmp/data-dt.sql:37: ERROR:  must be owner of table authors
psql:/tmp/data-dt.sql:43: ERROR:  must be owner of table book_audit
COPY 5
psql:/tmp/data-dt.sql:54: ERROR:  must be owner of table book_audit
psql:/tmp/data-dt.sql:60: ERROR:  must be owner of table books
psql:/tmp/data-dt.sql:68: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...
                    ^
QUERY:  INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen')
CONTEXT:  PL/pgSQL function public.log_book() line 1 at SQL statement
psql:/tmp/data-dt.sql:71: ERROR:  must be owner of table books
psql:/tmp/data-dt.sql:107: ERROR:  permission denied for sequence authors_id_seq

Read the must be owner of table books line together with the one under it. The disable was refused, the COPY that followed ran anyway with the trigger fully armed, and back came the exact error the flag exists to prevent. INSERT is not enough, and nothing short of table ownership or superuser will do it.

Denied setval calls leave their own residue on the way out. authors finished with three rows while authors_id_seq stayed at last_value=1, is_called=f, so the next insert without an explicit id asks for 1 and collides with a row already sitting there.

Two spellings of the disable exist, and the gap between them is wider than the words suggest. Both statements below run against a books table whose author_id references authors, using an author id that does not exist.

ALTER TABLE books DISABLE TRIGGER USER;
INSERT INTO books (id, author_id, title) VALUES (99, 999, 'orphan via USER-disabled trigger');

ERROR:  insert or update on table "books" violates foreign key constraint "books_author_id_fkey"
DETAIL:  Key (author_id)=(999) is not present in table "authors".

ALTER TABLE books DISABLE TRIGGER ALL;
INSERT INTO books (id, author_id, title) VALUES (98, 998, 'orphan via ALL-disabled trigger');

INSERT 0 1

Author 998 does not exist either. Foreign-key checks are internal system triggers, which USER does not touch, so the first orphan is correctly rejected. Suspend ALL and the second one lands. Since ALL is what pg_dump --disable-triggers emits, the flag will load rows in an order the constraints would refuse, and an inconsistent dump goes in as quietly as a consistent one because nothing checks it on the way through.

One transaction with constraints deferred

Ordering, not triggers, is what the second lever addresses, and declaring a foreign key DEFERRABLE accomplishes nothing on its own. Hand-shuffling a dump so the books block sits above the authors block shows why.

psql:/tmp/shuffled.sql:4: ERROR:  insert or update on table "books" violates foreign key constraint "books_author_id_fkey"
DETAIL:  Key (author_id)=(1) is not present in table "authors".
COPY 1

INITIALLY IMMEDIATE still checks at the end of each statement, so books ends at 0 and authors at 1. Wrap the same shuffled file in a transaction that actually defers, and it goes through.

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- COPY public.books ... (out of order, on purpose)
-- COPY public.authors ...
COMMIT;

BEGIN
SET CONSTRAINTS
COPY 2
COPY 1
COMMIT

Two books and one author went in backwards and came out correct. What deferring leaves entirely alone is triggers. A row count taken mid-transaction, before authors has received a single row and long before COMMIT, already finds two audit rows waiting.

BEGIN
SET CONSTRAINTS
COPY 2
 audit_rows_seen_before_authors_loaded_and_before_commit
-----------------------------------------------------------
                                                         2
COPY 1

An ordinary AFTER INSERT trigger fires at the end of its own statement and has no relationship with constraint timing whatsoever. Now put both problems in one file, which is what a real dump hands you, by applying the actual preamble on top of the shuffled and deferred script.

BEGIN
SET CONSTRAINTS
psql:/tmp/shuffled_realistic.sql:7: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...
                    ^
QUERY:  INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'insert seen')
CONTEXT:  PL/pgSQL function public.log_book() line 1 at SQL statement
psql:/tmp/shuffled_realistic.sql:8: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The last word in that transcript is ROLLBACK and the script said COMMIT, because Postgres demotes the commit of a failed transaction. Every table ends at zero, including authors, which had no trigger, no ordering problem, and nothing to do with any of it. Under plain autocommit the same trigger failure cost one table. Deferring constraints requires one transaction around the entire restore, and that transaction is what hands a single trigger error the authority to discard tables it never went near. The deferrable machinery itself, along with the two other ways out of a cyclic schema, gets a full walkthrough in the circular foreign key guide.

session_replication_role = replica

Nothing on the pg_dump command line reaches the third lever; it is a session parameter. In the container it let an insert whose author_id pointed at nobody straight through and kept the audit trigger silent, all without touching a single table definition, while the unprivileged role asking for the same thing was refused outright.

SET session_replication_role = replica;
ERROR:  permission denied to set parameter "session_replication_role"

An explicit GRANT SET ON PARAMETER session_replication_role lifts that refusal, and short of the grant it takes superuser. That privilege profile keeps the lever inside restore pipelines. Nobody sets it in a development shell, and a complete inventory of the parameter's reach deserves a bench of its own.

Circular foreign keys and a warning with exit code 0

Some schemas offer no valid load order at all, such as two tables that each hold a nullable foreign key to the other, with rows that genuinely cross-reference. pg_dump spots the cycle and says so on stderr.

pg_dump: warning: there are circular foreign-key constraints among these tables:
pg_dump: detail: cats
pg_dump: detail: dogs
pg_dump: hint: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump to avoid this problem.

That run's exit code is 0, so a pipeline checking the status will never learn the warning happened, and the file itself carries no marker either, just the usual COPY blocks with cats arbitrarily ahead of dogs because one of them has to go first. Restored into an empty copy of the schema with constraints immediate, both orderings lose and both tables finish at zero rows, since each table's first row needs the other already populated. Re-dumping with --disable-triggers and restoring as the owner put both back with their cross-references intact, which is the first of the two remedies pg_dump printed in its own hint.

Before you run the restore

One failure in this report is worth fixing in the code rather than working around at the command line. A trigger function that writes to a bare table name breaks any restore that leaves triggers armed, deferred constraints included, and schema-qualifying the function body is a five-character edit that retires the whole class. The two levers that suspend triggers dodge the error without fixing it.

The whole failure class also assumes a file has to travel between two databases, and it does not. Seedfast builds rows directly against the schema in front of it, and nothing that arrives that way brings a primary key or a sequence position along.

Frequently asked questions

Why does a pg_dump data-only restore fail with relation does not exist?

The empty search_path in pg_dump's own preamble is doing it. Any trigger function that names a table without its schema stops resolving for the length of the restore, and the error you get back names the table the trigger writes to rather than the one being loaded, which is why it reads like a missing object. Schema-qualify the function body, or suspend triggers for the load with --disable-triggers.

How do I restore a data-only dump into a database that already has data?

Expect a primary-key collision on every table whose ids overlap, and choose the failure mode before you start. Default psql keeps whatever managed to load and abandons the rest, which leaves a mixture belonging to neither database. Add --single-transaction and the first error rolls the whole file back, so the target ends where it started.

Does pg_restore report errors better than psql?

Yes. pg_restore exits 1 and prints errors ignored on restore: N, while psql -f exited 0 in every run here that left -v ON_ERROR_STOP off. With -v ON_ERROR_STOP=1 psql stops burying errors and a script failing the same way exited 3 at the first failure. The custom format also lets --disable-triggers be chosen at restore time against an archive dumped without it, which a plain SQL file cannot offer because its ALTER TABLE statements are static text.

How do I fix a sequence that is ahead of its table after a failed restore?

Point it back at the table's real maximum with setval, the same realignment any explicit-id load ends with. The dump's own trailing setval already ran and believed the dump, not the table, so the correction has to come from you. The full set of realignment recipes, including identity columns and whole-schema sweeps, lives in the out-of-sync sequence guide.

Related guides