Обсуждение: relation tmp_pg_shadow already exists

Operating System: Fedora core 1
From: postgresql 7.2 database
To: postgresql 7.3 database

I'm trying to restore a tar dump of postgresql 7.2 databases into a 7.3
server. I have two databases (db1,db2) both containing binary data. The
tar was dumped with the following command:

    pg_dump -C -b -F t -i db1.tar db1

The first one was restored OK. But on the second one,

    pg_restore -d db2 db2.tar

gives me this error.
=======================
$ pg_restore -v -d db2 db2.tar
pg_restore: connecting to database for restore
pg_restore: creating TABLE tmp_pg_shadow
pg_restore: connecting to database db2 as user u2
pg_restore: [archiver (db)] could not execute query: ERROR:  Relation
'tmp_pg_shadow' already exists
pg_restore: *** aborted because of error

These are the lines containing this table name from restore.sql within the
dump tarball.
=======================
$grep tmp_pg restore.sql
DROP TABLE "".tmp_pg_shadow;
-- Name: tmp_pg_shadow; Type: TABLE; Schema: ; Owner: u2
CREATE TABLE tmp_pg_shadow (
    datdba integer
);
-- Name: tmp_pg_shadow; Type: TABLE DATA; Schema: ; Owner: u2
COPY tmp_pg_shadow (datdba) FROM stdin;
copy tmp_pg_shadow (datdba)  from '$$PATH$$/75.dat' ;


I found 2 questions regarding this problem from my google search, both of
them unanswered, but hope there's a way to circumvent this error, and
would appreciate any help.


Regards,

Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University

Ben Kim <bkim@coe.tamu.edu> writes:
> I'm trying to restore a tar dump of postgresql 7.2 databases into a 7.3
> server. I have two databases (db1,db2) both containing binary data. The
> tar was dumped with the following command:

>     pg_dump -C -b -F t -i db1.tar db1

> The first one was restored OK. But on the second one,

>     pg_restore -d db2 db2.tar

> gives me this error.

I'm fairly suspicious of the -i switch you are using.  As a general
rule, you should NEVER use -i unless you have been told to by someone
who knows exactly what they are doing.  That safety check is there for
a reason.

Other than that, though, the only thought that comes to mind is that you
may be confused about the use of -C.  IIRC, specifying it at dump time
is useless --- it needs to be given to pg_restore instead.  You have
evidently got a non-empty (already restored into?) target database and
I think you are expecting pg_restore to clean it out, which it will not
with these command line options.

            regards, tom lane

Thanks for the prompt help. The problem seems to be missing users.
Removing -C revealed the problem.

> Other than that, though, the only thought that comes to mind is that you
> may be confused about the use of -C.  IIRC, specifying it at dump time
> is useless --- it needs to be given to pg_restore instead.  You have
> evidently got a non-empty (already restored into?) target database and
> I think you are expecting pg_restore to clean it out, which it will not
> with these command line options.

I was not aware that -C was useless for non-text dumps. I created another
dump with no "-C" and restored from it. This time, the restore started OK,
with no tmp_pg_shadow related error. However, there were a few users that
were missing in the new server. For each missing user and group, the
restore operation aborted. If I add the user and run pg_restore again, I
saw the tmp_pg_shadow error again.

If I dropdb, createdb, and run restore again, the restore proceeds without
tmp_pg_shadows error until it meets another missing user, and so on.

When I've added all missing users and groups, and dropdb, createdb'd, the
restore succeeded with no error.

After that, I tried dropdb, createdb and tried the old dump (created with
-C) and this time, it also succeeded with no error.

For some reason, using -C with pg_restore didn't work with either of the
dumps. (I see the same error with and without -C.)
=================
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] connection to database "db1" failed:
FATAL:  Database "db1" does not exist in the system catalog.


> I'm fairly suspicious of the -i switch you are using.  As a general
> rule, you should NEVER use -i unless you have been told to by someone
> who knows exactly what they are doing.  That safety check is there for
> a reason.

At least this time, it didn't make a difference. Does this mean that
-i should only be used for "very old versions (currently prior to 7.0)" as
in manpage?


One question is, is it possible with pg_dump to create a dump including
users/groups, when not using pg_dumpall?



Regards,

Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University