Обсуждение: pg_dumpall 8.1.4 large objects error

Поиск
Список
Период
Сортировка

pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Tom Lane
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Tom Lane
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Tom Lane
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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

Re: pg_dumpall 8.1.4 large objects error

От
Jeff Frost
Дата:
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