TIL Postgres: Template Databases

Whenever you create a new database in Postgres, you are actually basing it off an already present database in your cluster. This database, template1, and another, called template0, are standard system databases that exist in every newly created database cluster. Don't believe me? Why not quickly spin up a database and see it for yourself with this query:

SELECT * FROM pg_database;

In this post, we'll explore these template databases and see how we can make full use of their potential. We'll even look into creating a template database of our own.

template1

By default, running:

CREATE DATABASE new_db_name;

simply copies everything from the database template1. We can modify this template database in any way: add a table, insert some data, create new extensions, or install procedural languages. Any of these actions would be propagated to subsequently created databases.

This, however, is not advisable. Removing any one of these modifications would need you to manually uninstall or drop these changes from template1. You do have the option to drop and recreate the entire template1 database altogether. This unfortunately comes at the risk of committing a mistake along the way, effectively breaking CREATE DATABASE. It would be better to leave template1 alone and create a template database of your own.

Custom Template Databases

To set an existing database as a template database:

ALTER DATABASE template_db_name WITH is_template TRUE;

Doing this allows any user or role with the CREATEDB privilege to utilize it as a template. If not, only superusers or owners of the database would be allowed to do so.

To create a new database with this template:

CREATE DATABASE new_db_name TEMPLATE template_db_name;

Advantages

• With this, you can now have customized templates without the need to worry about polluting template1.
• You can safely drop the entire custom template database without the risk of breaking CREATE DATABASE.
• If you wish, you can create multiple template databases for various use cases.

Limitations

• To properly create a database from a custom template database, there should be no other connections present. CREATE DATABASE immediately fails if any connections exist at the start of the query.
• As such, if you are looking to replicate a database while maintaining your connections (eg. a production database), it would be more ideal to use the Postgres utility pg_dump.

template0

template0 contains the same data as template1. We could think of this template database as a fallback if anything irreversible happens to template1. As such, this template database should never be modified in any way as soon as the database cluster has been initialized. To create a database with template0 as the template database:

CREATE DATABASE new_db_name TEMPLATE template0;

Applications

• If anything goes wrong with template1, It can be dropped and recreated with template0 as the template database.
• We can also create a clean database that does not contain any modifications present in template1. This would be useful when restoring from pg_dump. Any conflicts brought about by modifications not present in the dump are eliminated.
• template0 can be used to specify new encodings. As pointed out in this article, creating a new database with template1 and new encodings would result in an error.

-- Will succeed
CREATE DATABASE new_db_name TEMPLATE template0 ENCODING 'SQL_ASCII';

-- Will return an error
CREATE DATABASE new_db_name ENCODING 'SQL_ASCII';

Conclusion

To quickly sum things up, we found out that new databases are, by default, created from a template database called template1. template1 can be modified in any way we please and the changes would be present in any database created afterward. We can also create custom template databases and base new databases from them instead. If things go awry, template0 is always there to help.

---

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.