On Thursday 10 December 2009 7:27:54 am CG wrote: > 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 <craig@xxxxxxxxxxxxxxxxxxxxx> wrote: > > One thing that comes to mind is to restore the dump file to a file instead of a database and see what is being dumped from the live database. -- Adrian Klaver aklaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
