pg_restore failing with "ERROR: out of memory"

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

Responses

Browse pgsql-admin by date

  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