Re: Export/import issue/question

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