From: | Bill McGonigle <mcgonigle(at)medicalmedia(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_dumpall storing multiple copies of DB's? |
Date: | 2002-03-05 16:41:40 |
Message-ID: | DE499D20-3057-11D6-9492-003065EAE3C0@medicalmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Goodwin | 2002-03-05 16:46:49 | Dynamic SQL in function |
Previous Message | Gregory Wood | 2002-03-05 16:36:07 | Re: storing intermediate results in recursive plpgsql functions |