Exploring the is_template Flag: Simplifying PostgreSQL Template Databases

June 25, 2024

Author

Sonia Valeja

Insight for DBAs

PostgreSQL

Share this Post:

PostgreSQL is a powerful and versatile relational database management system that provides many features for managing and manipulating databases. One such feature is the is_template flag, which can be used to create and manage template databases. In this blog post, we will explore what the is_template flag is, why it is useful, and how to use it effectively in PostgreSQL.

What is the is_template flag?

In PostgreSQL, the is_template flag is a boolean attribute that determines whether a database can be used as a template for creating new databases. When a database has the is_template flag set to true, it means that this database can be used as a template for creating other databases. By default, PostgreSQL comes with two template databases: template0 and template1.

Default template databases

template0: This is a clean template database that remains unchanged since the initial installation of PostgreSQL. It is often used to create new databases that need to be in their default state without customizations.

template1: This template database is initially identical to template0, but it can be modified by the database administrator. Any changes made to template1 will be inherited by new databases created from it.

Why is the is_template flag useful?

The is_template flag is particularly useful for:

Consistency: Ensuring new databases start with a consistent schema, extensions, and initial data.

Convenience: Quickly creating new databases that inherit the structure and data of an existing database.

Customization: Allowing database administrators to customize the default template (e.g., template1) so that new databases automatically include specific settings, schemas, or extensions.

How to use the is_template flag

Let’s dive into some practical examples to see how the is_template flag can be used in PostgreSQL.

Creating a custom template database

Create a new database: First, create a new database that can be used as a template. For this example, let’s call it my_template_db.

CREATE DATABASE my_template_db;

1	CREATE DATABASE my_template_db;

Customize the template database: Connect to my_template_db and customize it as needed. This could include creating schemas and tables and adding extensions.

postgres=# c my_template_db<br>You are now connected to database "my_template_db" as user "postgres".<br>my_template_db=#<br>my_template_db=# CREATE SCHEMA my_schema;<br>CREATE SCHEMA<br>my_template_db=#<br>my_template_db=# CREATE TABLE my_schema.my_table( id SERIAL, description text );<br>CREATE TABLE<br>my_template_db=#<br>my_template_db=# insert into my_schema.my_table (description) select 'Test' || generate_series(1,5);<br>INSERT 0 5<br>my_template_db=#<br>my_template_db=# select * from my_schema.my_table;<br> id | description<br>----+-------------<br>  1 | Test1<br>  2 | Test2<br>  3 | Test3<br>  4 | Test4<br>  5 | Test5<br>(5 rows)<br>my_template_db=#<br>

postgres=# c my_template_db You are now connected to database "my_template_db" as user "postgres". my_template_db=# my_template_db=# CREATE SCHEMA my_schema; CREATE SCHEMA my_template_db=# my_template_db=# CREATE TABLE my_schema.my_table( id SERIAL, description text ); CREATE TABLE my_template_db=# my_template_db=# insert into my_schema.my_table (description) select 'Test' || generate_series(1,5); INSERT 0 5 my_template_db=# my_template_db=# select * from my_schema.my_table; id | description ----+------------- 1 | Test1 2 | Test2 3 | Test3 4 | Test4 5 | Test5 (5 rows) my_template_db=#

— Add other customizations as needed

Set the is_template flag: After customizing the database, set the is_template flag to true to make it available as a template.

postgres=# UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template_db';<br>UPDATE 1

1	postgres=# UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template_db';<br>UPDATE 1

Creating a new database from a custom template

Once you have a custom template database, you can create new databases from it easily:

Create a New Database Using the Template: Use the TEMPLATE option in the CREATE DATABASE command to specify the template database.

postgres=# CREATE DATABASE new_db TEMPLATE my_template_db;<br>CREATE DATABASE<br>postgres=#

1	postgres=# CREATE DATABASE new_db TEMPLATE my_template_db;<br>CREATE DATABASE<br>postgres=#

Note: Please make sure to terminate the active sessions of my_template_db. The below query is one of the ways to terminate active sessions

select pg_terminate_backend(pid) from pg_stat_activity where datname = 'my_template_db';

1	select pg_terminate_backend(pid) from pg_stat_activity where datname = 'my_template_db';

Verify the new database: Connect to the new database and verify that it has inherited the structure and data from the template.

postgres=# c new_db<br>You are now connected to database "new_db" as user "postgres".<br>new_db=#<br>new_db=# select * from my_schema.my_table;<br> id | description<br>----+-------------<br>  1 | Test1<br>  2 | Test2<br>  3 | Test3<br>  4 | Test4<br>  5 | Test5<br>(5 rows)

1	postgres=# c new_db<br>You are now connected to database "new_db" as user "postgres".<br>new_db=#<br>new_db=# select * from my_schema.my_table;<br> id \| description<br>----+-------------<br> 1 \| Test1<br> 2 \| Test2<br> 3 \| Test3<br> 4 \| Test4<br> 5 \| Test5<br>(5 rows)

Conclusion

The is_template flag in PostgreSQL is a powerful feature that allows to create and manage template databases efficiently. By using template databases, one can ensure consistency, save time, and customize the initialization of new databases to meet your specific requirements. Whether anyone is setting up a new environment or managing multiple databases, understanding and leveraging the is_template flag can greatly enhance your PostgreSQL database management capabilities.

Happy templating!

Unlock the full potential of your PostgreSQL database! This eBook is the ultimate guide for database administrators, developers, and IT professionals tasked with upgrading PostgreSQL environments. Get your copy now and take the first step towards mastering PostgreSQL upgrades.

Essential Strategies for PostgreSQL Upgrade Success