PostgreSQL
Exploring the Default Postgres Template Databases
I was recently poking around the psql terminal, and noticed something that I had never had to deal with before: the Postgres template databases. I decided to seize the moment and launched myself into an exploration of what the template databases are meant to do, and why the heck there are two of them.
My first plan of attack was to just list them and see if I could find a difference right away, but everything looked exactly the same.
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
Since this wasn't helpful enough, I decided to pull up the database information with pg_database.
postgres=# select * from pg_database where datname like 'template%';
-[ RECORD 1 ]-+------------------------------------
datname | template0
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | t
datallowconn | f
datconnlimit | -1
datlastsysoid | 13266
datfrozenxid | 548
datminmxid | 1
dattablespace | 1663
datacl | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 2 ]-+------------------------------------
datname | template1
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | t
datallowconn | t
datconnlimit | -1
datlastsysoid | 13266
datfrozenxid | 548
datminmxid | 1
dattablespace | 1663
datacl | {=c/postgres,postgres=CTc/postgres}
postgres=# select datname, datallowconn from pg_database where datname like 'template%';
datname | datallowconn
----------+----------------
template0 | f
template1 | t
Aha! Found a difference: template1 allows database connections, but template0 does not. Being the person that I am, I couldn't help myself; I tried to connect.
postgres=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
Ok fine, so now I knew that I had two identical databases, one allowing connections, and one with connections disabled. Unsure of where to look next, I decided to take the long-standing advice of a colleague and read the dang manual. I discovered that, by default, when I create a new database (create database example;), Postgres is actually making a copy of template1. Since template1 allows connections, this meant that I could go into template1 and make a bunch of changes, and expect those changes to appear on any new databases I created. Once again, I had to test this out. I connected to template1, and then added a table with one row, and an extension.
postgres=# \c template1
You are now connected to database "template1" as user "marylee".
template1=# create table test (data varchar);
CREATE TABLE
template1=# insert into test values ('Some data');
INSERT 0 1
template1=# create extension dblink;
CREATE EXTENSION
Then I created a new database and checked to see that my changes were there.
template1=# create database example;
CREATE DATABASE
template1=# \c example
You are now connected to database "example" as user "marylee".
example=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | test | table | marylee
example=# select * from test;
data
-----------
Some data
(1 row)
example=# \dx dblink
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
Unsurprisingly, my new database had the table, row, and extension that I added to the template. So now I understood why I had template1, but what was the deal with template0? Why did I need two of these things? Back to the manual I went! Turns out, template0 can be useful to us in a couple ways. If we want to create a new database that bypasses any changes we made to template1, we can change our template specification during the create database process to use template0.
postgres=# create database example2 template template0;
CREATE DATABASE
postgres=# \c example2
You are now connected to database "example2" as user "marylee".
example2=# \d
Did not find any relations.
example2=# \dx dblink
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
(0 rows)
In this case my new database is empty, with no tables and no extra extensions. That's cool, but only really useful if we've made changes to template1. So, what other reasons are there for having a second template?
As it turns out, we can't edit the locale or encoding of a database copied from template1. Postgres blocks this behavior because template1 is editable and therefore may contain encoding or locale specific information. In other words, Postgres is trying to save us from ourselves. To test this out, I tried to create a new database using ASCII encoding instead of UTF8.
postgres=# create database ascii_db encoding 'SQL_ASCII';
ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.
As expected, I got an error from Postgres, with a helpful hint telling me to either use the same encoding as my template database, or to use template0. I tried again, this time specifying my template as template0.
postgres=# create database ascii_db template template0 encoding 'SQL_ASCII';
CREATE DATABASE
postgres=# \l ascii_db
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------+----------+----------+-------------+-------------+-----------------------
ascii_db | marylee | SQL_ASCII | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
Nice, so that's how we can change the encoding when creating a new database.
One last thing template0 is good for is allowing us to reset template1 to its original state. To do this, I first had to delete template1.
postgres=# drop database template1;
ERROR: cannot drop a template database
Whoops, looks like I can't drop a template database in Postgres. Meaning first, I had to first tell Postgres that template1 was no longer a template database. Then, I could delete it and recreate it again using template0.
postgres=# update pg_database set datistemplate = false where datname = 'template1';
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=# create database template1 owner postgres template template0 is_template true;
CREATE DATABASE
Then, I verified that I was back where I started.
postgres=# \c template1
You are now connected to database "template1" as user "marylee".
template1=# \d
Did not find any relations.
template1=# \dx dblink
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
(0 rows)
The dang manual: https://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html
Photo credit: rawpixel
