14

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.

Vogel612
  • 283
  • 1
  • 2
  • 11
  • 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. – András Váczi 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 01 '15 at 01:15
  • @CraigRinger Sorry for the wait, I edited the question to include what I did and the error messages I recieved – Vogel612 Apr 01 '15 at 07: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. – András Váczi Apr 01 '15 at 08:17
  • So the plan is, creating a table that is structured just like table, but without the triggers and constraints, restore the backup to there and then run an UPDATE over all recoreds? This should work.. – Vogel612 Apr 01 '15 at 08:23

2 Answers2

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 ;)

Community
  • 1
Vogel612
  • 283
  • 1
  • 2
  • 11
3

Prepend this line to the .sql data dump:

set session_replication_role = replica;

and psql shouldn't complain on restore.

Ivan Kozik
  • 131
  • 3