Thread: 7.4.6 pg_dump failed
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
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
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
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