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');
Scroll to view full table

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

\c exampledb
Scroll to view full table

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.
Scroll to view full table

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:

Scroll to view full table

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;
Scroll to view full table

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

More from Cloud

Connected products at the edge

6 min read - There are many overlapping business usage scenarios involving both the disciplines of the Internet of Things (IoT) and edge computing. But there is one very practical and promising use case that has been commonly deployed without many people thinking about it: connected products. This use case involves devices and equipment embedded with sensors, software and connectivity that exchange data with other products, operators or environments in real-time. In this blog post, we will look at the frequently overlooked phenomenon of…

6 min read

SRG Technology drives global software services with IBM Cloud VPC under the hood

4 min read - Headquartered in Ft. Lauderdale, Florida, SRG Technology LLC. (SRGT) is a software development company supporting the education, healthcare and travel industries. Their team creates data systems that deliver the right data in real time to customers around the globe. Whether those customers are medical offices and hospitals, schools or school districts, government agencies, or individual small businesses, SRGT addresses a wide spectrum of software services and technology needs with round-the-clock innovative thinking and fresh approaches to modern data problems. The…

4 min read

IBM Tech Now: May 30, 2023

< 1 min read - ​Welcome IBM Tech Now, our video web series featuring the latest and greatest news and announcements in the world of technology. Make sure you subscribe to our YouTube channel to be notified every time a new IBM Tech Now video is published. IBM Tech Now: Episode 77 This episode, we're covering the following topics: IBM Watson Code Assistant IBM Hybrid Cloud Mesh IBM Event Automation Stay plugged in You can check out the IBM Blog Announcements for a full rundown…

< 1 min read

Strengthening cybersecurity in life sciences with IBM and AWS

7 min read - Cloud is transforming the way life sciences organizations are doing business. Cloud computing offers the potential to redefine and personalize customer relationships, transform and optimize operations, improve governance and transparency, and expand business agility and capability. Leading life science companies are leveraging cloud for innovation around operational, revenue and business models. According to a report on mapping the cloud maturity curve from the EIU, 48% of industry executives said cloud has improved data access, analysis and utilization, 45% say cloud…

7 min read