What are PostgreSQL Templates?

Summary

PostgreSQL bases every new database creation on an existing template database within the cluster, defaulting to template1. While administrators can directly modify template1 with tables, data, extensions, or procedural languages, altering it risks breaking future database creation commands if mistakes happen. Setting an existing database as a custom template using the ALTER DATABASE command allows users with the CREATEDB privilege to instantiate customized databases without polluting system templates. Any database creation from a template requires zero active connections on that template at execution time, making pg_dump the preferred tool for replicating active production environments. The immutable template0 system database serves as a fallback to recreate corrupted templates, restore clean dumps, or initialize databases with alternative character encodings.

Takeaways

1. 1

   Setting is_template to TRUE on an existing database allows any role with the CREATEDB privilege to use it as a template, whereas unflagged databases restrict template usage to superusers or owners.

2. 2

   The CREATE DATABASE command fails immediately if any active connections exist on the target template database at the start of execution, making pg_dump preferable for live database replication.

3. 3

   The pristine template0 database must remain unmodified after cluster initialization to enable recovery of corrupted template1 databases and allow database creation with alternative encodings such as SQL_ASCII.