Обсуждение: Help with pg_restore

I'm having some problems running pg_rstore, and reading the documentation
hasn't helped. I've used postgres for a couple of years as a programmer, but
this is the first time I've had to restore a large database. I'm also
posting this message to the postgres-novices lists.

We've got a large database system (several 10s of gigabytes). We have a copy
of our database that was generated by db_dump. We had our RAID array crash a
week ago, and just finished repairing it, so I now have to restore the
database.

The db_dump output was split into 3 files; let's call them file1, file2, and
file3.

I created the data directory, then ran initdb to do the intial setup. I next
ran:
cat file1 file2 file3 | pg_restore --clean --create
It churned for a while and generated a lot of output (file1 and file 2 are
just over a gigabyte each, and file3 is about 300 MB). But when I ran psql,
none of the tables had been created. What am I doing wrong?

Just for giggles I specified "--file=reload.sql" and ran it again, thinking
I could build a file that I could then feed into psql to do the restore. But
that died with a "maximum file size exceeded" error. I'm assuming that's
because of how pg_restore was compiled (we've for some pretty massive files
(well over 2 GB) on disk, so our install of Linux obviously supports files
larger than 2 GB).

What do I need to do to get our database back? I am not able to get any
useful work done until I can get access to the database again.

HELP!

 --------------------------
Dan Mahoney
dan@webfolks.us
AA6GF

"How you behave towards cats here below determines your status in Heaven."
Robert Heinlein

"There are two means of refuge from the miseries of
life - music and cats" - Albert Schweitzer

"dan" <dan@webfolks.us> writes:
> cat file1 file2 file3 | pg_restore --clean --create

Not sure that you can get away with this; pg_restore may expect to be
able to seek in its input file.

> It churned for a while and generated a lot of output (file1 and file 2 are
> just over a gigabyte each, and file3 is about 300 MB). But when I ran psql,
> none of the tables had been created. What am I doing wrong?

Were there any error messages in that output?

You've left out a lot of other significant information, btw, like how
you invoked pg_dump, what OS this is, what PG version, ...

            regards, tom lane

Tom Lane writes:

> Were there any error messages in that output?

None that I saw, but I'll scan the file again. It's a rather large file (18
GB), so that scan could take a while.

> You've left out a lot of other significant information, btw, like how
> you invoked pg_dump, what OS this is, what PG version, ...

Invoked via
  pg_dump --clean --create --verbose --format=c --compress 9

OS is RedHat Linux, 2.4.18-3 kernel comoiled for SMP (I've got two CPUs).

Not sure the version of Postgres in use. I can't find a --version" switch on
any of the executables.

Dan

"dan" <dan@webfolks.us> writes:
> Not sure the version of Postgres in use. I can't find a --version" switch on
> any of the executables.

Try "select version()" in a psql session.  But I can't believe there's
no --version switch, unless it's really ancient.

            regards, tom lane

"dan" <dan@webfolks.us> writes:
> I created the data directory, then ran initdb to do the intial setup. I next
> ran:
> cat file1 file2 file3 | pg_restore --clean --create
> It churned for a while and generated a lot of output (file1 and file 2 are
> just over a gigabyte each, and file3 is about 300 MB). But when I ran psql,
> none of the tables had been created. What am I doing wrong?

After some digging in the CVS logs, I came across this post-7.1 fix:

2001-10-23 17:26  tgl

    * src/bin/pg_dump/pg_backup_archiver.c: Forbid the switch
    combination --clean --create, which is pointless (why bother
    dropping individual objects in a just-created database?) as well as
    dangerous (as the code stands, the drops will be issued in the
    wrong database, namely the one you were originally connected to).

which might or might not explain your difficulty.  What I'm currently
suspecting is that pg_restore did restore, but not into the database you
expected it to (I think it would have created a new database of the
original DB name, and restored into that).

            regards, tom lane