pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrelation tmp_pg_shadow already exists
3+ messages / 2 participants
[nested] [flat]
* relation tmp_pg_shadow already exists
@ 2005-01-31 16:33 Ben Kim <bkim@coe.tamu.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Ben Kim @ 2005-01-31 16:33 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: relation tmp_pg_shadow already exists
@ 2005-01-31 17:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ben Kim <bkim@coe.tamu.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-01-31 17:39 UTC (permalink / raw)
To: Ben Kim <bkim@coe.tamu.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: relation tmp_pg_shadow already exists
@ 2005-01-31 20:23 Ben Kim <bkim@coe.tamu.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Ben Kim @ 2005-01-31 20:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-01-31 20:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-31 16:33 relation tmp_pg_shadow already exists Ben Kim <bkim@coe.tamu.edu>
2005-01-31 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-31 20:23 ` Ben Kim <bkim@coe.tamu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox