Re: Can't pg_dumpall, claims database exists twice

Scott Ribe <scott_ribe(at)killerbytes(dot)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