On 10/12/2009 3:31 AM, CG wrote:
Hi all, We're using PostgreSQL 8.4 ... We do our nightly database backups with pg_dump. I was doing a test restore and I encountered some data during the reload that was in a table against the conditions of a foreign key constraint. I run my restores with the "-e" option to halt on errors, so this data halted the restore... I went to check the running database and the row in question had been deleted.
I had defined the foreign key to cascade on delete, and I imagine that during the dump the delete occurred on the master table. Perhaps the keyed table had already been dumped so when it came time to dump the master table, the referencing row was not there to be dumped.
pg_dump does all its work in a single serializable transaction to avoid this sort of problem. It doesn't see any changes made to the database after it starts. So, assuming you used pg_dump to dump the database as a whole rather than invoking it separately for a bunch of separate tables, that should not be your problem.
How do you run pg_dump? Can you supply the script or command line?
One would imagine that PostgreSQL would have protections for that sort of thing...
It does, which is what makes the issue you've encountered somewhat strange. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
