Thread: how to get pg_restore to continue if an error occurs

I'm using 7.4.19 utils on Centos 5 to move a database from one shared
hosting server to another shared hosting server.

The dump and restore options that I used are:-

pg_dump -O -v -F c
pg_restore -c -O -x -v

pg_restore aborts the restore (with a return code=1) when trying to
'comment schema "public"', which is because the schema is not owned by
the database owner.

1. How do I get pg_restore to simply ignore this error and continue
the restore to completion?
2. Is it ok to have the schema "public" owned by the owner of the database?
3. How do I tell if the restore is complete as a cursory look seems to
indicate the restore is complete?


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au

Sydney Flash Platform Developers Group
April meeting: TBD
Date: Mon 28th April 6pm for 6:30 start
Details soon

I guess this is something that is not a common occurrence as no one
has yet to suggest a solution.

So it looks like I'm going to have to answer my own question.

Firstly, I'd like to say how well thought out is the design of the
dump/restore utils.

If you use the -F c option in pg_dump, this creates what is called a
custom format dump file. This basically creates dump segments which
can be referred to by ID, which can seem by using pg_restore -l to
list the content of the archive. So in my case, the list is:-

;
; Archive created at Mon Apr 14 17:20:44 2008
;     dbname: thedb
;     TOC Entries: 66
;     Compression: -1
;     Dump Version: 1.7-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;
;
; Selected TOC Entries:
;
4; 2200 ACL public postgres
5; 250601 TABLE table1 ownername
6; 250603 TABLE table2 ownername
...
3; 2200 COMMENT SCHEMA public postgres

So by saving this output and commenting out (using the ';') the
entries you what ignored, you can then run the pg_restore util with
the --use-list=list-file and only the uncommented items will be
restored.

Pretty neat.


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au

Sydney Flash Platform Developers Group
April meeting: TBD
Date: Mon 28th April 6pm for 6:30 start
Details soon