From: | Aaron Brown <abrown(at)bzzagent(dot)com> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | pg_restore failing with "ERROR: out of memory" |
Date: | 2008-03-19 17:53:37 |
Message-ID: | C406CB61.5B99%abrown@bzzagent.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2008-03-19 18:46:51 | Re: Windows 64 bit |
Previous Message | Campbell, Lance | 2008-03-19 16:58:28 | Windows 64 bit |