Tom Lane wrote:
Karl Wright <kwright@xxxxxxxxxxxxx> 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
