Level up your knowledge with our PostgreSQL Tips

We get a lot of customers asking us numerous questions about PostgreSQL. So, we decided to launch a series called PostgreSQL Tips to level up your knowledge about the database and to give you tips on how to use it. In this article, we’ll cover the databases you get when you create a PostgreSQL deployment: template databases.

Template databases

When setting up PostgreSQL, whether it’s local or in the cloud, you get three standard databases outside the box: “template0,” “template1,” and “postgres.” If you use IBM Cloud Databases (ICD) for PostgreSQL, you’ll get an additional database called “ibmclouddb.”

While you might be familiar with the empty “postgres” database, what’s the use of the template databases? In this PostgreSQL Tips, we’ll take a look at these template databases and show you what they’re there for and how you might use them in in your PostgreSQL deployment.

Creating a database and “template1”

As we mentioned, when a PostgreSQL cluster is provisioned, you’ll be given three databases (or four if using IBM Cloud Databases). Two of these are called “template0” and “template1,” and they are referred to in the PostgreSQL documentation as template databases.

The name “template” is exactly what it the database is: it’s a template for new databases. You can view which databases in PostgreSQL are your templates with the following command—pay attention to datistemplate that specifies whether the database is a template or not:

SELECT datname, datallowconn, datistemplate FROM pg_database;

When you run CREATE DATABASE or createdb from the command line, you get a copy of everything in “template1.” Any functions, triggers, extensions, or anything else you’ve put in or installed in this database will be copied into subsequent databases. If you rely a lot on the same objects being carried over to new databases, then installing those in “template1” can simplify things.

Let’s look at the following example. If you connect to “template1” \c template1, you’ll notice by default that it’s empty. However, let’s create a table called “mytable” within “template1” and add some data to it:

CREATE TABLE mytable (id int, name text);
INSERT INTO mytable VALUES (1, 'Henry');

Now, let’s create another database called “exampledb” and connect to it:

CREATE DATABASE exampledb;
\c exampledb 

Once connected, type \d and you’ll see that “mytable” has been copied over. Using a SELECT statement will also show you that “Henry” is also in the table.

If you don’t want “mytable” to be created in subsequent databases, make sure to delete anything you don’t want to be copied.

One thing to pay attention to is if you try to create a database when another session is connected to “template1,” you’ll get an error. That’s because when creating a database from a template, you cannot have any connection from the source. For instance, if we’re connected to “template1” and try to create a new database we’ll get the following:

template1=> create database exampledb;
ERROR:  source database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.
template1=> 

That’s because “template1” is the source for all CREATE DATABASE commands. If we create a database from another template—let’s say “template0” or another database we want to use as a template with no user sessions—then we won’t have an issue.

What about “template0”?

“template0” is also a template database. It contains the same default database objects as “template1,” except you shouldn’t modify it because it’s meant to be a pristine database copy and ICD for PostgreSQL prevents you from doing so. “template0” is a fresh version of the database which doesn’t contain any of the modifications that you might have introduced into “template1,” and you can set encodings and modify settings for databases created with “template0” versus “template1.”

If you’ve modified “template1” and need a fresh database without the modifications, you can create a new database from “template0” using:

CREATE DATABASE newdb TEMPLATE template0;

That’s why “template0” doesn’t allow you to connect to the database like you can in “template1.” To keep “template0” from being spoiled, it’s locked down so that you can create pristine databases on the fly without potential source connection errors.

If you’re restoring a database from a .dump file, the PostgreSQL documentation also recommends that you restore using “template0” because you don’t want anything conflicting with a restore that you may have introduced in “template1”.

Dropping templates

First of all, you cannot drop databases that users are connected to in PostgreSQL. As for template databases, on local versions of PostgreSQL, you can drop them but we don’t recommend that. If you want to restore “template1” to its default, you just need to clean it up by uninstalling or dropping everything you’ve put in it. That’s because if you forget to recreate “template1” after dropping it, you will not be able to create new databases unless you specify a template.

With ICD for PostgreSQL, we’ve prevented users from dropping “template1” to avoid this scenario altogether. To restore “template1” we recommend that you clean it up, or run pg_dump on your databases to create a backup and then restore it into a new PostgreSQL deployment.

Creating new databases as templates

While it’s not common practice, you’re allowed to create new databases to serve as a source for templates in PostgreSQL. On ICD for PostgreSQL, you cannot change datistemplate from pg_database to true since it requires superuser access. However, you can create a new database and use those as templates for others.

For example, to create a new database using “exampledb” that includes the table “mytable,” we can specify that database as a template like we did when referencing “template0” above:

CREATE DATABASE newexample TEMPLATE exampledb;

Again, the caveat here is that in order to create a new database, you cannot have connections to it. Therefore, if you have a session on “exampledb” and want to use it as a template source, it will fail.

So, what if you want to create a new database for testing and want to use the production database as the template?

The short answer is not to use templates for this. That’s because you’d have to stop all the connections to your production database in order to run CREATE DATABASE. Instead, you should use pg_dump to backup your production database and restore it to the new database using the .dump file. Since you only want a single database, use pg_dump, not pg_dumpall, which will back up the entire PostgreSQL cluster.

Summing up

In this PostgreSQL Tip, we demystify PostgreSQL template databases and how and when you might use them. Template databases are really useful when you use the same database objects every time you create a new database. However, you have to be careful because you don’t want to bloat “template1” with objects and then have to do an extensive cleaning of the database because you have too many objects that aren’t needed. Therefore, we recommend taking a conservative approach to add objects inside “template1” to avoid bloating the database—add only what’s necessary.

Check out IBM Cloud Databases for PostgreSQL