Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 307602E004A for ; Wed, 19 Mar 2008 16:09:21 -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 65948-10 for ; Wed, 19 Mar 2008 16:09:09 -0300 (ADT) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by postgresql.org (Postfix) with ESMTP id 87E212E003D for ; Wed, 19 Mar 2008 16:09:12 -0300 (ADT) X-Greylist: delayed 03:39:15.936261 by SQLgrey-1.7.5 Received: from owa.bzzagent.com (exch.bzzagent.com [66.152.196.38]) by mx3.hub.org (Postfix) with ESMTP id B8669380A31 for ; Wed, 19 Mar 2008 16:09:11 -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 15:09:03 -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 19:09:03 +0000 User-Agent: Microsoft-Entourage/12.0.0.071130 Date: Wed, 19 Mar 2008 15:08:59 -0400 Subject: Re: pg_restore failing with "ERROR: out of memory" From: Aaron Brown To: Tom Lane CC: Message-ID: Thread-Topic: [ADMIN] pg_restore failing with "ERROR: out of memory" Thread-Index: AciJ9F5NsArD/cgAR16u3LACsdzsZgAAFE5f In-Reply-To: <17408.1205953599@sss.pgh.pa.us> Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3288784140_33360694" X-OriginalArrivalTime: 19 Mar 2008 19:09:03.0615 (UTC) FILETIME=[B24808F0:01C889F4] 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/147 X-Sequence-Number: 28630 > 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_3288784140_33360694 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Yes, it kind of needs to be data only since I am pulling from a slonized database. My experience has been if you don=B9t load the schema first with a schema from slony1_extract_schema.sh, we end up with all the slony triggers and crap in the dump. If there is a better way of doing this, I=B9m definitely all ears. Aaron=20 On 3/19/08 3:06 PM, "Tom Lane" wrote: > Aaron Brown writes: >> > 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 sche= ma >> and >> > restore it on another machine. >=20 > Does it really need to be data-only? A regular schema+data dump usually > restores a lot faster. >=20 > Your immediate problem is probably that it's running out of memory for > pending foreign-key triggers. Even if it didn't run out of memory, the > ensuing one-tuple-at-a-time checks would take forever. You'd be better > off dropping the FK constraint, loading the data, and re-creating the > constraint. >=20 > There's further discussion of bulk-loading tricks in the manual: > http://www.postgresql.org/docs/8.2/static/populate.html >=20 > regards, tom lane >=20 >=20 >=20 >=20 > ------------------------------------------------------- > Aaron Brown, Systems Engineer > BzzAgent, Inc. | www.bzzagent.com > abrown@bzzagent.com | 617.451.2280 > -------------------------------------------------------=20 --B_3288784140_33360694 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [ADMIN] pg_restore failing with "ERROR: out of memory"= Yes, it kind of needs to be data only since I am pulling fr= om a slonized database.  My experience has been if you don’t load= the schema first with a schema from slony1_extract_schema.sh, we end up wit= h all the slony triggers and crap in the dump.  If there is a better wa= y of doing this, I’m definitely all ears.

Aaron


On 3/19/08 3:06 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Aaron Brown <abrown@bzz= agent.com> writes:
> 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 sche= ma and
> restore it on another machine.

Does it really need to be data-only?  A regular schema+data dump usual= ly
restores a lot faster.

Your immediate problem is probably that it's running out of memory for
pending foreign-key triggers.  Even if it didn't run out of memory, th= e
ensuing one-tuple-at-a-time checks would take forever.  You'd be bette= r
off dropping the FK constraint, loading the data, and re-creating the
constraint.

There's further discussion of bulk-loading tricks in the manual:
http://ww= w.postgresql.org/docs/8.2/static/populate.html

            &nb= sp;           regards= , tom lane




-------= ------------------------------------------------
Aaron Brown, Systems Engineer
BzzAgent, Inc. | www.bzzagent.com
abrown@bzzagent.com | 617.451.2280
-------------------------------------------------------

--B_3288784140_33360694--