pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
7.4.6 pg_dump failed
4+ messages / 2 participants
[nested] [flat]

* 7.4.6 pg_dump failed
@ 2005-01-26 15:16  Marty Scholes <marty@outputservices.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Marty Scholes @ 2005-01-26 15:16 UTC (permalink / raw)
  To: pgsql-admin

I am dumping some larger (less-used) tables from 7.4.6 to facilitate an 
upgrade to 8.0.

A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump 
before it aborted with:

pg_dump: dumpClasses(): SQL command failed
pg_dump: Error message from server: out of memory for query result
pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor

What causes this?  This is on a Sparc box with 8GB of real memory and 
104GB of virtual, so I am fairly confident that it did not run out of 
memory.  What would cause pg_dump to run for almost 29 hours and then 
die?  The table has not been accessed during that period.

To be fair, the table is not tiny, it consists of 114 segments in the 
base directory: files 17935163 through 17935163.113.

The table contains a text field that could contain several hundred MB of 
data, although always less than 2GB.

How can I dump this table?

Thanks,
Marty




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: 7.4.6 pg_dump failed
@ 2005-01-26 16:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Marty Scholes <marty@outputservices.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-01-26 16:19 UTC (permalink / raw)
  To: Marty Scholes <marty@outputservices.com>; +Cc: pgsql-admin

Marty Scholes <marty@outputservices.com> writes:
> A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump 
> before it aborted with:

> pg_dump: dumpClasses(): SQL command failed
> pg_dump: Error message from server: out of memory for query result
> pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor

Even though it says "from server", this is actually an out-of-memory
problem inside pg_dump, or more specifically inside libpq.

> The table contains a text field that could contain several hundred MB of 
> data, although always less than 2GB.

"Could contain"?  What's the actual maximum field width, and how often
do very wide values occur?  I don't recall the exact space allocation
algorithms inside libpq, but I'm wondering if it could choke on such a
wide row.

You might have better luck if you didn't use -d.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: 7.4.6 pg_dump failed
@ 2005-01-26 16:32  Marty Scholes <marty@outputservices.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Marty Scholes @ 2005-01-26 16:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

The table contains postscript documents.  My company prints and mails 
about 50,000 pages of invoices each day for a customer. We are 
experimenting with putting up a 7 year web based document archive for 
their customers to cut mailing costs and make it easier for their 
customers to take advantage of early pay discounts.  The archive might 
also be used for the customer service reps at the call centers.

I was able to truncate the table since this is experimental and most of 
the data can be reconstructed after the upgrade to 8.0.  The TEXT field 
contains the postscript of each document.  Some invoices are 10,000+ 
pages.  The largest one I recall was about 200 MB in size.

If we do get this project rolling under Pg, I will still have the issue 
with dumping.  Perhaps it was the -d option that caused it.  I 
appreciate the feedback.

Marty

Tom Lane wrote:
> Marty Scholes <marty@outputservices.com> writes:
> 
>>A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump 
>>before it aborted with:
> 
> 
>>pg_dump: dumpClasses(): SQL command failed
>>pg_dump: Error message from server: out of memory for query result
>>pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor
> 
> 
> Even though it says "from server", this is actually an out-of-memory
> problem inside pg_dump, or more specifically inside libpq.
> 
> 
>>The table contains a text field that could contain several hundred MB of 
>>data, although always less than 2GB.
> 
> 
> "Could contain"?  What's the actual maximum field width, and how often
> do very wide values occur?  I don't recall the exact space allocation
> algorithms inside libpq, but I'm wondering if it could choke on such a
> wide row.
> 
> You might have better luck if you didn't use -d.
> 
> 			regards, tom lane






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: 7.4.6 pg_dump failed
@ 2005-01-26 21:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Marty Scholes <marty@outputservices.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2005-01-26 21:32 UTC (permalink / raw)
  To: Marty Scholes <marty@outputservices.com>; +Cc: pgsql-admin

I wrote:
>> You might have better luck if you didn't use -d.

After looking into it, the above seems to be sound advice.  pg_dump
appears to make several (three or four) copies of a wide field value
when using -d or -D mode, but only one copy when using the default
COPY mode.  I made a minor change in 8.0 sources to get rid of one
extra copy step, but getting it below three copies looks to be
impractical.

In any case it'd be a good idea to increase the data-segment ulimit
setting you are using for both the postmaster and pg_dump, if you intend
to push multi-hundred-meg field values around.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-01-26 21:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-26 15:16 7.4.6 pg_dump failed Marty Scholes <marty@outputservices.com>
2005-01-26 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-26 16:32   ` Marty Scholes <marty@outputservices.com>
2005-01-26 21:32     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox