Hi all,
I'm trying to do a pg_dump of my database but I'm getting this error:
# pg_dump misp > misp.sql
pg_dump: query returned more than one (2) pg_database entry for database
"misp"
I can connect to the database OK, I just can't dump it. So I connected
as postgres to look at the pg_database table:
misp=# SELECT oid,* from pg_database;
oid | datname | datdba | encoding | datistemplate | datallowconn
| datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig |
datacl
----------+-----------+--------+----------+---------------+-------------
-+---------------+--------------+--------------+---------+-----------+--
---------------
9019080 | webstats | 101 | 0 | f | t
| 16974 | 427 | 427 | | |
21676718 | webstats | 1 | 0 | f | t
| 16974 | 427 | 427 | | |
1 | template1 | 1 | 0 | t | t
| 16974 | 427 | 427 | | |
{=,postgres=CT}
16975 | template0 | 1 | 0 | t | f
| 16974 | 427 | 427 | | |
{=,postgres=CT}
15062595 | misp_jon2 | 101 | 0 | f | t
| 16974 | 427 | 427 | | |
25823532 | misptest | 1 | 0 | f | t
| 16974 | 427 | 427 | | |
3988824 | devices | 1 | 0 | f | t
| 16974 | 427 | 427 | | |
30149940 | status | 1 | 0 | f | t
| 16974 | 427 | 427 | | |
30153251 | mispcopy | 1 | 0 | f | t
| 16974 | 427 | 427 | | |
31238435 | misp | 1 | 0 | f | t
| 16974 | 440751129 | 3661976602 | | |
6790290 | misp | 1 | 0 | f | t
| 16974 | 2300049162 | 1226307340 | | |
7511185 | misp_jon | 101 | 0 | f | t
| 16974 | 427 | 427 | | |
(12 rows)
This shows 2 entries for "misp" with different OIDs. Only the first one
(oid 31238435) exists in the data/base directory. So I tried to delete
the other row but it doesn't work:
misp=# DELETE from pg_database where oid = 6790290;
DELETE 0
Several sources I found via Googling suggested this should work. But it
doesn't, so I'm stuck. Things I've tried are:
1. Same thing in standalone mode - no difference
2. VACUUM on misp and pg_database
3. UPDATE on pg_database to change the name of the bogus row - this
doesn't match any rows either just like the DELETE.
Any help would be greatly appreciated.
Thanks,
James