Problem with pg_dump

[m.vanklink]

We are experiencing a problem with pg_dump. When running pg_dump it returns with the following error:

Code: Select all

...
pg_dump: dumping contents of table crm_department_output
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of memory
DETAIL:  Failed on request of size 536870912.
pg_dump: The command was: COPY public.crm_department_output (crm_department_output_id, document_type, document_format_type, crm_department_id, ocument_date, origin_type, origin_id, owner_id, log_creation_user, log_creation_dt, log_modification_user, log_modification_dt, document, checkout_dt, checkout_user, checkin_dt, name, document_sequence_integer, amount_excl_vat, amount_incl_vat, glb_currency_id, currency_amount_excl_vat, currency_amount_incl_vat, document_reference, crm_contactperson_id, mailed_dt, faxed_dt, cost_price, printed_dt, start_date, end_date) TO stdout;
pg_dump: *** aborted because of error


The table consist of more than 3000 rows. Rows contain a bytea-column, this column holds documents up to approximately 130MB.

When running this COPY-query in pgAdmin redirecting to a file instead of STDOUT it produces the same error. But when adding WITH BINARY to the query it runs okay! This is why we think this is not the result of corruption of data but rather a memory problem. When dealing with memory problems in postgres there are three settings we can adjust: shared_buffers, work_mem and maintenance_work_mem. Currently these are set to 32MB, 1MB and 16MB.

Tonight we are going to change shared_buffers to 256MB and restart the database-server. Would this be a good idea and could this solve the problem or are we changing the wrong value?

[m.vanklink]

Did the server restart. Changed shared_buffers to 256MB but still ended up with the same error...

[m.vanklink]

Did some searching on the internet. Came up with two possible causes.

1. Corruption of data. This is not likely because when we repeat the COPY process manually in pgAdmin it still crashes. But when we add WITH BINARY to it, it runs okay.
2. Memory issue. We've seen some reports on extremely wide columns and our table contains extremely wide columns (> 100MB). But we have not seen a solution for this. No bugfix and also no tips for adjusting the memory parameters.

Any ideas?

[Kaptan]

I can remember something like this happened to me also.
I think that the error you get is not a postgres error per se, but rather a system error. In our case it had something to do with running postgres on a virtual server that had too little memory allocated (I think, because in the end we dropped the whole virtual server, and reinstalled stuff on a dedicated server, and didn't had errors like this anymore). But I can remember that most people pointed out that it could be data corruption. Couldn't you at least make sure that your data isn't corrupt? (probably a good thing to know anyway) And if there is no corruption you can always start debugging the memory. In that case I would start with monitoring your memory usage while performing the COPY command / using postgres.

[m.vanklink]

Thanks for you input Sem. The server is a physical server with plenty memory (12GB). The COPY command (with WITH BINARY) on this table runs okay and produces a file of app. 10GB. How can we make sure that data is not corrupt? We are talking about thousands of fairly big documents...

[Kaptan]

Unfortunately I wouldn't know how to do this.. I was thinking out loud about how I would approach this.

Isn't there also a clue in adding the WITH BINARY option? What does WITH BINARY exactly do?

Could it be that without the WITH BINARY option that there is a problem with newlines (some sort of escaping problem) ? (postgres doc's state "When COPY is used without the BINARY or CSV options, the data read or written is a text file with one line per table row. Columns in a row are separated by the delimiter character"). Maybe by adding the WITH BINARY option the rows are identified as seperate, and possible not treated as one big row or something.. Maybe you get the out of memory error because the COPY command tries to hold all rows as one in memory.. Again, thinking out loud again, the more I think about it, the more I realise I don't actually know what I'm talking about But thinking along this path, there could be a third possible cause: that there isn't any problem with memory or data corruption, but rather a consequence of the configured encoding etc. that causes errors with specific commands..

[ROCLASI]

Hi Michel,

What version of PostgreSQL is this? And is it a 32-bit or 64-bit build?
'SELECT version();' will show all this.

Code: Select all

DETAIL:  Failed on request of size 536870912.

If I read that right it fails on 536MB of data. My guess is that this is due to the fact that it writes it out in hex so binary data gets quite a bit bloated when written out in text.

You say you have a machine with 12Gig or RAM. Is this a dedicated DB server? I.e. can PostgreSQL address all that ?
If so you can up the shared memory to a LOT higher. This is what the PostgreSQL manual tells us:

If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even large settings for shared_buffers are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Larger settings for shared_buffers usually require a corresponding increase in checkpoint_segments, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

So you can set the shared buffers to a (much) larger value as long as it's not larger than the system shared memory.

The work_mem and maintenance_work_mem are more about speed (in-memory processing vs on disk) so no real need to change these.

Useful links:
http://www.postgresql.org/docs/9.2/stat ... ource.html
http://wiki.postgresql.org/wiki/Tuning_ ... SQL_Server

Hope this helps.

[lwjwillemsen]

Hi Robert,

We (Michel and Lambert) got some feedback from Tom Lane (the PostgreSQL guru I guess) on this issue :

There is a limit to the size of column you can manipulate without
running out of memory, and it is context-dependent, so this behavior is
neither surprising nor a bug. The reason COPY is failing while COPY
BINARY doesn't is that the former requires multiple transient copies
of data during I/O conversion, output line formatting, etc. At a couple
hundred megabytes apiece that'll add up pretty quickly.

If you don't want to reconsider your data storage layout, a possible
solution is to move to a 64-bit build of Postgres, so that you're not up
against a 4GB total address space limit. (I'm not sure that 8.4.x
supports 64-bit Windows, but you could definitely get such a build in
more recent PG versions.) Pending such a fix, you might be able to make
more space available for COPY workspace by reducing shared_buffers and
other settings that control consumption of shared memory space.

regards, tom lane

Our end-users would still call it a 32-bit program bug...
The fact is you cannot make a backup of non-corrupted data

Anyway, we now have some clarity on this matter.

Regards,