Re: Fixing the loss of 'template1'

On 03/07/2012 09:43 AM, Marti Raudsepp wrote:

On Wed, Mar 7, 2012 at 16:23, Martin Gregorie <martin@gregorie.org> wrote:

Is it possible to reinstate 'template1' and all its works without
reinitialising the database from scratch. It was suggested to me that
running "initdb" might fix it while leaving my data in place though my
source was uncertain about its success. Would this work or is there a
better way to restore 'template1' in a populated database system?

You can always re-create template1 from template0:

CREATE DATABASE template1 WITH TEMPLATE template0;

This is what I have done when I lost template1.

In the course of migrating from (I think) Postgres 8.4 under Fedora 12
to Postgres 9.1 under Fedora 16 I managed to loose 'template1' and
associated data.

As far as I can tell, the only way to remove the template1 database is
to rename it. Maybe that's what happened. This query should tell you
the name of the database that used to be template1:

SELECT datname FROM pg_database WHERE datistemplate;

Regards,
Marti