Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3DE502E0043 for ; Wed, 19 Mar 2008 12:37:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 20485-06 for ; Wed, 19 Mar 2008 12:37:05 -0300 (ADT) X-Greylist: delayed 00:07:09.955212 by SQLgrey-1.7.5 Received: from owa.bzzagent.com (exch.bzzagent.com [66.152.196.38]) by postgresql.org (Postfix) with ESMTP id C7BCE2E003D for ; Wed, 19 Mar 2008 12:37:04 -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 11:29:52 -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 15:29:52 +0000 User-Agent: Microsoft-Entourage/12.0.0.071130 Date: Wed, 19 Mar 2008 11:29:49 -0400 Subject: Pg_restore failing with ERROR: out of memory From: Aaron Brown To: Message-ID: Thread-Topic: Pg_restore failing with ERROR: out of memory Thread-Index: AciJ1hGFjITXNWt6302PXwzDYeE7Ew== Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3288770989_32596842" X-OriginalArrivalTime: 19 Mar 2008 15:29:52.0627 (UTC) FILETIME=[13AE8430:01C889D6] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.82 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819 X-Spam-Level: * X-Archive-Number: 200803/178 X-Sequence-Number: 28661 > 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_3288770989_32596842 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_3288770989_32596842 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Pg_restore failing with ERROR: out of memory I’m attempti= ng to do something that should be a trivially simple task.  I want to d= o 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. &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"