Thread: pg_restore problem

On 09/12/2012 12:23 AM, Kjell Øygard wrote:
> Morning guys...
>
> I have two servers , one with postgres 9.2rc1 and one with postgres
> 9.1.4. I need to do a restore from a dump from 9.1.4 to 9.2rc1 and I get
> this error:
>
> pg_restore: [archiver (db)] Error from TOC entry 177675; 2613 579519
> BLOB 579519 primar
> pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate
> key value violates unique constraint "pg_largeobject_metadata_oid_index"
> DETAIL:  Key (oid)=(579519) already exists.
>      Command was: SELECT pg_catalog.lo_create('579519');
>
> This just keep repeat itself in the log.
>
> The command used is: pg_restore -O -U user -d  database2 database2.dump
>  >dump.log 2>&1 &
>
> Appreciate any help

Several things:
1) The production version of 9,2 is out(9.2.0).
2) When you did the dump from 9.1.4 did you use the 9.1.4 or 9.2 version 
of pg_dump?
3) What was the pg_dump command you used?

>
> --
> Rgds
> Kjell Inge Øygard
> Electronic Chart Centre
> www.ecc.no <http://www.ecc.no>
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

On 09/14/2012 01:58 AM, Kjell Øygard wrote:
> 1 - Ok, I was not aware of that....
> 2 -  I used version 9.1.4 of pg_dump
> 3 - The command was in a script, se below
>
> pdir=/usr/local/postgresql-9.1.4/
> bdir=/backup/`hostname -s`/dump/
> export PATH=${pdir}/bin:$PATH
>
> # make sure tmp files are not readable by others
> umask 0077
>
> for db in `psql -l -t -h localhost | awk '{print $1}' |grep -v
> template|grep -v postgres`
> do
>    pg_dump -h localhost -F c -Z -b $db > ${bdir}/${db}.tmp && mv
> ${bdir}/${db}.tmp ${bdir}/${db}.dump

I do not see anything obviously wrong.
Two suggestions.
1) Use the 9.2 version of pg_dump. Newer versions know about changes in 
data handling and are also backward compatible(to 7.0).
2) As of 8.3(I believe) the -b switch is redundant for whole database dumps.

When you do the above dump are there large objects in the 9.2 database 
in spite of the errors?

>
>
> rgds Kjell Inge Ø
>



-- 
Adrian Klaver
adrian.klaver@gmail.com