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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> > 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.

Well, the column type of the referencing field is int4 (rather than
OID) and there is no RI constraint. I can add the latter by hand, but
fixing the former will be a royal pain (the SQL to create the tables
is generated by a fiendishly complicated Java app).

Given the above, it's not surprising to me that pg_restore doesn't fix
the reference--what needs to be changed in my schema?

> > $ 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.

I must have missed that--it's certainly not mentioned in the 'pg_dump'
and 'pg_restore' manpages, and the 'CREATE DATABASE' reference
doesn't directly mention the issue (though it talks about the
difference between template0 and template1).

Makes sense once I think about it.

> 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).

Agreed.

Tom, as usual you come through. If you're ever in Atlanta I will buy
you many beers. :)

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863