Thread: Can't pg_dumpall, claims database exists twice
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.) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
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