Re: pg_dumpall storing multiple copies of DB's?

On Tuesday, March 5, 2002, at 10:48 , Tom Lane wrote:

> Indeed.  Did you perhaps have multiple pg_shadow entries matching the
> database's datdba in the old installation?

It's possible the old system, the one I inherited, did have this
problem.  That's since been recycled, so it's hard to say.  A dump from
that system was loaded onto the current system.  The other pg_shadow
data arrived intact.  Unless 7.1.3 has code to prevent duplicates and
7.0 (old system) didn't I would suspect pg_shadow looks the same now as
it did then.

The current system, which only has one pg_shadow entry for the
database's datdba, is still producing the dumps with multiple copies.

When I run:

SELECT datname, coalesce(usename, (select usename from pg_shadow where
usesysid=(select datdba from pg_database where datname='template0'))),
pg_encoding_to_char(d.encoding), datistemplate, datpath FROM pg_database
d LEFT JOIN pg_shadow u ON (datdba = usesysid) WHERE datallowconn;

(from pg_dumpall) I only see one copy of the database.

Two questions come to mind:

How does pgsql handle this case?  Does it iterate over every OID?  Just
the first?  I don't appear to be suffering any data loss or corruption.

What's the best way to, a) determine the copy of the table to keep and
b) do a new dump with just that one?  pg_dump doesn't seem to accept any
options for OID's, except to ignore them.  Hmmm, as I typed that, it
occured to me that perhaps an OID-less dump would do the trick - on
import it would either duplicate an insert or give an error, depending
on the table constraints.

-Bill