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.
Best Answer
@dezso had the completely right idea:
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:
So I am finally back with my original data, ready for the next testrun ;)