Thread: trouble migrating large objects from 7.1.3 to 7.3.2
Hi - I'm trying to migrate a database with large objects from 7.1.3 to 7.3.2 & have so far been unsuccessful. I'm running both 7.1.3 & 7.3.2 on the same machine (running Slackware Linux 8.1) and I'm using the following command to migrate the data:
pg_dump -p 6543 -b -Ft -v -o dbase | pg_restore -p 5432 -Ft -v -o -d dbase
The above command causes pg_restore to fail with the following error:
pg_restore: [tar archiver] could not find header for file 37.dat in tar archive
If I omit the -o options, the dump & restore seem to work, but the OID's are incorrect & cannot be referenced. I know the data is there because the size of the new database on disc is roughly the same as the old database, but I cannot reference any of the data in the OID fields (all other fields are okay). All the values in the new OID fields increase by two, rather than represent the actual size of the data in the field. I have noticed that if I use psql to view the OID fields while the data is migrating, the numbers look correct right up until the end. It seems that during the last phase of pg_restore, the OID numbers get reset to some sequence that just increases by two for every OID. Here's a sample of what is in the OID column of both the old & new databases:
old new
26916 17034
29118 17036
30787 17038
33030 17040
36042 17042
The same problem occurs whether I pipe the output from pg_dump to pg_restore or use an intermediate file. I've also tried using pg_dumplo & get the same results (no error, just OID's whose value increase by two).
I've seen messages in the archive from other people with the same problem, but no answers were given.
Any ideas?
Thanks.
John Layman
"John M. Layman" <jml@frijid.net> writes:
> I'm using the following command to migrate the data:
> pg_dump -p 6543 -b -Ft -v -o dbase | pg_restore -p 5432 -Ft -v -o -d dbase
The tar-archive code seems to have some bugs in it :-(. I will look at
that, but the primary problem you are going to face here is that
pg_restore needs to seek in the dump file to implement this operation,
and it can't seek a pipe.
I think you will need to (a) use -Fc not -Ft, and (b) write the dump to
a temp file that pg_restore reads as a separate operation.
regards, tom lane
Tom- thanks for the suggestion. As you mentioned, substituting Fc for Ft in the pipe command fails with an fseek error: pg_restore: [custom archiver] Dumping a specific TOC data block out of order is not supported without id on this input stream (fseek required) When I use option Fc with an intermediate file, I get an error about one of my sequences not existing. Do I have to do something different when using the Fc option? Here are the commands I was using: 7.1.3: pg_dump -p 6543 -b -Fc -v -o dbase > dump 7.3.2 initdb postmaster createdb dbase pg_restore -p 5432 -Fc -v -o -d dbase < dump And the error: pg_restore: [archiver (db)] could not execute query: ERROR: Relation "disc_idx_seq" does not exist I tried dropping the sequence & re-running the restore, but that just got me into more trouble. I'm guessing that I should let pg_restore create the database for me (instead of issuing a createdb command), but I can't get the -c or -C options to work. I get the following error when using either of them without running createdb: pg_restore: [archiver (db)] connection to database "dbase" failed: FATAL: database "dbase" does not exist in the system catalog And I get the following errors if I do run createdb: with option -c: pg_restore: [archiver (db)] connection to database "dbase" failed: FATAL: parser zero-length delimited identifier at or near """" at character 47 with option -C: pg_restore: [archiver (db)] connection to database "dbase" failed: CREATE DATABASE: database "dbase" already exists Am I doing something wrong here? ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "John M. Layman" <jml@frijid.net> Cc: <pgsql-admin@postgresql.org> Sent: Monday, April 21, 2003 10:14 PM Subject: Re: [ADMIN] trouble migrating large objects from 7.1.3 to 7.3.2 > "John M. Layman" <jml@frijid.net> writes: > > I'm using the following command to migrate the data: > > > pg_dump -p 6543 -b -Ft -v -o dbase | pg_restore -p 5432 -Ft -v -o -d dbase > > The tar-archive code seems to have some bugs in it :-(. I will look at > that, but the primary problem you are going to face here is that > pg_restore needs to seek in the dump file to implement this operation, > and it can't seek a pipe. > > I think you will need to (a) use -Fc not -Ft, and (b) write the dump to > a temp file that pg_restore reads as a separate operation. > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"John M. Layman" <jml@frijid.net> writes:
> Am I doing something wrong here?
Either that, or managing to hit a ton of corner-case bugs at the same
time ...
One thing to check: are you using 7.3's pg_dump to dump from the 7.1
database? If not, try it. The newer pg_dump should avoid at least
some old bugs.
If that doesn't help, I'd be interested to see a schema-only, text-style
dump from your 7.1 database (please send it to me off-list).
regards, tom lane
"John M. Layman" <jml@frijid.net> writes:
> [ various problems... ]
> pg_restore: [archiver (db)] could not execute query: ERROR: Relation
> "disc_idx_seq" does not exist
I couldn't reproduce this running the dump file you were kind enough to
send me off-list.
> I tried dropping the sequence & re-running the restore, but that just got me
> into more trouble. I'm guessing that I should let pg_restore create the
> database for me (instead of issuing a createdb command),
Not necessarily, but you do usually want to start from an empty database
--- dropping just one object is not going to work.
> but I can't get the -c or -C options to work.
I think this is mostly pilot error, probably compounded by
insufficiently clear explanations in the docs. It might help to think
about what each of these switches actually does. "-c" simply causes
pg_restore to issue a "DROP foo" command immediately before each of its
"CREATE foo" commands. (That's at the individual-object level, I don't
think it applies to the whole database.) This switch strikes me as of
little use --- it's always faster to just drop the whole database before
you start the restore. (I suppose it might come in handy for certain
kinds of database-merge operations, though.) What the "-C" switch does
is cause pg_restore to issue a "CREATE DATABASE dbname" and then
"\connect dbname" before it starts restoring individual objects. The
gotcha here is you cannot say "-d dbname" in the pg_restore command
line, because dbname doesn't exist yet. You have to tell it to
initially connect to some DB that *does* exist, from whence it can issue
the CREATE DATABASE command. So usually "-C" would go along with
"-d template1".
(If you can think of a better way to explain all this, patches for the
documentation would be most welcome.)
> pg_restore: [archiver (db)] connection to database "dbase" failed: FATAL:
> parser zero-length delimited identifier at or near """" at character 47
This error confuses me though --- it doesn't square with the above
considerations, and I couldn't reproduce it with your schema dump.
Could you try it with statement logging enabled, and look to see exactly
what commands pg_restore issued that led up to the failure?
regards, tom lane
"John M. Layman" <jml@frijid.net> writes:
> pg_dump -p 6543 -b -Ft -v -o dbase | pg_restore -p 5432 -Ft -v -o -d dbase
> The above command causes pg_restore to fail with the following error:
> pg_restore: [tar archiver] could not find header for file 37.dat in tar archive
Philip Warner points out that "-o" for pg_restore means "restore in OID
order", which requires seeking in the input file, which won't work on
piped input. Try it without the -o on the restore side. (Offhand I'd
bet that you don't really want -o on the dump side, either, but that's
a different issue.)
The error messages do leave something to be desired :-(
regards, tom lane
Tom/Philip - I've tried with the -o option on the dump side only & I get the
original problem - the OID's in the new database just increase by 2. The data
is there, though, because the disc space decreases.
I was originally running pg_dump & pg_restore without the -o options, but when
I saw it wasn't working, I tried using the -o options in various combinations
(pg_ dump only, pg_restore only & both).
From what the docs say, I shouldn't need these options, but I tried them as a
last resort.
"John M. Layman" <jml@frijid.net> writes:
> pg_dump -p 6543 -b -Ft -v -o dbase | pg_restore -p 5432 -Ft -v -o -d dbase
> The above command causes pg_restore to fail with the following error:
> pg_restore: [tar archiver] could not find header for file 37.dat in tar
archive
Philip Warner points out that "-o" for pg_restore means "restore in OID
order", which requires seeking in the input file, which won't work on
piped input. Try it without the -o on the restore side. (Offhand I'd
bet that you don't really want -o on the dump side, either, but that's
a different issue.)
The error messages do leave something to be desired :-(
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly