Thread

1. Trouble Restore With Large Objects

   Thomas T. Thai <tom@minnesota.com> — 2002-12-21T17:45:07Z

   I'm moving from 7.2.3 to 7.3. I backed up everything and have restored
   almost everything exept one database which has large objects.
   
   The database with large objects was backed up using:
   
   $ pg_dump -Ft -b phpads > phpads.pgsql.tar
   
   I restored using:
   
   $ pg_restore -d phpads phpads.pgsql.tar
   pg_restore: NOTICE:  TypeCreate: changing argument type of function
   txtidx_in from OPAQUE to CSTRING
   pg_restore: NOTICE:  TypeCreate: changing argument type of function
   txtidx_out from OPAQUE to txtidx
   pg_restore: NOTICE:  TypeCreate: changing return type of function
   txtidx_in from OPAQUE to txtidx
   pg_restore: NOTICE:  TypeCreate: changing return type of function
   txtidx_out from OPAQUE to CSTRING
   pg_restore: NOTICE:  TypeCreate: changing argument type of function
   qtxt_in from OPAQUE to CSTRING
   pg_restore: NOTICE:  TypeCreate: changing argument type of function
   qtxt_out from OPAQUE to query_txt
   pg_restore: NOTICE:  TypeCreate: changing return type of function qtxt_in
   from OPAQUE to query_txt
   pg_restore: NOTICE:  TypeCreate: changing return type of function qtxt_out
   from OPAQUE to CSTRING
   pg_restore: NOTICE:  TypeCreate: changing argument type of function
   mqtxt_in from OPAQUE to CSTRING
   pg_restore: [archiver (db)] could not execute query: ERROR:  TypeCreate:
   function qtxt_out(mquery_txt) does not exist
   
   Function is from tsearch. At this point I've not loaded the tsearch.sql
   functions into database phpads yet. So I drop phpads and start over:
   
   $dropdb phpads
   
   $psql phpads < /usr/local_install/postgresql-7.3/share/contrib/tsearch.sql
   
   $pg_restore -d phpads phpads.pgsql.tar
   pg_restore: [archiver (db)] could not execute query: ERROR:  type txtidx
   already exists
   
   The trouble here is pg_dump should have just moved on with this type of
   error instead of stop. That's what it does with a normal text restore.
   
   Is there a way to restore this DB or back it up using an alternative
   method and preserve the large objects?
   
   
   
   

2. Re: Trouble Restore With Large Objects

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-21T20:00:39Z

   "Thomas T. Thai" <tom@minnesota.com> writes:
   > $pg_restore -d phpads phpads.pgsql.tar
   > pg_restore: [archiver (db)] could not execute query: ERROR:  type txtidx
   > already exists
   
   > The trouble here is pg_dump should have just moved on with this type of
   > error instead of stop. That's what it does with a normal text restore.
   
   Yeah.  I have been intending to change pg_restore to not quit on error,
   but haven't gotten around to it.  I'd suggest you see about doing that.
   
   Another possible workaround is to do a schema-only restore into a text
   file that you can load with psql (thereby ignoring this error) followed
   by a data-only restore with pg_restore.  I think that would work but
   have never tried it.
   
   			regards, tom lane