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 DATABASEimmediately 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 withtemplate0as 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. template0can be used to specify new encodings. As pointed out in this article, creating a new database withtemplate1and 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.