Обсуждение: pg_dumpall 8.1.4 large objects error
I'm curious why this would happen: pg_dump: dumpBlobs(): could not open large object: ERROR: inv_open: large object 145391 not found The db being dumped is 7.3.2 and the pg_dumpall is from a source compiled 8.1.4. The OS in question is Redhat 8 (soon to be upgraded). When I used the 7.3.2 pg_dump -b, everything dumped out ok, but using the 8.1.4 pg_dumpall which is supposed to properly handle large objects, I received this error. Note, there's only the single error above, and I still ended up with a 17gig bzipped file. The 7.3.2 pg_dump yielded a 16gig file in custom format with default compression. I need to get a reasonable dump so I can upgrade this ancient system. Should I upgrade to the most recent 7.3.x version before doing the dump to have the most reasonable chance of success? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost <jeff@frostconsultingllc.com> writes:
> I'm curious why this would happen:
> pg_dump: dumpBlobs(): could not open large object: ERROR: inv_open: large
> object 145391 not found
Some cursory trawling in the REL7_3 sources says that this means that
"SELECT DISTINCT loid FROM pg_largeobject" found a large object OID
that then could not be found by an indexscan of pg_largeobject. So
I'd try a REINDEX of pg_largeobject to see if that fixes it. See the
REINDEX man page concerning hoops you have to jump through to reindex
a system catalog --- IIRC, the hoops are much higher and narrower back
in 7.3.
> The db being dumped is 7.3.2
Of course, I'm reading 7.3.15 sources ...
regards, tom lane
On Tue, 6 Jun 2006, Tom Lane wrote: > Some cursory trawling in the REL7_3 sources says that this means that > "SELECT DISTINCT loid FROM pg_largeobject" found a large object OID > that then could not be found by an indexscan of pg_largeobject. So > I'd try a REINDEX of pg_largeobject to see if that fixes it. See the > REINDEX man page concerning hoops you have to jump through to reindex > a system catalog --- IIRC, the hoops are much higher and narrower back > in 7.3. Thanks Tom. BTW, you might be amused to see the uptime output prior to shutting the machine down to add a PCI card: 10:17am up 1004 days, 7 min, 1 user, load average: 0.00, 0.00, 0.00 -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Wed, 7 Jun 2006, Jeff Frost wrote: > On Tue, 6 Jun 2006, Tom Lane wrote: > >> Some cursory trawling in the REL7_3 sources says that this means that >> "SELECT DISTINCT loid FROM pg_largeobject" found a large object OID >> that then could not be found by an indexscan of pg_largeobject. So >> I'd try a REINDEX of pg_largeobject to see if that fixes it. See the >> REINDEX man page concerning hoops you have to jump through to reindex >> a system catalog --- IIRC, the hoops are much higher and narrower back >> in 7.3. > Got the REINDEX completed and found a new error that I haven't seen before: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: Memory exhausted in AllocSetAlloc(96) pg_dump: The command was: FETCH 100 IN blobcmt pg_dumpall: pg_dump failed on database "vsl_cs", exiting I was dumping like so: /usr/local/pgsql-8.1.4/bin/pg_dumpall | /usr/bin/bzip2 > vsl_cs-20060608.sql.bz2 Would it be better and more efficient to just pg_dumpall the globals and pg_dump in custom format the vsl_cs db? There's actually only one DB on the systems besides the system dbs. Server is 7.3.2, and the plan is to upgrade it. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Fri, 9 Jun 2006, Jeff Frost wrote:
> Got the REINDEX completed and found a new error that I haven't seen before:
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: Memory exhausted in
> AllocSetAlloc(96)
> pg_dump: The command was: FETCH 100 IN blobcmt
> pg_dumpall: pg_dump failed on database "vsl_cs", exiting
>
> I was dumping like so:
>
> /usr/local/pgsql-8.1.4/bin/pg_dumpall | /usr/bin/bzip2 >
> vsl_cs-20060608.sql.bz2
>
> Would it be better and more efficient to just pg_dumpall the globals and
> pg_dump in custom format the vsl_cs db? There's actually only one DB on the
> systems besides the system dbs. Server is 7.3.2, and the plan is to upgrade
> it.
Hit send before i was done:
ulimit -d shows:
bash-2.05b$ ulimit -d
unlimited
The free output on the system looks like this:
bash-2.05b$ free
total used free shared buffers cached
Mem: 1031024 481548 549476 0 30620 359524
-/+ buffers/cache: 91404 939620
Swap: 2562328 193992 2368336
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost <jeff@frostconsultingllc.com> writes:
>> On Tue, 6 Jun 2006, Tom Lane wrote:
>>> I'd try a REINDEX of pg_largeobject to see if that fixes it.
> Got the REINDEX completed and found a new error that I haven't seen before:
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: Memory exhausted in AllocSetAlloc(96)
Hm, I'm not sure why it did that. Possibly an ANALYZE on pg_largeobject
would change the plan for the SELECT DISTINCT and get you out of
trouble.
> Would it be better and more efficient to just pg_dumpall the globals and
> pg_dump in custom format the vsl_cs db?
Won't help AFAIK --- that query will be used for blob dumping in all
cases. Or actually, according to the cursor name, this is the query
for dumping blob comments.
> Server is 7.3.2, and the plan is to upgrade it.
Might be worth your while to update the server to 7.3.latest in-place.
The list of bugs fixed in 7.3.x is very long; I'm too lazy to look and
see if any of them look related to this, but it's possible.
Also, if you don't mind altering pg_dump, it looks to me like the query
being used here is unnecessarily inefficient:
/* Cursor to get all BLOB comments */
if (AH->remoteVersion >= 70200)
blobQry = "DECLARE blobcmt CURSOR FOR SELECT DISTINCT loid, obj_description(loid, 'pg_largeobject') FROM
pg_largeobject";
This is computing obj_description() redundantly for each pg_largeobject
chunk. Perhaps there is a memory leak in obj_description() in 7.3.2?
If so it'd help to use
DECLARE blobcmt CURSOR FOR
SELECT loid, obj_description(loid,'pg_largeobject')
FROM (SELECT DISTINCT loid FROM pg_largeobject) ss;
(Think I'll go change this in CVS, too, as it's obviously tremendously
inefficient if you've got big large objects...)
regards, tom lane
On Fri, 9 Jun 2006, Tom Lane wrote: >> pg_dump: SQL command failed >> pg_dump: Error message from server: ERROR: Memory exhausted in AllocSetAlloc(96) > > Hm, I'm not sure why it did that. Possibly an ANALYZE on pg_largeobject > would change the plan for the SELECT DISTINCT and get you out of > trouble. > >> Would it be better and more efficient to just pg_dumpall the globals and >> pg_dump in custom format the vsl_cs db? > > Won't help AFAIK --- that query will be used for blob dumping in all > cases. Or actually, according to the cursor name, this is the query > for dumping blob comments. > >> Server is 7.3.2, and the plan is to upgrade it. > > Might be worth your while to update the server to 7.3.latest in-place. > The list of bugs fixed in 7.3.x is very long; I'm too lazy to look and > see if any of them look related to this, but it's possible. The in-place upgrade was going to be my next question, but that'll require another maintenance window. I was really hoping to get a good backup before the next time we take it down. :-/ > Also, if you don't mind altering pg_dump, it looks to me like the query > being used here is unnecessarily inefficient: > > /* Cursor to get all BLOB comments */ > if (AH->remoteVersion >= 70200) > blobQry = "DECLARE blobcmt CURSOR FOR SELECT DISTINCT loid, obj_description(loid, 'pg_largeobject') FROM pg_largeobject"; > > This is computing obj_description() redundantly for each pg_largeobject > chunk. Perhaps there is a memory leak in obj_description() in 7.3.2? > If so it'd help to use > > DECLARE blobcmt CURSOR FOR > SELECT loid, obj_description(loid,'pg_largeobject') > FROM (SELECT DISTINCT loid FROM pg_largeobject) ss; I'll alter pg_dump and recompile, then give a test. Does pg_dumpall just call pg_dump or do I need to change it there too? Thanks for the help Tom. I'll report back with the status. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
I wrote: >> This is computing obj_description() redundantly for each pg_largeobject >> chunk. Perhaps there is a memory leak in obj_description() in 7.3.2? Actually, obj_description() is a SQL-language function, and we had horrendous problems with end-of-function-call memory leakage in SQL functions in every version before 7.4. So this is undoubtedly the source of Jeff's problem. Jeff Frost <jeff@frostconsultingllc.com> writes: > I'll alter pg_dump and recompile, then give a test. Does pg_dumpall just call > pg_dump or do I need to change it there too? pg_dumpall calls pg_dump, so only one place to fix. I've already committed the fix in CVS, if you'd prefer to use a tested patch. http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c.diff?r1=1.422.2.3;r2=1.422.2.4 regards, tom lane
On Fri, 9 Jun 2006, Tom Lane wrote: > pg_dumpall calls pg_dump, so only one place to fix. I've already > committed the fix in CVS, if you'd prefer to use a tested patch. > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c.diff?r1=1.422.2.3;r2=1.422.2.4 I'm running a test dump now, so we'll see sometime tomorrow (it takes about 20 hrs with the current setup) if it worked properly or if I find a new problem. :-) -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Fri, 9 Jun 2006, Jeff Frost wrote: > On Fri, 9 Jun 2006, Tom Lane wrote: > >> pg_dumpall calls pg_dump, so only one place to fix. I've already >> committed the fix in CVS, if you'd prefer to use a tested patch. >> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_dump.c.diff?r1=1.422.2.3;r2=1.422.2.4 > > I'm running a test dump now, so we'll see sometime tomorrow (it takes about > 20 hrs with the current setup) if it worked properly or if I find a new > problem. :-) You'll be happy to hear that the test dump was successful and actually only required 12 hrs to complete. Now that we can create a valid db dump, we'll be doing the upgrade as soon as a maintenance window presents itself. Thanks Tom! -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Sat, 10 Jun 2006, Jeff Frost wrote: >> I'm running a test dump now, so we'll see sometime tomorrow (it takes about >> 20 hrs with the current setup) if it worked properly or if I find a new >> problem. :-) > > You'll be happy to hear that the test dump was successful and actually only > required 12 hrs to complete. Now that we can create a valid db dump, we'll > be doing the upgrade as soon as a maintenance window presents itself. Server is now up and running on 8.1.4 and much happier for it! The restore only took about 5 hours compared with 12 hours to dump! Wow, I guess we've had a few performance enhancements between 7.3 and 8.1. :-) Thanks for all the help as always! -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954