Skip site navigation (1) Skip section navigation (2)

Re: Export/import issue/question

From: Karl Wright <kwright(at)metacarta(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Export/import issue/question
Date: 2007-06-21 13:58:05
Message-ID: 467A83ED.8000904@metacarta.com (view raw or flat)
Thread:
Lists: pgsql-admin
Tom Lane wrote:
> Karl Wright <kwright(at)metacarta(dot)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;CODE=04&amp..."
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;CODE=04&amp..."
<<<<<<

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


In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2007-06-21 14:08:49
Subject: Re: Export/import issue/question
Previous:From: Srinivas KotapallyDate: 2007-06-21 11:02:41
Subject: Re: back up maintenance schedule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group