Search Postgresql Archives

Re: pg_dump and ON DELETE CASCADE problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux