Infrastructure at your Service

Daniel Westermann

What the hell are these template0 and template1 databases in PostgreSQL?

When people start to work with PostgreSQL, especially when they are used to Oracle, some things might be very confusing. A few of the questions we usually get asked are:

  • Where is the listener and how can I configure it?
  • When you talk about a PostgreSQL cluster where are the other nodes?
  • Why do we have these template databases and what are they used for?
  • …and some others…

In this post we’ll look at the last point: Why do we have two template databases (template0 and template1) and additionally a database called “postgres”? That makes three databases by default. In Oracle we only have one, well two when you use pluggable databases (the root and pdb$seed). Why does PostgreSQL need three by default? Isn’t that only overhead? Lets see …

To begin with: Assuming these databases are really not required we can drop them, can’t we?

([email protected][local]:5432) [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 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Can we drop the “postgres” database?

([email protected][local]:5432) [postgres] > drop database postgres;
ERROR:  cannot drop the currently open database
Time: 1.052 ms

Ok, this is the first point to remember: You can not drop a database which users are currently connected to (in this case it is my own connection). So lets try to connect to template1 and then drop the “postgres” database:

[email protected]:/home/postgres/ [PG960] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.

([email protected][local]:5432) [template1] > drop database postgres;
DROP DATABASE
Time: 489.287 ms
([email protected][local]:5432) [template1] > 

Uh, our default “postgres” database is gone. Does it matter? Not really from a PostgreSQL perspective but probably all your clients (pgadmin, monitoring scripts, …) will have a problem now:

[email protected]:/home/postgres/ [postgres] psql
psql: FATAL:  database "postgres" does not exist

The “postgres” database is meant as a default database for clients to connect to. When you administer a PostgreSQL instance which runs under the postgres operating system user the default database that is used for a connection is the same as the username => postgres. Now that this database does not exist anymore you can not longer connect if you do not provide a database name in you connection request. But we can still connect to “template1”:

[email protected]:/home/postgres/ [postgres] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.

Second thing to remember: The “postgres” database is meant as a default database for connections. It is not required, you can drop it but probably a lot of tools you use will need to be adjusted because they assume that the “postgres” database is there by default.

Luckily we can easy recover from that:

[email protected]:/home/postgres/ [postgres] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.

([email protected][local]:5432) [template1] > create database postgres;
CREATE DATABASE
([email protected][local]:5432) [template1] > \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 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

What happened? We connected to template1 again and re-created the “postgres” database. Of course everything we added to the “postgres” database before we dropped it is not any more available. This brings us to the next question: When we create a new database what or who defines the initial contents?

Third thing to remember: When you create a new database by using the syntax “create database [DB_NAME]” you get an exact copy of template1.

Really? What happens when I modify template1? Lets add one table and one extension:

[email protected]:/home/postgres/ [postgres] psql template1
psql (9.6rc1 dbi services build)
Type "help" for help.

([email protected][local]:5432) [template1] > create table my_test_tab ( a int );
CREATE TABLE
([email protected][local]:5432) [template1] > create extension hstore;
CREATE EXTENSION
Time: 123.722 ms
([email protected][local]:5432) [template1] > \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

If the above statement is true every new database created with the above syntax should contain the table and the extension, right?

([email protected][local]:5432) [postgres] > \c db_test
You are now connected to database "db_test" as user "postgres".
([email protected][local]:5432) [db_test] > \d
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | my_test_tab | table | postgres
(1 row)

([email protected][local]:5432) [db_test] > \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Whatever you put into template1 will be available in a new database if you use the following syntax: “create database [DB_NAME];” This can simplify your deployments a lot if you rely on pre-installed objects for e.g. monitoring or development.

Ok, I got it, but what is template0 for then? For this to understand we first take a look at pg_database, especially two columns: datallowconn and datistemplate:

([email protected][local]:5432) [db_test] > select datname,datallowconn,datistemplate from pg_database order by 3;
  datname  | datallowconn | datistemplate 
-----------+--------------+---------------
 postgres  | t            | f
 db_test   | t            | f
 template1 | t            | t
 template0 | f            | t
(4 rows)

When you take a look at “datallowcon” the only database that has set this to false is “template0”. Do you remember the beginning of this post when we tried to delete the “postgres” database? You can only delete a database when there are no connections. But: You can only create a database from another database if there are no connections to the source, too. Really? Why then can I create a new database when I am connected to template1 when template1 is the source for the new database?

([email protected][local]:5432) [template1] > create database db_test_2;
CREATE DATABASE

Confusing? This does not work anymore if there is another session connected to template1.

Lets try to create another new database but this time we we use db_test as the source. Yes, this is possible if you slightly adjust the syntax. But before we create the database we create a another connection to db_test:

([email protected][local]:5432) [template1] > \q
[email protected]:/home/postgres/ [PG960] psql db_test
psql (9.6rc1 dbi services build)
Type "help" for help.

In another session we try to create new database with db_test as the source:

[email protected]:/home/postgres/ [PG960] psql postgres
psql (9.6rc1 dbi services build)
Type "help" for help.

([email protected][local]:5432) [postgres] > create database db_test_3 template db_test;
ERROR:  source database "db_test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Fourth point to remember: For creating new databases you can use whatever database you like as the source when you specify then template explicitly.
Fifth point to remember: When you want to drop a database or when you want to create a database there must be no connections to the database you either want to drop or you want to create a new database from.

Coming back to the “datistemplate” and “datallowcon” settings: template0 is the only database that has “datallowcon” set to false, why? Because template0 is meant as the default unmodifiable database. You never should make any changes there. In a brand new PostgreSQL instance template0 and template1 are exactly the same. But why do I need both of them then? Assume you messed up template1 somehow (installed too many objects, for example). Using template one you still can recover from that easily:

[email protected]:/home/postgres/ [PG960] psql postgres
psql (9.6rc1 dbi services build)
Type "help" for help.
([email protected][local]:5432) [postgres] > update pg_database set datistemplate = false where datname = 'template1';
UPDATE 1
([email protected][local]:5432) [postgres] > drop database template1;
DROP DATABASE
([email protected][local]:5432) [postgres] > create database template1 template template0;
CREATE DATABASE
([email protected][local]:5432) [postgres] > update pg_database set datistemplate = true where datname = 'template1';
UPDATE 1

What happened here? I modified template1 to not being a template anymore because you can not drop a database flagged as a template. Then I dropped and re-created the template1 database by using template0 as the template. Now my template1 is an exact copy of template0 again and all the things I messed up are gone. Another use case for this is: Image you modified template1 to include the stuff you rely on but at some point in the future you need a new database which shall be without your modifications (e.g. restoring a dump). For this you always can use template0 as a template because template0 is always clean. And this is why connections are not allowed to template0.

Of course you can create your own template database(s) by setting the “datistemplate” to true:

([email protected][local]:5432) [postgres] > update pg_database set datistemplate = true where datname = 'db_test';
UPDATE 1
([email protected][local]:5432) [postgres] > drop database db_test;
ERROR:  cannot drop a template database

What about the overhead:

([email protected][local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'template0' ));
 pg_size_pretty 
----------------
 7233 kB
(1 row)

([email protected][local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'template1' ));
 pg_size_pretty 
----------------
 7233 kB
(1 row)

([email protected][local]:5432) [postgres] > select * from pg_size_pretty ( pg_database_size ( 'postgres' ));
 pg_size_pretty 
----------------
 7343 kB
(1 row)

Should not really be an issue on todays hardware. Hope this puts some light on these default databases.

22 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure