04) Template0

Created mercredi 17 février 2016

1 chocolate-mousse:> psql -U postgres template1
2 	Welcome to psql, the PostgreSQL interactive terminal.
3 
4 	Type:  \copyright for distribution terms
5 		   \h for help with SQL commands
6 		   \? for help on internal slash commands
7 		   \g or terminate with semicolon to execute query
8 		   \q to quit
9 
10 	template1=# DROP DATABASE staffos;
11 	DROP
12 	template1=# CREATE DATABASE staffos;
13 	CREATE
14 	template1=# \i staffos_revised.pgdump
15 	CREATE
16 	CREATE
17 	CREATE
18 	CREATE
19 
20 	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:

1    template1=# \c template0
2 	FATAL 1: Database "template0" is not currently accepting connections
3 	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:

1   template1=# select * from pg_database;
2 	   datname   | datdba | encoding | datistemplate | datallowconn |
3 	-------------+--------+----------+---------------+--------------+
4 	 backup_test |     27 |        0 | f             | t            |
5 	 britlist    |     27 |        0 | f             | t            |
6 	 dcl         |      1 |        0 | f             | t            |
7 	 template0   |      1 |        0 | t             | f            |
8 	 kevinprob   |     28 |        0 | f             | t            |
9 	 kitchen     |      1 |        0 | f             | t            |
10 	 oooconlist  |    103 |        0 | f             | t            |
11 	 regression  |      1 |        0 | f             | t            |
12 	 staffos     |     27 |        0 | f             | t            |
13 	 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!