Can't pg_dumpall, claims database exists twice

On Mar 28, 2005, at 2:51 AM, Scott Ribe wrote:

This is 8.0.1 on OS X; where do I start on straightening this out?
(There is
only 1 postmaster running, and it seems OK from client apps, both my
own app
and psql.)

My first thought would be to look at the query preceding this error
message (dumpDatabase in pg_dump.c) and try to determine why you are
getting more than one database. Just from looking at this code, it
seems like a mismatch between database version and pg_dump version
might be a likely candidate.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Scott Ribe <scott_ribe@killerbytes.com> writes:

pg_dumpall is failing with this error:
pg_dump: query returned more than one (2) pg_database entry for database
"pedcard"
pg_dumpall: pg_dump failed on database "pedcard", exiting

This is 8.0.1 on OS X; where do I start on straightening this out?

"select * from pg_database" might provide some clues ... if it does in
fact show more than one row for "pedcard", it'd be useful to look at the
system columns too:
select ctid,oid,xmin,cmin,xmax,cmax,* from pg_database

regards, tom lane

On Mon, Mar 28, 2005 at 12:51:19AM -0700, Scott Ribe wrote:

pg_dumpall is failing with this error:

pg_dump: query returned more than one (2) pg_database entry for database
"pedcard"
pg_dumpall: pg_dump failed on database "pedcard", exiting

This is 8.0.1 on OS X; where do I start on straightening this out? (There is
only 1 postmaster running, and it seems OK from client apps, both my own app
and psql.)

What's the result of the following query?

SELECT tableoid, xmin, xmax, oid, datname, datdba,
datvacuumxid, datfrozenxid
FROM pg_database
ORDER BY datname, oid;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

My first thought would be to look at the query preceding this error
message (dumpDatabase in pg_dump.c) and try to determine why you are
getting more than one database. Just from looking at this code, it
seems like a mismatch between database version and pg_dump version
might be a likely candidate.

I only have 8.0.1 on this machine. I removed the prior version before
installing.

--
Scott Ribe
scott_ribe@nospam.killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

select ctid,oid,xmin,cmin,xmax,cmax,* from pg_database

ctid | oid | xmin | cmin | xmax | cmax | datname | datdba |
encoding | datistemplate | datallowconn | datlastsysoid | datvacuumxid |
datfrozenxid | dattablespace | datconfig | datacl
-------+--------+--------+------+------+------+-----------+--------+--------
--+---------------+--------------+---------------+--------------+-----------
---+---------------+-----------+------------------------
(0,1) | 288848 | 787399 | 0 | 0 | 0 | pedcard | 1 |
0 | f | t | 17228 | 482 |
482 | 1663 | |
(0,3) | 220622 | 3149 | 0 | 0 | 0 | pedcard | 1 |
0 | f | t | 17228 | 770122 |
3221995595 | 1663 | |
(0,6) | 1 | 538 | 0 | 0 | 0 | template1 | 1 |
0 | t | t | 17228 | 482 |
482 | 1663 | | {postgres=CT/postgres}
(0,7) | 17229 | 539 | 0 | 0 | 0 | template0 | 1 |
0 | t | f | 17228 | 482 |
482 | 1663 | | {postgres=CT/postgres}
(4 rows)

--
Scott Ribe
scott_ribe@nospam.killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

What's the result of the following query?

SELECT tableoid, xmin, xmax, oid, datname, datdba,
datvacuumxid, datfrozenxid
FROM pg_database
ORDER BY datname, oid;

tableoid | xmin | xmax | oid | datname | datdba | datvacuumxid |
datfrozenxid
----------+--------+------+--------+-----------+--------+--------------+----
----------
1262 | 3149 | 0 | 220622 | pedcard | 1 | 770122 |
3221995595
1262 | 787399 | 0 | 288848 | pedcard | 1 | 482 |
482
1262 | 539 | 0 | 17229 | template0 | 1 | 482 |
482
1262 | 538 | 0 | 1 | template1 | 1 | 482 |
482
(4 rows)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:

select ctid,oid,xmin,cmin,xmax,cmax,* from pg_database

ctid | oid | xmin | cmin | xmax | cmax | datname | datdba |
encoding | datistemplate | datallowconn | datlastsysoid | datvacuumxid |
datfrozenxid | dattablespace | datconfig | datacl
-------+--------+--------+------+------+------+-----------+--------+--------
--+---------------+--------------+---------------+--------------+-----------
---+---------------+-----------+------------------------
(0,1) | 288848 | 787399 | 0 | 0 | 0 | pedcard | 1 |
0 | f | t | 17228 | 482 |
482 | 1663 | |
(0,3) | 220622 | 3149 | 0 | 0 | 0 | pedcard | 1 |
0 | f | t | 17228 | 770122 |
3221995595 | 1663 | |

Now how the heck did that happen? That's not some kind of weird UPDATE
failure, because the rows have different OIDs ... it seems like the
newer row must have been explicitly inserted, and it should surely have
been blocked by the unique index on datname. Are there subdirectories
under $PGDATA/base for both of those OIDs?

If so, I'd suggest renaming one DB or the other by brute force, eg
update pg_database set datname = 'pedcard2' where oid = 288848;
checkpoint;
and then looking to see what you've got. It's not obvious which of
these you want to keep.

regards, tom lane

Now how the heck did that happen? That's not some kind of weird UPDATE
failure, because the rows have different OIDs ... it seems like the
newer row must have been explicitly inserted, and it should surely have
been blocked by the unique index on datname. Are there subdirectories
under $PGDATA/base for both of those OIDs?

No, in fact there is only template1, template0, and 1 of the pedcard
instances. Am I right that this simplifies things greatly? That what I have
here is a single database with all the data in it, and some phantom entry in
pg_database that cannot possibly be used anywhere since it lacks associated
data files?

Could you give me an outline of how to clean this up? Should I be looking at
other catalog info to see if there are components of the db that are also
duplicated? Or should I just delete the bogus pg_database entry, then dump
and restore?

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

Now how the heck did that happen? That's not some kind of weird UPDATE
failure, because the rows have different OIDs ... it seems like the
newer row must have been explicitly inserted, and it should surely have
been blocked by the unique index on datname. Are there subdirectories
under $PGDATA/base for both of those OIDs?

So I guess I should also ask if there's anything I could do to give you more
information about how this might have happened? Would it be helpful to
brute-force rename the bogus entry and submit a dump of the schema?

--
Scott Ribe
scott_ribe@nospam.killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:

Now how the heck did that happen? That's not some kind of weird UPDATE
failure, because the rows have different OIDs ... it seems like the
newer row must have been explicitly inserted, and it should surely have
been blocked by the unique index on datname. Are there subdirectories
under $PGDATA/base for both of those OIDs?

No, in fact there is only template1, template0, and 1 of the pedcard
instances. Am I right that this simplifies things greatly?

Yeah, you can just brute-force delete the row with the OID that doesn't
correspond to any existing subdirectory. But that makes it even odder
... where did that row come from??? Have you done any database-level
operations at all lately?

regards, tom lane