Title: Pg_restore failing with ERROR: out of memory
I’m attempting to do something that should be a trivially simple task. I want to do a data only dump from my production data in the public schema and restore it on another machine. Both machines are running 8.2.5, both machines are virtually identical and have 16GB of RAM.
I created an archive with the following command:
pg_dump -v -Fc -a -n public -f 20080318-data-archive
The archive is roughly 2GB. An uncompressed SQL dump is ~9GB.
...and am trying to restore it with:
pg_restore -av -d m16test 20080318-data-archive
I have a copy of the public schema loaded prior to doing the pg_restore.
Several small tables import fine, but when it gets to answerselectinstance, a table with > 107 million rows, it chokes:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3709; 0 1170915411 TABLE DATA answerselectinstance bzzagent
pg_restore: [archiver (db)] COPY failed: ERROR: out of memory
DETAIL: Failed on request of size 32.
CONTEXT: COPY answerselectinstance, line 61944061: "180097 63 0"
I have also tried running a straight data-only SQL file through psql and get a similar error.
Answerselectinstance is defined as follows:
Table "public.answerselectinstance"
Column | Type | Modifiers
----------------+---------+--------------------
memberid | integer | not null
answerid | integer | not null
taskinstanceid | integer | not null default 0
Indexes:
"answerselectinstance_pkey" PRIMARY KEY, btree (memberid, answerid, taskinstanceid)
"asi_answerid_idx" btree (answerid)
"asi_memberid_idx" btree (memberid)
"asi_taskinstanceid_idx" btree (taskinstanceid)
Triggers:
"RI_ConstraintTrigger_1170917112" AFTER INSERT OR UPDATE ON answerselectinstance FROM answer NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'answerselectinstance', 'answer', 'UNSPECIFIED', 'answerid', 'id')
_bzz_cluster_logtrigger_301 AFTER INSERT OR DELETE OR UPDATE ON answerselectinstance FOR EACH ROW EXECUTE PROCEDURE _bzz_cluster.logtrigger('_bzz_cluster', '301', 'kkk')
As you can see, this is part of a slony cluster. I have created the schema using slony1_extract_schema.sh and am importing the data into a standalone server.
Our DBA solved this problem a few months ago, but recently left the company and did not document the procedure. I’m no PostgreSQL expert....
Let me know what other information would be helpful.
Thanks,
Aaron
