The command's nothing out-of-the-ordinary:
#!/bin/bash
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
#####################################################################
# Set Variables
#####################################################################
DAY_NUM=`/bin/date +"%d"`
MON_NUM=`/bin/date +"%m"`
YEAR_NUM=`/bin/date +"%Y"`
/usr/local/pgsql/bin/pg_dump -h 192.168.1.5 -Upostgres -f
backup.$YEAR_NUM$MON_NUM$DAY_NUM.pga -Fc -b data
#END
Curiouser and curiouser... Last night's dump failed to restore in the same way:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7545; 2606 311883439 FK
CONSTRAINT packet_search_trigram_puuid_fkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR: insert or update
on table "packet_search_trigram" violates foreign key constraint
"packet_search_trigram_puuid_fkey"
DETAIL: Key (packet_uuid)=(0ab44da9-544d-413a-9ab1-a1b442310b24) is not
present in table "packet".
Command was:
ALTER TABLE ONLY packet_search_trigram
ADD CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
REFERE...
pg_restore: *** aborted because of error
pg_restore: finished item 7545 FK CONSTRAINT packet_search_trigram_puuid_fkey
pg_restore: [archiver] worker process failed: exit code 1
pg_restore: *** aborted because of error
That was the same failure I got the previous night. I go to the live database
and rows with that key are /not/ in either one of those tables. They /were/ in
the tables at one point. I have an ON DELETE trigger that copies deleted rows
into another table, so I can see that a row with that key once existed in those
tables.
This may not be a pg_dump problem, but some sort of MVCC irregularity where
pg_dump is able to dump rows that it shouldn't. I bet a VACUUM FULL would clean
this up, but I have a live problem here. If I eradicate it, who knows when
we'll see it again...
--- On Wed, 12/9/09, Craig Ringer <cr...@postnewspapers.com.au> wrote:
From: Craig Ringer <cr...@postnewspapers.com.au>
Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
To: cgg...@yahoo.com
Cc: "postgresql listserv" <pgsql-general@postgresql.org>
Date: Wednesday, December 9, 2009, 9:02 PM
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
