Обсуждение: 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