Thread: Fixing the loss of '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. The immediate effect is that, although my schema and
data seem to be intact and are working correctly, pg_dumpall refused to
run because it can't find 'template1'.

The sequence was something like this:
1) first step was to bring my updates fully up to date by running
pg_dumpall with output to a USB hard drive and to run the incremental
backup that's part of my application (a mail archive that backs up into
a set of mbox files).

2)installed Fedora 16, including the Postgres 9.1 package, and fully
updated all packages

3)initialised the Postgres user and basic database structures, started
the server.

4)attempted to use pg_restore to recover my database. This threw lots of
errors and was obviously stupid, so I killed it and reloaded the backup
with psql. This ran OK except that some data was incorrectly restored to
my mail messages table because tabs in some messages confused the COPY
command.

5) Started again: I dropped my schema and recreated it before letting my
application restore the database from its mbox files.

6) The next scheduled backup using pg_dumpall failed immediately because
it couldn't find 'template1'.

Observations
============
a) could the abrupt termination of pg_restore have removed 'template1'
from the database structure?

b) I've seen the confusion between tabs in data and the tabs used as
delimiters by pg_dump cause this problem in the past. It was fixed then,
but seems to have crept back in.

Question
========
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?


Martin

Martin,

> 6) The next scheduled backup using pg_dumpall failed immediately because
> it couldn't find 'template1'.

The template1 database is the default database to which pg_dumpall attempts
to connect.  If you use the -l or --database option, you can change that and
pg_dumpall will resume functioning as you expect.

--
Gary Chambers

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;

> 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

Marti,

> As far as I can tell, the only way to remove the template1 database is to
> rename it.

Templates are databases with the datistemplate column set to true.  A
superuser can:

UPDATE pg_database SET datistemplate=false where datname='blah';
DROP DATABASE blah;

As far as Pg is concerned, there is no problem with removing the template1
or postgres databases.  That is not (or may not be) the case for some
utilities and Linux distributions out there that expect them to exist where
used as defaults.  I haven't yet tried removing template0.

--
Gary Chambers

On Wednesday, March 07, 2012 6:23:42 am Martin Gregorie wrote:

>
> Question
> ========
> 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?

So the question is, did you have your template1 customized in the 8.4
installation and are you looking to transfer those added items to the 9.1
installation?  Some information on the template system can be found here:

http://www.postgresql.org/docs/9.1/interactive/manage-ag-templatedbs.html

As others have said you can recreate a bare template1 from template0.  If you
had not customized template1 that should do.  If you had customized it:

On the chance that the 8.4 cluster is still up and running you could just do a
pg_dump of template1 on that cluster and load it into the new cluster. If the
8.4 cluster is not running you can go to your pg_dumpall file and get the
template1 objects from there. Search for the  \connect template1 string and look
for the SQL commands until the next \connect string.


>
>
> Martin

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, 2012-03-07 at 09:38 -0500, Gary Chambers wrote:
> Martin,
>
> > 6) The next scheduled backup using pg_dumpall failed immediately because
> > it couldn't find 'template1'.
>
> The template1 database is the default database to which pg_dumpall attempts
> to connect.  If you use the -l or --database option, you can change that and
> pg_dumpall will resume functioning as you expect.
>
I've just logged in under postgres and run '\l' - and the databases
postgres, template0 and template1 are still there:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres          +
           |          |          |             |             |
postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres=CTc/postgres+
           |          |          |             |             |
=c/postgres

So, is this a privilege issue? I don't understand the content of that
somewhat cryptic 'privilege' column. Is it set how you'd expect?

Marti:
======
I got this output:

postgres=# SELECT datname FROM pg_database WHERE datistemplate;
  datname
-----------
 template0
 template1
(2 rows)

so it doesn't look like its been renamed.


Martin

On Wednesday, March 07, 2012 6:23:42 am Martin Gregorie wrote:

> Question
> ========
> 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?


Was reading through this again and got to wondering, is pg_dumpall really
necessary? Do you have multiple databases you want to dump at once? If not a
plain pg_dump against a particular database would work. There is also the
advantage that if you use pg_dump -Fc you can use pg_restore. My use case for
pg_dumpall is pg_dumpall -g to get the global information only from the cluster.
I then use pg_dump for individual databases. You would still want to recreate a
template1 because quite a few tools expect it to be there.



--
Adrian Klaver
adrian.klaver@gmail.com

On Wednesday, March 07, 2012 7:19:51 am Martin Gregorie wrote:
> On Wed, 2012-03-07 at 09:38 -0500, Gary Chambers wrote:
> > Martin,
> >
> > > 6) The next scheduled backup using pg_dumpall failed immediately
> > > because it couldn't find 'template1'.
> >
> > The template1 database is the default database to which pg_dumpall
> > attempts to connect.  If you use the -l or --database option, you can
> > change that and pg_dumpall will resume functioning as you expect.
>
> I've just logged in under postgres and run '\l' - and the databases
> postgres, template0 and template1 are still there:
>
> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
> privileges
> -----------+----------+----------+-------------+-------------+-------------
> ---------- postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>
> postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> postgres=CTc/postgres+
>
> =c/postgres
>
> So, is this a privilege issue? I don't understand the content of that
> somewhat cryptic 'privilege' column. Is it set how you'd expect?

The privilege code can be found below, look towards bottom of page:

http://www.postgresql.org/docs/9.1/interactive/sql-grant.html

The privileges look alright, same as mine.

So what user are you trying to restore the pg_dumpall data as?
What is the exact error message you get?

>
> Marti:
> ======
> I got this output:
>
> postgres=# SELECT datname FROM pg_database WHERE datistemplate;
>   datname
> -----------
>  template0
>  template1
> (2 rows)
>
> so it doesn't look like its been renamed.
>
>
> Martin

--
Adrian Klaver
adrian.klaver@gmail.com

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;

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

Martin Gregorie <martin@gregorie.org> writes:
> I've just logged in under postgres and run '\l' - and the databases
> postgres, template0 and template1 are still there:

Oh, time for a new theory then.  What's the *exact* error message you
were getting from pg_dump?  Do you get the same from a manual attempt to
connect to template1?

> So, is this a privilege issue? I don't understand the content of that
> somewhat cryptic 'privilege' column. Is it set how you'd expect?

It looks reasonable offhand.  I'm a bit surprised that the public entry
isn't first for template1, but I think that shouldn't make any
difference.

            regards, tom lane

Martin Gregorie <martin@gregorie.org> writes:
> On Wed, 2012-03-07 at 10:50 -0500, Tom Lane wrote:
>> Oh, time for a new theory then.  What's the *exact* error message you
>> were getting from pg_dump?  Do you get the same from a manual attempt to
>> connect to template1?

> Yes:

> # psql -d template1 -U postgres
> psql: FATAL:  Peer authentication failed for user "postgres"

Oh, well, that hasn't got anything to do with the database permissions,
nor template1 for that matter.  That says you're trying to log in as
postgres and the OS reports that you're not postgres.  You either need
to su to postgres or change your authentication method.

            regards, tom lane