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

Doug McNaught <doug(at)wireboard(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> 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)

Ah, that's why. That's just plain wrong. What do you think will happen
when you get past 2 billion OIDs?

> fixing the former will be a royal pain (the SQL to create the tables
> is generated by a fiendishly complicated Java app).

It may be fiendishly complicated, but that doesn't make it any less
wrong.

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

The referencing columns have to be type OID or LO (LO is not standard,
but you might prefer it if you use any of the contrib utilities for
LO maintenance).

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

It is documented someplace or other, but perhaps not in the most useful
places (ie, where you'd look in this context).

Come to look at it, pg_dump -C emits the *wrong thing*. Will fix.

regards, tom lane