Re: relation tmp_pg_shadow already exists

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