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
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.