Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 79D3064FD32 for ; Sun, 28 Dec 2008 23:11:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40605-07 for ; Sun, 28 Dec 2008 23:11:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exchange.blackducksoftware.com (inap.blackducksoftware.com [69.25.205.162]) by mail.postgresql.org (Postfix) with ESMTP id 8FEFB64FD06 for ; Sun, 28 Dec 2008 23:11:50 -0400 (AST) Received: from [192.168.1.6] (66.30.248.248) by exdirector.blackducksoftware.com (10.9.8.116) with Microsoft SMTP Server id 8.1.311.2; Sun, 28 Dec 2008 22:11:48 -0500 Message-ID: <49583FF3.8030105@blackducksoftware.com> Date: Sun, 28 Dec 2008 22:11:47 -0500 From: Ted Allen User-Agent: Thunderbird 2.0.0.18 (X11/20081125) MIME-Version: 1.0 To: Merlin Moncure CC: Tom Lane , "pgsql-performance@postgresql.org" Subject: Re: Troubles dumping a very large table. References: <495271EB.80508@blackducksoftware.com> <12778.1230140996@sss.pgh.pa.us> <13141.1230313118@sss.pgh.pa.us> In-Reply-To: Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/219 X-Sequence-Number: 32255 I was hoping use pg_dump and not to have to do a manual dump but if that latest solution (moving rows >300mb elsewhere and dealing with them later) does not work I'll try that. Thanks everyone. Merlin Moncure wrote: > On Fri, Dec 26, 2008 at 12:38 PM, Tom Lane wrote: > >> Ted Allen writes: >> >>> 600mb measured by get_octet_length on data. If there is a better way to measure the row/cell size, please let me know because we thought it was the >1Gb problem too. We thought we were being conservative by getting rid of the larger rows but I guess we need to get rid of even more. >>> >> Yeah, the average expansion of bytea data in COPY format is about 3X :-( >> So you need to get the max row length down to around 300mb. I'm curious >> how you got the data in to start with --- were the values assembled on >> the server side? >> > > Wouldn't binary style COPY be more forgiving in this regard? (if so, > the OP might have better luck running COPY BINARY)... > > This also goes for libpq traffic..large (>1mb) bytea definately want > to be passed using the binary switch in the protocol. > > merlin >