Обсуждение: 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

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