Trouble Restore With Large Objects

Started by Thomas T. Thaiover 23 years ago2 messagesgeneral
Jump to latest
#1Thomas T. Thai
tom@minnesota.com

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?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Thomas T. Thai (#1)
Re: Trouble Restore With Large Objects

"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