Restoring plain-text pg_dump with psql and --disable-triggers

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


Prepend this line to the .sql data dump:

set session_replication_role = replica;

and psql shouldn't complain on restore.