Hi,
We are using PostgresSQL 9.5.10 and pg_dump/pg_restore to export and import database.
We encountered an issue (which is not easily reproducible) when running pg_restore:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3624; 2606 37504 FK CONSTRAINT <fk_name> postgres
pg_restore: [archiver (db)] could not execute query: ERROR: insert or update on table "<table_name>" violates foreign key constraint "fk_name"
DETAIL: Key (<column_name>)=(fbc3dd78-643f-419e-9f0f-72d81ef22cf3) is not present in table "<table_name>".
Command was: ALTER TABLE ONLY <table_name> ADD CONSTRAINT <fk_name> FOREIGN KEY
The following command was used to backup the database:
postgresql-9.5.10-2-windows-x64-binaries\pgsql\bin\pg_dump.exe -Fc --verbose --no-password -U postgres -d, postgres -f db.dump -E utf8
The following command was used to restore the database:
postgresql-9.5.10-2-windows-x64-binaries\pgsql\bin\pg_restore.exe --verbose --no-password --single-transaction --no-owner --no-privileges -U postgres -d postgres db.dump
After playing with the dump and importing schema first and data next without the triggers we indeed see that data is missing in the table i.e. dump is not consistent.
We don't stop the application which uses database during the dump but according to the documentation the dump still should be consistent.
How is it possible that pg_dump created dump which is not consistent? Did it happen because we haven't stopped the application?
Is there any way to create dumps without stopping the application?
Thanks,
Olga