Обсуждение: Trouble Restore With Large Objects
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?
"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