Обсуждение: Problems when dumping a database

Hi all,

My pgsql DBMS works great except that when I want to dump a database,
pg_dump says that database template1 doesn't exist (actually it does !)
and the dump is aborted...
Any ideas ???

Thanks a lot

Lionel

Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> My pgsql DBMS works great except that when I want to dump a database,
> pg_dump says that database template1 doesn't exist (actually it does !)
> and the dump is aborted...

Curious.  Can you connect to template1 by hand (eg "psql template1")?

If not, try issuing "vacuum pg_database" (you can do this from any
database not only template1) to see if it helps.

If that doesn't fix it, we'll need to see the exact output from pg_dump,
as well as what shows up in the postmaster log.

            regards, tom lane

Le 20.02.01 à 13:18, "Tom Lane" écrivait :

)Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
)> My pgsql DBMS works great except that when I want to dump a database,
)> pg_dump says that database template1 doesn't exist (actually it does !)
)> and the dump is aborted...
)
)Curious.  Can you connect to template1 by hand (eg "psql template1")?

Yes I can. But something strange is that I can use the tables of the
template1 database :

template1=> select * from pg_shadow;     (this will work)

But I cannot list them :

template1=> \d
Couldn't find any tables, sequences or indices!

)If not, try issuing "vacuum pg_database" (you can do this from any
)database not only template1) to see if it helps.

Doesn't change nothing in my case...

)If that doesn't fix it, we'll need to see the exact output from pg_dump,
)as well as what shows up in the postmaster log.

Output of pg_dump :
$ pg_dump tressens -u
Username: tressens
Password:

pg_dump: couldn't find the template1 database.  You are really hosed.
Giving up.

Nothing strange in the logs but I typed by hand what postmaster does when
he's looking for template1 in pg_database :
SELECT oid FROM pg_database WHERE datname = 'template1'

But this SELECT returns two tuples ! I have two identical tuples in
pg_database for template1.
Perhaps deleting one will help me, but which one (equal ?).


What do you think about it ?

Thanks a lot.

Lionel

Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> Le 20.02.01 � 13:18, "Tom Lane" �crivait :
> )Tressens Lionel <tressens@etud.insa-tlse.fr> writes:
> )> My pgsql DBMS works great except that when I want to dump a database,
> )> pg_dump says that database template1 doesn't exist (actually it does !)
> )> and the dump is aborted...
> )
> )Curious.  Can you connect to template1 by hand (eg "psql template1")?

> Yes I can. But something strange is that I can use the tables of the
> template1 database :

> template1=> select * from pg_shadow;     (this will work)

> But I cannot list them :

> template1=> \d
> Couldn't find any tables, sequences or indices!

Hm.  Not finding any user tables in template1 is normal behavior ---
try \dS to view the system tables.  However, I now have a theory,
because psql has reported this condition as 'No relations found.'
since 7.0 or before.  Evidently you are invoking an old (6.5 or before)
psql.  What version is your database server, and is it possible you're
calling a psql of an older version?  And, even more to the point, a
pg_dump also of an older version?

> SELECT oid FROM pg_database WHERE datname = 'template1'

> But this SELECT returns two tuples ! I have two identical tuples in
> pg_database for template1.
> Perhaps deleting one will help me, but which one (equal ?).

This is definitely uncool, but I'm not sure if it's related to the
pg_dump problem or not.  You could try deleting the one of larger OID
(the correct OID for template1 is about 17000 depending on what version
you're running) and then do a pg_database vacuum.

            regards, tom lane

Le 22.02.01 à 22:23, "Tom Lane" écrivait :

)> SELECT oid FROM pg_database WHERE datname = 'template1'
)
)> But this SELECT returns two tuples ! I have two identical tuples in
)> pg_database for template1.
)> Perhaps deleting one will help me, but which one (equal ?).
)
)This is definitely uncool, but I'm not sure if it's related to the
)pg_dump problem or not.  You could try deleting the one of larger OID
)(the correct OID for template1 is about 17000 depending on what version
)you're running) and then do a pg_database vacuum.

Thanks Tom, it worked.
I had these tuples :
template1=> select oid from pg_database where datname = 'template1';
   oid
------
 17120
459008
(2 rows)

I deleted the second one and it works now...

Thanks again.

Lionel