8

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

namely:

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:

ALTER TABLE table DISABLE TRIGGER ALL

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:

BEGIN TRANSACTION READ WRITE;
TRUNCATE TABLE table;

-- copy here

COMMIT;

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.

migrated from stackoverflow.com Mar 31 '15 at 15:43

This question came from our site for professional and enthusiast programmers.

  • 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. – dezso Mar 31 '15 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 '15 at 16:17
  • @Vogel612 "invalid commands" ? Show the exact errors please. – Craig Ringer Apr 1 '15 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 '15 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. – dezso Apr 1 '15 at 8:17
4

@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:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

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

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

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

0

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, privacy policy and cookie policy

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