Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4BBF12E0068 for ; Wed, 19 Mar 2008 14:53:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03124-06 for ; Wed, 19 Mar 2008 14:53:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from owa.bzzagent.com (exch.bzzagent.com [66.152.196.38]) by postgresql.org (Postfix) with ESMTP id E63BB2E004D for ; Wed, 19 Mar 2008 14:53:42 -0300 (ADT) Received: from EXCHENT01.bzzagent.lan ([172.16.92.24]) by owa.bzzagent.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 19 Mar 2008 13:53:41 -0400 Received: from 172.16.0.230 ([172.16.0.230]) by EXCHENT01.bzzagent.lan ([172.16.92.24]) via Exchange Front-End Server owa.bzzagent.com ([192.168.1.22]) with Microsoft Exchange Server HTTP-DAV ; Wed, 19 Mar 2008 17:53:41 +0000 User-Agent: Microsoft-Entourage/12.0.0.071130 Date: Wed, 19 Mar 2008 13:53:37 -0400 Subject: pg_restore failing with "ERROR: out of memory" From: Aaron Brown To: "pgsql-admin@postgresql.org" Message-ID: Thread-Topic: pg_restore failing with "ERROR: out of memory" Thread-Index: AciJ6ig1me7aHsYXMUu2SpcaSu/HZA== Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3288779617_33075805" X-OriginalArrivalTime: 19 Mar 2008 17:53:41.0423 (UTC) FILETIME=[2AD84BF0:01C889EA] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.82 tagged_above=0 required=5 tests=AWL=0.000, HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819 X-Spam-Level: * X-Archive-Number: 200803/144 X-Sequence-Number: 28627 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3288779617_33075805 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable I=B9m 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 an= d 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 ge= t 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 EAC= H ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('', '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=B9m no PostgreSQL expert.... Let me know what other information would be helpful. Thanks, Aaron --B_3288779617_33075805 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable pg_restore failing with "ERROR: out of memory" IR= 17;m attempting to do something that should be a trivially simple task. &nbs= p;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, b= oth 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. &n= bsp;

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 D= ATA 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 d= ata-only SQL file through psql and get a similar error.

Answerselectinstance is defined as follows:
      Table "public.answerselectinstance= "
     Column     |  Type &= nbsp; |     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 (taskinsta= nceid)
Triggers:
    "RI_ConstraintTrigger_1170917112" AFTER I= NSERT 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_cluste= r.logtrigger('_bzz_cluster', '301', 'kkk')

As you can see, this is part of a slony cluster.  I have created the s= chema using slony1_extract_schema.sh and am importing the data into a standa= lone 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
--B_3288779617_33075805--