pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Karl Wright <kwright@metacarta.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Export/import issue/question
Date: Thu, 21 Jun 2007 09:58:05 -0400
Message-ID: <467A83ED.8000904@metacarta.com> (raw)
In-Reply-To: <7516.1182273130@sss.pgh.pa.us>
References: <4677F3D2.6050909@metacarta.com>
	<7516.1182273130@sss.pgh.pa.us>

Tom Lane wrote:
> Karl Wright <kwright@metacarta.com> 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&amp;act=Login&amp;COD...;
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&amp;act=Login&amp;COD...;
<<<<<<

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




view thread (7+ messages)  latest in thread

Message-ID: <467A83ED.8000904@metacarta.com>
Permalink:  ../467A83ED.8000904@metacarta.com/
Also on:    postgresql.org/message-id/467A83ED.8000904@metacarta.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: kwright@metacarta.com, tgl@sss.pgh.pa.us
  Subject: Re: Export/import issue/question
  In-Reply-To: <467A83ED.8000904@metacarta.com>
  References: <4677F3D2.6050909@metacarta.com> <7516.1182273130@sss.pgh.pa.us> <467A83ED.8000904@metacarta.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox