Version: PostgreSQL 9.2.1 - 32-Bit Windows

We have a database which is fairly small (~1.4GB) that is unable to be backed up. It seems to fail on the same table (attachment_data - has about 3500 rows) during pg_dump and giving the following error:

pg_dump: dumping contents of table "public.attachment_data" pg_dump: Dumping the contents of table "attachment_data" failed: PQgetResult() 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.attachment_data (file_data, id) TO stdout;

Not knowing if this was a database issue or an issue with the postgresql instance itself, I installed the same version of postgre on another server, copied the data folder over, and have been working off that data and replicated the same problem.

Thinking it was a bad row or two in the table based on this post for a similar issue, I've been doing selects on top X rows to try and discover bad ones:

select * from attachment_data limit 200

I thought I was getting somewhere - I failed on a couple rows and deleted them. Then I would go back and do the selects again, but it fails inconsistently. For example, sometimes a select limit 2000 will work, and other times it will generate the following:

ERROR: out of memory for query result

I should mention I cannot do a select * from attachment_data if I don't limit it - it will also fail for out of memory.

I've also made some changes to postgresql.conf file on memory and shared_buffers, to see if this would have any impact. I've increased it in steps, restarting each time, up to 1512MB with no change.

I also increased max_locks_per_transactions to 256 based on another thread with no luck.

Any suggestions on things to try?

  • The issue you linked to is not similar because their size is 4294967290 or 0xfffffffa in hex which makes them think the size field is corrupted. But the failed request you're seeing of 536870912 bytes may be for legitimate data. – Daniel Vérité Dec 30 '18 at 13:26
  • Anyway I'd suggest querying octet_length(file_data) instead of * to locate the biggest rows, corrupted or not. – Daniel Vérité Dec 30 '18 at 13:28
  • @DanielVérité so if I am running octet_length(file_data), what is the value of the output other than knowing size? Am I trying to find a match for that value of file size failure? I don't seem to have a match, so a little lost what I should do with the results. – Matt8 Jan 3 '19 at 16:03
  • The point is to check whether the biggest sizes correspond to legit contents or corrupted rows. Once you can pinpoint what exact row has 256MB of binary data (so it seems per the error message), maybe you can tell whether it's expected given the nature or role of this data? – Daniel Vérité Jan 3 '19 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.