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. One would imagine that PostgreSQL would have protections for that sort of thing...
Can you think of how I can protect against this in the future?
