pg_dump: missing pg_database entry
Hello,
I'm experiencing a strange problem with PostgreSQL 7.4.9.
One of my database production servers has 2 large databases, it's still
possible to connect to them and pass queries, but the pg_database
system table is empty, which prohibits such actions as dumping the
databases.
For instance:
postgres@dial-bdd1:~$ pg_dump maf
pg_dump: missing pg_database entry for database "maf"
does not work,but:
postgres@dial-bdd1:~$ psql maf
Welcome to psql 7.4.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
maf=#
This works, but the pg_database looks like it's empty:
maf=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datvacuumxid | datfrozenxid | datpath | datconfig |
datacl
---------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------+-----------+--------
(0 rows)
I tried to repair the system indexes, but it doesn't work either...
Thanks in advance for your help.
gl@lbn.fr wrote:
Hello,
I'm experiencing a strange problem with PostgreSQL 7.4.9.
One of my database production servers has 2 large databases, it's still
possible to connect to them and pass queries, but the pg_database
system table is empty, which prohibits such actions as dumping the
databases.
I believe that postgresql keeps a plaintext copy of the database table,
because it can't access that table until you are connected.
I'd suggest you make a backup of you whole data directory immediatly, in
case things get worse (e.g. some tries to create a database, and this causes
the plaintext copy to be overwritten).
One reason the pg_database table seems to be empty could be oid wraparound.
Has this database been vacuumed regularly? If not, try doing a "vacuum full"
now - according to some earlier discussion about oid wraparound on this list
this should fix the problem if the wraparound hasn't happend too long ago.
But, in any case, take a (filesystem leven) backup of your database NOW, before
you do anything else.
greetings, Florian Pflug
gl@lbn.fr writes:
I'm experiencing a strange problem with PostgreSQL 7.4.9.
One of my database production servers has 2 large databases, it's still
possible to connect to them and pass queries, but the pg_database
system table is empty, which prohibits such actions as dumping the
databases.
Sounds like XID wraparound. Does vacuuming pg_database make the problem
go away? If so, I'd recommend doing database-wide vacuums in all your
databases ASAP. And then instituting a regular vacuum maintenance schedule.
regards, tom lane
Tom Lane a écrit :
gl@lbn.fr writes:
I'm experiencing a strange problem with PostgreSQL 7.4.9.
One of my database production servers has 2 large databases, it's still
possible to connect to them and pass queries, but the pg_database
system table is empty, which prohibits such actions as dumping the
databases.Sounds like XID wraparound. Does vacuuming pg_database make the problem
go away? If so, I'd recommend doing database-wide vacuums in all your
databases ASAP. And then instituting a regular vacuum maintenance schedule.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
Thanks for your answers.
Indeed, the vacuum schedule was not regularly executed. Manually
launching the procedure solved the problem.
Regards,
GL