Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C48809FC13C for ; Thu, 21 Jun 2007 10:58:19 -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 68236-03 for ; Thu, 21 Jun 2007 10:58:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from silene.metacarta.com (silene.metacarta.com [65.77.47.18]) by postgresql.org (Postfix) with ESMTP id 8A4BF9FC11A for ; Thu, 21 Jun 2007 10:58:12 -0300 (ADT) Received: from localhost (silene.metacarta.com [65.77.47.18]) by silene.metacarta.com (Postfix) with ESMTP id 8C79C14C85AF; Thu, 21 Jun 2007 09:58:10 -0400 (EDT) Received: from silene.metacarta.com ([65.77.47.18]) by localhost (silene.metacarta.com [65.77.47.18]) (amavisd-new, port 10024) with ESMTP id 19681-09; Thu, 21 Jun 2007 09:58:07 -0400 (EDT) X-Auth-Received: from [65.77.47.195] (dhcp-65-77-47-195.metacarta.com [65.77.47.195]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by silene.metacarta.com (Postfix) with ESMTP id 26B8514C85A6; Thu, 21 Jun 2007 09:58:07 -0400 (EDT) Message-ID: <467A83ED.8000904@metacarta.com> Date: Thu, 21 Jun 2007 09:58:05 -0400 From: Karl Wright User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: Export/import issue/question References: <4677F3D2.6050909@metacarta.com> <7516.1182273130@sss.pgh.pa.us> In-Reply-To: <7516.1182273130@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at metacarta.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/219 X-Sequence-Number: 25887 Tom Lane wrote: > Karl Wright writes: >> and then I installed 8.1, and attempted the following: >> pg_restore --file dbsnapshot --format=t --table=ingeststatus -a >> But, I get the following error: >> pg_restore: [tar archiver] could not find header for file toc.dat in tar >> archive > > Oh, I'm overthinking the problem. You left out some details here, > right? Like it sat and did nothing until you hit control-D? > > The above command is wrong because --file is an *output* switch for > pg_restore --- it would have tried to read a tar archive from stdin, > and the "could not find header" complaint is what you get when it hits > immediate EOF and the tar format has been forced on the command line. > (You would have gotten a more recognizable complaint without --format=t, > which is redundant anyway.) Fortunately, it doesn't seem to try to > write the output file right away, so the dumpfile didn't get trashed. > > Correct usage would be something like > > pg_restore --table=ingeststatus -a dbsnapshot >restore.sql > > or add -d etc switches to issue SQL directly to the target database. > > regards, tom lane > OK - I was able to get this to basically work, although there are two problems. First problem: While the total amount of time required to export is reasonable (30 minutes or so), the time required to pg_restore my whole set is very large (more than 12 hours). It also errored out on the largest table: >>>>>> localhost:/common# pg_restore --table=hopdeletedeps -a dbsnapshot -d metacarta -U metacarta pg_restore: ERROR: out of memory DETAIL: Failed on request of size 32. CONTEXT: COPY hopdeletedeps, line 33239560: "http://boards.nbc.com/nbc/index.php?s=5618dbef4559888cf6c2e9321710a293&act=Login&CODE=04&..." pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: out of memory DETAIL: Failed on request of size 32. CONTEXT: COPY hopdeletedeps, line 33239560: "http://boards.nbc.com/nbc/index.php?s=5618dbef4559888cf6c2e9321710a293&act=Login&CODE=04&..." <<<<<< New questions: (a) How do I get around the "out of memory" error for pg_restore? This is a system with 16GB main memory, with a similar amount of swap space, so I would find it hard to go to a much larger footprint. (b) How can I get the restore performance up to the level where it takes only a couple of hours at most to do this restore? Thanks, Karl