I had to perform some tests with a short script to update some "legacy" data in one of my tables.

Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. Simplest way to do that was:

pg_dump -a --file table.sql -t table database

Now I did what I had to do, checked the results and found them rather unsatisfactory. I thought to myself: how lucky I am to have a backup of that table.

I had already been warned when I backed up the table that:

pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump:   table
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

I didn't think much of it, but now we have a problem. Indeed the table in question has multiple triggers attached to it, but I cannot restore the table.sql with option --disable-triggers of the pg_restore command.

If I try following command I get an error message:

pg_restore -a -d database -t table -h localhost --disable-triggers table.sql


pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Is there a flag for the psql-command that exhibits the same behavior as --disable-triggers?

I have already checked the psql "manpage", searching for trigger and similar keywords but didn't find anything.

Or is the only option I have to drop the triggers on the table before restoring the data?

Sidenote: I am using postgres v. 9.3 on a Ubuntu 14.10 System

It was suggested to edit the generated sql-file, to include the statement:


When I now executed: psql -d database -f table.sql I got an error message about violating the "Unique" constraint of the primary key.

To fix this I tried to wrap the copy into:


-- copy here


Now the error message is:

psql:project_backup.sql:18: ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "another" references "table".
HINT:  Truncate table "another" at the same time, or use TRUNCATE ... CASCADE.
psql:project_backup.sql:20: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:project_backup.sql:21: invalid command \N
psql:project_backup.sql:22: invalid command \N

The latter warning repeats for each \N (symbolizing the null value) in the dump.

  • 2
    You can go and edit your dump in any editor. Just prepend the COPY with an ALTER TABLE table DISABLE TRIGGER ALL and reenable these at the end. Mar 31, 2015 at 15:49
  • Prepending only the disable, I get an error stating the copy violates the unique constraint of the primary key. (Quite understandably) When I proceed to BEGIN TRANSACTION READ WRITE; TRUNCATE TABLE table; to have my data secure I get showered with messages about invalid commands :(
    – Vogel612
    Mar 31, 2015 at 16:17
  • @Vogel612 "invalid commands" ? Show the exact errors please. Apr 1, 2015 at 1:15
  • @CraigRinger Sorry for the wait, I edited the question to include what I did and the error messages I recieved
    – Vogel612
    Apr 1, 2015 at 7:34
  • All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data. Apr 1, 2015 at 8:17

2 Answers 2


@dezso had the completely right idea:

All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:


    (column1, column2, ...) = 
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

-- I didn't but you can drop table_backup here

So I am finally back with my original data, ready for the next testrun ;)


Prepend this line to the .sql data dump:

set session_replication_role = replica;

and psql shouldn't complain on restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.