Thread: 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? > > http://archives.postgresql.org Thanks for your answers. Indeed, the vacuum schedule was not regularly executed. Manually launching the procedure solved the problem. Regards, GL