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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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.
1 2 | postgres=# UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template_db'; 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.
1 2 3 | postgres=# CREATE DATABASE new_db TEMPLATE my_template_db; CREATE DATABASE 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
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.
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# c new_db You are now connected to database "new_db" as user "postgres". new_db=# new_db=# select * from my_schema.my_table; id | description ----+------------- 1 | Test1 2 | Test2 3 | Test3 4 | Test4 5 | Test5 (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
About the Author
Sonia ValejaSonia is a professional with over a decade of IT experience on multiple projects from diverse geographies. During this tenure, she got an opportunity to work in various roles such as PL/SQL Developer, Migration Expert, DB Lead, DBA, Corporate Trainer, Data Analyst, PostgreSQL Developer, etc. In 2021, she enhanced her skill set by earning an MS in Data Science. Sonia is also a co-author of the book "PostgreSQL for Jobseekers," published by BPB Publications in June 2023. Additionally, she created a video course on pgBackRest that was launched on LinkedIn Learning in March 2024.
