Обсуждение: Problem with template1 database

Hello,

I use the PostgreSQL 7.4.11 server on FC3 (RPM install). It successfully runs a database.

I want to dump all the content with the command "pg_dumpall > /tmp/dumpPg".

But I get the error

pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
DETAIL:  The database subdirectory "/var/lib/pgsql/data/base/1" is missing.

And effectively the /var/lib/pgsql/data/base/1 directory is missing on my server.

Is there a way to reconstruct it?

I've tried the command "createdb -T template0 template1" but I get the same error.
I have a backup of /var/lib/pgsql/data/base/1 directory but the data directory was initialized by PostgreSQL version 7.3 which is not compatible with my current 7.4.11 version. Unfortunately I can't reinstall 7.3 to dump template1.

Thanks by advance for your help!

Regards,
Marc

---

Découvrez un nouveau moyen de poser toutes vos questions quelque soit le sujet ! Yahoo! Questions/Réponses pour partager vos connaissances, vos opinions et vos expériences. Cliquez ici.

On Fri, Sep 15, 2006 at 02:46:21PM +0200, toto titi wrote:
>  pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
>  DETAIL:  The database subdirectory "/var/lib/pgsql/data/base/1" is missing.
>
>  And effectively the /var/lib/pgsql/data/base/1 directory is missing on my server.
>
>  Is there a way to reconstruct it?

The following article discusses reconstructing template1:

http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php

If you can connect to any database then you shouldn't need to go
through the extra steps required to connect to template0.  It does
look like you'll need to drop template1, as the DETAIL message
implies that pg_database has a row for template1 but the data
directory is missing (as opposed to pg_database not having a row
for template1).  Something like this should work:

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 TEMPLATE = template0;
UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';

You might wish to practice this procedure on a test cluster before
doing it for real.  You could initdb a test cluster, start a
postmaster on it (running on a different port or different machine),
create a database, remove the data/base/1 directory (e.g., "rm -rf
data/base/1"), connect to the database you created, then run the
above commands.

Do you know how template1's directory went missing?  That's a mystery
that deserves investigation so you can prevent it from happening again.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Fri, Sep 15, 2006 at 02:46:21PM +0200, toto titi wrote:
>> pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
>> DETAIL:  The database subdirectory "/var/lib/pgsql/data/base/1" is missing.
>>
>> And effectively the /var/lib/pgsql/data/base/1 directory is missing on my server.

> Do you know how template1's directory went missing?  That's a mystery
> that deserves investigation so you can prevent it from happening again.

Indeed.  If you don't know that, I'm not sure that I'd trust the cluster
enough to try to continue to work with it.  I'd suggest dumping whatever
databases you have individually with pg_dump (instead of using
pg_dumpall) and then initdb and reload.  Note that pg_dump alone won't
save info about your users and groups, so make notes about those so you
can re-create them by hand.

            regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> a écrit :

> Michael Fuhr <mike@fuhr.org> writes:
> > On Fri, Sep 15, 2006 at 02:46:21PM +0200, toto
> titi wrote:
> >> pg_dumpall: could not connect to database
> "template1": FATAL:  database "template1" does not
> exist
> >> DETAIL:  The database subdirectory
> "/var/lib/pgsql/data/base/1" is missing.
> >>
> >> And effectively the /var/lib/pgsql/data/base/1
> directory is missing on my server.
>
> > Do you know how template1's directory went
> missing?  That's a mystery
> > that deserves investigation so you can prevent it
> from happening again.
>
> Indeed.  If you don't know that, I'm not sure that
> I'd trust the cluster
> enough to try to continue to work with it.  I'd
> suggest dumping whatever
> databases you have individually with pg_dump
> (instead of using
> pg_dumpall) and then initdb and reload.  Note that
> pg_dump alone won't
> save info about your users and groups, so make notes
> about those so you
> can re-create them by hand.

Thanks Tom and Michael for your answers. After a try
on a test server, I've applied your procedure Tom and
now it works perfectly well!

I could not figure out why template1 directory went
missing.

--
Marc







___________________________________________________________________________
Découvrez un nouveau moyen de poser toutes vos questions quelque soit le sujet !
Yahoo! Questions/Réponses pour partager vos connaissances, vos opinions et vos expériences.
http://fr.answers.yahoo.com