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

Categories

More from Cloud

IBM Cloud VMware as a Service introduces multitenant as a new, cost-efficient consumption model

4 min read - Businesses often struggle with ongoing operational needs like monitoring, patching and maintenance of their VMware infrastructure or the added concerns over capacity management. At the same time, cost efficiency and control are very important. Not all workloads have identical needs and different business applications have variable requirements. For example, production applications and regulated workloads may require strong isolation, but development/testing, training environments, disaster recovery sites or other applications may have lower availability requirements or they can be ephemeral in nature,…

IBM accelerates enterprise AI for clients with new capabilities on IBM Z

5 min read - Today, we are excited to unveil a new suite of AI offerings for IBM Z that are designed to help clients improve business outcomes by speeding the implementation of enterprise AI on IBM Z across a wide variety of use cases and industries. We are bringing artificial intelligence (AI) to emerging use cases that our clients (like Swiss insurance provider La Mobilière) have begun exploring, such as enhancing the accuracy of insurance policy recommendations, increasing the accuracy and timeliness of…

IBM NS1 Connect: How IBM is delivering network connectivity with premium DNS offerings

4 min read - For most enterprises, how their users access applications and data is an essential part of doing business, and how they service those application and data responses has a direct correlation to revenue generation.    According to We Are Social’s Digital 2023 Global Overview Report, there are 5.19 billion people around the world using the internet in 2023. There’s an imperative need for businesses to trust their networks to deliver meaningful content to address customer needs.  So how responsive is the…

IBM Cloud Databases for MongoDB (Enterprise Edition): Changes to backup functionality

< 1 min read - We are announcing that IBM Cloud Databases for MongoDB (Enterprise Edition) will no longer support the creation of On Demand backups beginning on March 1, 2024. On Demand backups are being replaced by the recently deployed Point in Time Recovery (PITR) capabilities in the Enterprise Edition of our popular fully managed MongoDB service. With PITR, you can restore a copy of your database to any point in the past seven days. This gives you granular access to the past state…