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

Doug McNaught <doug(at)wireboard(dot)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 argument types

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