PostgreSQL Tips: Template Databases

In response to the growing inquiries from customers about PostgreSQL, we are excited to introduce a series titled PostgreSQL Tips, designed to enhance your understanding of this powerful database system. This installment focuses on the essential template databases that are created when you set up a PostgreSQL deployment.

Template Databases

Upon provisioning a PostgreSQL cluster, whether locally or in the cloud, you will encounter three standard databases: “template0,” “template1,” and “postgres.” If you are utilizing IBM Cloud® Databases (ICD) for PostgreSQL, an additional database named “ibmclouddb” is included.

While the “postgres” database may be familiar to many, the roles of the template databases are often less understood. This section will explore their functions and how they can be leveraged in your PostgreSQL environment.

Creating a Database and “template1”

As previously mentioned, when a PostgreSQL cluster is established, it includes three databases (or four with IBM Cloud Databases). Among these are “template0” and “template1,” which are classified as template databases in the PostgreSQL documentation.

The term “template” aptly describes these databases, as they serve as blueprints for creating new databases. To identify which databases in PostgreSQL are designated as templates, you can execute the following command, paying close attention to the datistemplate column:

SELECT datname, datallowconn, datistemplate FROM pg_database;

When you issue a CREATE DATABASE or createdb command, a copy of everything from “template1” is created. This includes any functions, triggers, or extensions you have installed. If you frequently rely on certain objects being replicated across new databases, populating “template1” can streamline the process.

For example, if you connect to “template1” using c template1, you will find it empty by default. Let’s create a table named “mytable” and insert some data:

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

Next, we can create a new database called “exampledb” and connect to it:

CREATE DATABASE exampledb; c exampledb

Once connected, typing d will reveal that “mytable” has been successfully copied over, and a SELECT statement will confirm that “Henry” is present in the table.

If you prefer not to have “mytable” appear in subsequent databases, simply remove any unwanted objects before creating new databases. It is also important to note that if another session is connected to “template1,” attempting to create a new database will result in an error:

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

This restriction exists because “template1” serves as the source for all CREATE DATABASE commands. However, if you create a database from “template0” or another template without active user sessions, you will not encounter this issue.

What About “template0”?

“template0” is another template database that contains the same default objects as “template1,” but it is crucial not to modify it. This database is intended to remain pristine, and with ICD for PostgreSQL, modifications are prevented. “template0” provides a clean version of the database, allowing for the setting of encodings and configurations for databases created from it, distinct from those created with “template1.”

If you need a fresh database without the modifications made to “template1,” you can create a new database from “template0” using the following command:

CREATE DATABASE newdb TEMPLATE template0;

Due to its purpose, “template0” does not allow user connections, ensuring its integrity as a source for creating new databases without the risk of source connection errors. Additionally, when restoring a database from a .dump file, it is advisable to use “template0” to avoid conflicts with any modifications in “template1.”

Dropping Templates

It is important to note that you cannot drop databases that have active user connections in PostgreSQL. While it is technically possible to drop template databases in local versions, it is not recommended. If you wish to restore “template1” to its default state, simply clean it up by uninstalling or removing any added objects. Failing to recreate “template1” after dropping it will prevent you from creating new databases unless you specify an alternative template.

In the case of ICD for PostgreSQL, users are restricted from dropping “template1” to avoid such complications. To restore “template1,” it is best to clean it up or utilize pg_dump to back up your databases and restore them into a new PostgreSQL deployment.

Creating New Databases as Templates

Although it is not a common practice, you can create new databases to serve as templates in PostgreSQL. In ICD for PostgreSQL, changing the datistemplate flag to true requires superuser access, but you can still create new databases to use as templates for others.

For instance, to create a new database using “exampledb” that includes the table “mytable,” you can specify it as a template:

CREATE DATABASE newexample TEMPLATE exampledb;

As always, ensure that there are no active connections to the database you wish to use as a template, or the command will fail. If you are considering creating a new database for testing purposes and want to use the production database as a template, it is advisable to avoid this approach. Instead, utilize pg_dump to back up your production database and restore it to the new database using the .dump file. For single database backups, opt for pg_dump rather than pg_dumpall, which backs up the entire PostgreSQL cluster.

Through this exploration of PostgreSQL template databases, we hope to illuminate their functionality and best practices for their use. Template databases can significantly enhance efficiency when creating new databases, provided they are managed thoughtfully to avoid unnecessary complexity.

Tech Optimizer