Re: Backing up databases with large objects DOESN'T WORK?

Doug McNaught <doug@wireboard.com> writes:
> *The 'loid' fields in the pg_largeobject table are different after the
> restore!*

Yup.

> This of course breaks the references to pg_largeobject in my other
> tables.

pg_restore is supposed to take care of fixing those for you.  If that
failed, we should investigate why.

> $ pg_dump -b -Fc mydb > outfile
> $ dropdb mydb
> $ createdb mydb
> $ pg_restore -d mydb < outfile
> pg_restore: [archiver (db)] could not execute query: ERROR:  function plpgsql_call_handler already exists with same
argumenttypes 

Hmm, do you have plpgsql installed into template1?  You are supposed to
use template0 as the template when creating a database to be restored
by pg_restore.

It occurs to me that pg_restore shouldn't necessarily abandon ship after
getting an SQL error.  The normal behavior of a SQL-script-type pg_dump
dump is that it'll keep plugging after an error, and this frequently
is good not bad (eg, GRANTs to nonexistent users shouldn't abort the
restore).

            regards, tom lane