Thread: Backing up databases with large objects DOESN'T WORK?
Does anyone actually do this and have it work? I have an int4 field in one of my tables that refers to 'pg_largeobject.loid' so I can retrieve the associated LOB. There is no RI constraint on this field (due to the way my 3rd-party DB library works; I can probably add one with ALTER TABLE if necessary). Under 7.1.3 (as distributed with Red Hat 7.2) I use: $ pg_dump -b -Fc mydb > outfile I then do: $ dropdb mydb $ createdb mydb $ pg_restore -d mydb <outfile This runs to completion with no problems, however: *The 'loid' fields in the pg_largeobject table are different after the restore!* This of course breaks the references to pg_largeobject in my other tables. "No problem", I say, "I'll just use the -o option to pg_dump." Now, running pg_restore as above gives me: Archiver(db): Could not execute query. No result from backend. Not too helpful. OK, download and compile 7.2, and create and load my database. $ 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 WTF? I can't restore from backups because I did 'createlang plpgsql template1'? Am I doing something wrong or does this stuff just plain not work? No flames intended but it's been a really frustrating afternoon. ;) -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
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
Doug McNaught <doug@wireboard.com> writes:
> Tom Lane <tgl@sss.pgh.pa.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
Tom Lane <tgl@sss.pgh.pa.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 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. 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
Doug McNaught <doug@wireboard.com> writes:
> Do I also need an RI constraint? That's relatively easy to do,
> especially if I'm doing the horrible sed thing.
I don't think an RI constraint would help a lot, since the LO operations
aren't going to check it (eg, lo_unlink won't fire an error). In
general we don't support triggers placed on system catalogs...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Doug McNaught <doug@wireboard.com> writes: > > Tom Lane <tgl@sss.pgh.pa.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? Ummm... Disaster? Frogs raining down from the sky? > > 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. Agreed. I do have source for the darn thing; I just need to figure out the easiest way to fix it--it's one of those DB-independent object abstraction layers that seduces you into using it because it generates a lot of code for you... Or maybe I'll just run a sed script over the generated SQL before executing it. That would be *really* nasty but a LOT easier. ;) > > 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). Do I also need an RI constraint? That's relatively easy to do, especially if I'm doing the horrible sed thing. > It is documented someplace or other, but perhaps not in the most useful > places (ie, where you'd look in this context). OK, I don't feel so bad. ;) > Come to look at it, pg_dump -C emits the *wrong thing*. Will fix. Cool, turned up an actual bug! -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Tom Lane <tgl@sss.pgh.pa.us> writes: > Doug McNaught <doug@wireboard.com> writes: > > Do I also need an RI constraint? That's relatively easy to do, > > especially if I'm doing the horrible sed thing. > > I don't think an RI constraint would help a lot, since the LO operations > aren't going to check it (eg, lo_unlink won't fire an error). In > general we don't support triggers placed on system catalogs... OK. I will tangle with the schema definition tomorrow and see if I can get it to work. Thanks again... -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863