Hi all,
I've run into a snag with using pg_restore. My database contains two tables
with the lo datatype they use with Visual Basic client side and the ODBC
driver. I have a cron job running every night to backup my database thus:
DB="somedb"
SCHEMA="/var/db/backup/schema.sql"
DATA="/var/db/backup/data.tar"
PGDUMP="/usr/local/pgsql/bin/pg_dump"
$PGDUMP -f $SCHEMA -F p -C -o -s -h localhost -p 5432 -U postgres $DB
chmod 700 $SCHEMA
$PGDUMP -f $DATA -Ft -C -o -a -b -h localhost -p 5432 -U postgres $DB
chmod 700 $DATA
This is then copied onto another file server for offsite backups. I can't
remember why but I decided to dump the schema seperately to the data. I've
never had to do a restore so far.
My current production DB is 7.3.0 and I have been playing with 7.3.2 with the
view to upgrading however I want to test in a dev environment before slapping
the 7.3.2 binary on my production data. When I create the new database in a
new cluster, reload the schema and then reload the data it always dies at the
BLOB recreation. The commands I issue to load the data (using port 5431 for
the test DB) are:
./createdb -p5431 somedb
./psql -p5431 -dsomedb < /var/db/backup/schema.sql
./pg_restore -p5431 -dsomedb -Ft -N -X disable-triggers
/var/db/backup/data.tar
The error I get is:
ERROR: Relation "BLOBs" does not exist
I'm presuming that pg_dump writes the blob table of contents incorrectly
however I'm not sure... All my BLOB (lo) objects are defined in a non public
schema however postgres stores lo objects in pg_largeobjects anyway so maybe
its something to do with the OID/lo linking between my two BLOB tables and
pg_largeobjects... I'm probably way out here but does anyone have any
pointers on getting BLOB/lo loading to work?
Thanks in advance,
Jason
