Adventures in PostgreSQL, Episode 1

Episode 1: Restoring a Corrupted Template1 using Template0

Copyright © May 2002 Josh Berkus.
May be redistributed in unaltered form including this copyright notice. All other rights reserved.

All of us have had the experience where our typing fingers get ahead of our brains. Usually, for me, this happens on e-mail, but occasionally I really goof things up on my database server by pressing "Enter" at the wrong time. This was one of those times.

I'd dumped the database I was working on, and edited some referential integrity links and constraints (this is probably the best way to do this in PostgreSQL). Now, I was ready to re-load my edited database. On my console:

    chocolate-mousse:> psql -U postgres template1
    Welcome to psql, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help on internal slash commands
           \g or terminate with semicolon to execute query
           \q to quit

    template1=# DROP DATABASE staffos;
    DROP
    template1=# CREATE DATABASE staffos;
    CREATE
    template1=# \i staffos_revised.pgdump
    CREATE
    CREATE
    CREATE
    CREATE

    etc ...

Anybody spot my mistake? I did, after about 30 seconds ... which was 30 seconds too late. I'd loaded the entire database schema into Template1, the template database, and not the StaffOS database! This would mess up every database I created on the server thereafter.

(For those who don't know, Template1 is the PostgreSQL "database template". You should modify it when you want something -- for example, a user or the PL/pgSQL language -- added to all databases you create on that server. However, you do not want to use it as a regular database ... ever!)

What could I do? I could dump the entire server, INITDB, and reload all the databases one at a time (eleven databases - ouch!). I could try and clean up the Template1 database by hand, but with 28 tables, 40 views, and 85 functions in the StaffOS database, that solution would be nigh impossible.

So, instead, I got on the mailing lists. A helpful user on PGSQL-SQL pointed me to this documentation page.

Eureka! I cried. I was saved. In PostgreSQL 7.2, there was a second template database, Template0, which exists as an emergency backup to Template1 -- just in case anyone blundered as badly as me.

Speaking of blunders, I immediately backed up the whole server. Disk space is cheap, and one can't be too careful. (Note: If you proceed on any of the instructions below, be aware that messing with template1 and template0 can crash your database server. We are not responsible for any problems.)

However, to protect its status as an emergency backup and prevent mucking it up, too, Template0 is protected. Therefore:

    template1=# \c template0
    FATAL 1: Database "template0" is not currently accepting connections
    Previous connection kept

The secret is a "system table" called pg_databases. This table exists in all databases, and defines some basic properties for the databases. We're particularly interested in two of those properties, datistemplate and datallowconn. "datistemplate" tells us which are our template databases, and "datallowconn" tells us to which databases registered users may connect. If we look at the table, we see:

    template1=# select * from pg_database;
       datname   | datdba | encoding | datistemplate | datallowconn |
    -------------+--------+----------+---------------+--------------+
     backup_test |     27 |        0 | f             | t            |
     britlist    |     27 |        0 | f             | t            |
     dcl         |      1 |        0 | f             | t            |
     template0   |      1 |        0 | t             | f            |
     kevinprob   |     28 |        0 | f             | t            |
     kitchen     |      1 |        0 | f             | t            |
     oooconlist  |    103 |        0 | f             | t            |
     regression  |      1 |        0 | f             | t            |
     staffos     |     27 |        0 | f             | t            |
     template1   |      1 |        0 | t             | t            |

    etc ...

In order to connect to template0, we need to change that flag:

    template1=# UPDATE pg_database SET datallowconn = TRUE
    template1-# WHERE datname = 'template0';
    UPDATE 1

Now we can connect, and drop the Template1 database in order to replace it with a copy of Template0.

    template1=# \c template0
    CONNECT
    template0=# drop database template1;
    ERROR: DROP DATABASE: database is marked as a template

Ooops! We need to set another flag:

    template0=# UPDATE pg_database SET datistemplate = FALSE
    template0-# WHERE datname = 'template1';
    UPDATE 1

Now, we can (this is the risky part, backup first!):

    template0=# drop database template1;
    DROP
    template0=# create database template1 with template = template0;
    CREATE

Whew! Quickly, now, lets put stuff back the way we found it:

    template0=# UPDATE pg_database SET datistemplate = TRUE
    template0-# WHERE datname = 'template1';
    UPDATE 1
    template0=# \c template1
    CONNECT
    template1=# UPDATE pg_database SET datallowconn = FALSE
    template1-# WHERE datname = 'template0';

Unfortunately, I have lost a few things in this process. I had several functions set up in Template1 that I wanted to keep there, such as the results of "createlang plpgsql" and a few text-parsing functions. These I had to re-create by hand. I reccomend that you create a file of these Template1 modifications somewhere to save yourself the effort.

Finally, acting on the advice of the docs, I did one more thing to ensure maximum efficiency from my database server:

    template1-# VACUUM FULL FREEZE;
    VACUUM

Whew! There we are, back in business!