Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_restore returns error schema objects already exist

pg_restore returns error schema objects already exist

From
DavidF@nhb.org
Date:
Hi  -

I'm working with postgres for the first time, and trying to do some trial
backups and restores. The pg_dump command is working fine, both through
pgadmin III and at the shell. For example, "pg_dump -F c -b -v -f
/pgbackups/mydb2dump.backup mydb2". According to the online manual, I need
to manually recreate the database prior to restoring it (or I can use the -C
option in pg_restore to do this). However, when I use pg_restore, it always
complains that objects in the schema already exist. "pg_restore -C -d
template1 /pgbackups/mydb2dump.backup" returns with "pg_restore:
[archiver(db)] could not execute query: ERROR: schema "information_schema"
already exists". This is, again, both at the shell and with pgadmin. Am I
missing some option, or misunderstanding the process? My goal is to be able
to restore the entire database, including the schema, so that I can rebuild
my ListManager server from scratch in a disaster recovery. Thanks a bunch in
advance!

David Fenske
Information Technology Administrator
National Honey Board
davidf@nhb.org
(303)776-2337 x 27

Re: pg_restore returns error schema objects already exist

From
Tom Lane
Date:
DavidF@nhb.org writes:
> ... However, when I use pg_restore, it always
> complains that objects in the schema already exist. "pg_restore -C -d
> template1 /pgbackups/mydb2dump.backup" returns with "pg_restore:
> [archiver(db)] could not execute query: ERROR: schema "information_schema"
> already exists".

That's odd ... it sounds like pg_dump is dumping the information_schema,
which it should not do.  Perhaps you are accidentally using a pg_dump
from 7.3, which predates PG's support of information_schema?  Anyway I'm
pretty sure this must be some sort of version skew issue.  You generally
need to be sure that pg_dump is not any older than the server it is
dumping from.

            regards, tom lane

Re: pg_restore returns error schema objects already exist

From
brew@theMode.com
Date:
David.....

> I'm working with postgres for the first time, and trying to do some
> trial backups and restores.

I backup with pg_dump (using plain text with --clean to drop the
objects).  That way I can easily view the database.

To load the data back in I use the command line front end program psql :

psql dbname < backup.sql

However this doesn't work if you have binary data stored in the database.

I'm sure others on the list have some more ideas, too.

Welcome....

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================