Re: Problem restoring database

"Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> writes:
> I have a big problem restoring a database. I have two backups, one made
> with pgadmin from my client, and one that is made nightly on the server
> with the local pg_dump. The PG version is 7.4.17 on RHEL4.

> local restore with pg_restore, using the local made backup:
> ...
> pg_restore: creating SEQUENCE sequence
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation
> "sequence" already exists

This one is failing because you are restoring into a database that
already contains objects --- at least sequence "sequence", and maybe
others.  A likely explanation is that you've created those objects
in template1 and so they're being copied into any new database.
You should either clean out template1, or be careful to clone new
databases from template0 instead.

> using pgadmin 1.8 from my client, using the server-made backup:
> ...
> pg_restore: [archiver (db)] could not execute query: ERROR:
> unrecognized configuration parameter "standard_conforming_strings"
>     Command was: SET standard_conforming_strings = off;

This one is evidently failing because the backup was made with a version
of pg_dump that's newer than 7.4.x.  pg_dump output in general can be
loaded into a server that's *newer* than the pg_dump, but not one that's
*older*; at least not without doing manual surgery on the dump script
to get rid of any commands the older server doesn't understand.

> pg_restore: restoring large object OID 191837784
> pg_restore: [archiver] could not create large object 191837784
> pg_restore: *** aborted because of error

Probably this is also a version-skew problem, though it's hard to be
sure without knowing which pg_dump version we're dealing with.  The
other failures you show definitely fall under that category.

            regards, tom lane