Thread: Export/import issue/question
Hi, I'm trying to move a database from postgresql 7.4 to postgresql 8.1. Unfortunately this is not going well. This is what I did: pg_dump --file dbsnapshot --format=t -a -b 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 However, a tar tf shows that toc.dat is indeed in the dbsnapshot file: kwright@baetis:/common$ tar tf dbsnapshot toc.dat 2.dat 3.dat 4.dat 5.dat 6.dat 7.dat 8.dat 9.dat 10.dat 11.dat 12.dat 13.dat 14.dat 15.dat ... The tar is pretty large: -rw-r--r-- 1 root root 6892524032 Jun 19 09:48 dbsnapshot Any idea what I'm doing wrong? Karl
Karl Wright <kwright@metacarta.com> writes: > But, I get the following error: > pg_restore: [tar archiver] could not find header for file toc.dat in tar > archive Could we see "pg_restore -l" output for that dump file? This looks a bit like some bugs we've seen before, eg, http://archives.postgresql.org/pgsql-bugs/2006-10/msg00263.php but the particular cause of that one wouldn't apply to a 7.4 dump file. regards, tom lane
Tom Lane wrote: > Karl Wright <kwright@metacarta.com> writes: >> But, I get the following error: >> pg_restore: [tar archiver] could not find header for file toc.dat in tar >> archive > > Could we see "pg_restore -l" output for that dump file? > > This looks a bit like some bugs we've seen before, eg, > http://archives.postgresql.org/pgsql-bugs/2006-10/msg00263.php > but the particular cause of that one wouldn't apply to a 7.4 > dump file. > > regards, tom lane > You could, if I could get it. When I do this: pg_restore -l it just hangs. No CPU usage, no disk usage - just sits there. When I do this: pg_restore -l --file dbsnapshot ... same thing. When I supply database credentials via -U and -W, then it simply displays: pg_restore: [tar archiver] could not find header for file toc.dat in tar archive Not very helpful, I know. Any other ideas? Karl
Karl Wright wrote: > Tom Lane wrote: >> Karl Wright <kwright@metacarta.com> writes: >>> But, I get the following error: >>> pg_restore: [tar archiver] could not find header for file toc.dat in >>> tar archive >> >> Could we see "pg_restore -l" output for that dump file? >> >> This looks a bit like some bugs we've seen before, eg, >> http://archives.postgresql.org/pgsql-bugs/2006-10/msg00263.php >> but the particular cause of that one wouldn't apply to a 7.4 >> dump file. >> >> regards, tom lane >> > > You could, if I could get it. > > When I do this: > > pg_restore -l > > it just hangs. No CPU usage, no disk usage - just sits there. > > When I do this: > > pg_restore -l --file dbsnapshot > > ... same thing. > > When I supply database credentials via -U and -W, then it simply displays: > > > pg_restore: [tar archiver] could not find header for file toc.dat in tar > archive > > Not very helpful, I know. Any other ideas? > > Karl > Nevermind. The man page was not clear for -l. Here's the output: kwright@baetis:/common$ sudo pg_restore -l dbsnapshot ; ; Archive created at Tue Jun 19 09:39:30 2007 ; dbname: metacarta ; TOC Entries: 20 ; Compression: 0 ; Dump Version: 1.7-0 ; Format: TAR ; Integer: 4 bytes ; Offset: 8 bytes ; ; ; Selected TOC Entries: ; 2; 0 17143 TABLE DATA public agents metacarta 3; 0 17147 TABLE DATA public ingeststatus metacarta 4; 0 17156 TABLE DATA public authconnectors metacarta 5; 0 17161 TABLE DATA public authconnections metacarta 6; 0 17175 TABLE DATA public connectors metacarta 7; 0 17180 TABLE DATA public repoconnections metacarta 8; 0 17198 TABLE DATA public jobs metacarta 9; 0 17210 TABLE DATA public schedules metacarta 10; 0 17220 TABLE DATA public jobcollections metacarta 11; 0 17227 TABLE DATA public jobqueue metacarta 12; 0 30176 TABLE DATA public repohistory metacarta 13; 0 30191 TABLE DATA public throttlespec metacarta 14; 0 30198 TABLE DATA public jobhopfilters metacarta 15; 0 30212 TABLE DATA public hopcount metacarta 16; 0 30226 TABLE DATA public intrinsiclink metacarta 17; 0 30239 TABLE DATA public hopdeletedeps metacarta 18; 0 30257 TABLE DATA public robotsdata metacarta 19; 0 30264 TABLE DATA public dnsdata metacarta 20; 0 0 BLOBS - BLOBS kwright@baetis:/common$ > > >
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
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&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
Karl Wright <kwright@metacarta.com> writes: > (a) How do I get around the "out of memory" error for pg_restore? Does this table have foreign key constraints? I imagine you're running out of space for the deferred-trigger list. > (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? For both this and (a), the main thing is "avoid data-only restores". Read http://www.postgresql.org/docs/8.2/static/populate.html particularly the last section about making efficient use of pg_dump. regards, tom lane