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?