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.