Re: Problem with template1 database

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