In PostgreSQL, what are template0 and template1, and when should CREATE DATABASE use TEMPLATE template0 instead of the default?
answer
- CREATE DATABASE copies a database
- site-local additions propagate
- pristine and closed to connections
- restoring a plain dump
- nobody connected to the source
basics
~20 sCREATE DATABASE clones template1 by default, so objects added there reach every new database. template0 is a pristine, connection-disabled original; clone it to restore a plain pg_dump script or to choose an encoding or locale unlike template1's.
solid answer
~40 sPostgreSQL creates a database by **copying an existing one**, `template1` by default. Anything you add to `template1`, such as an extension or a standard schema, is copied into every database created afterwards, though not into existing ones. `template0` holds the original contents and should never be changed; it is normally marked `datallowconn = false`. Use `TEMPLATE template0` when restoring a plain `pg_dump` script, so site-local additions cannot conflict, and when the new database needs an encoding or locale different from `template1`'s. Any database can be a template, but **no other session may be connected to it** while it is copied, otherwise `CREATE DATABASE` fails with `source database ... is being accessed by other users` (SQLSTATE `55006`).
code
sql · 10 lines-- build a lab template once, as a superuser
CREATE DATABASE lab_template;
-- connect to lab_template, create the shared ref schema and extensions, then:
ALTER DATABASE lab_template IS_TEMPLATE true ALLOW_CONNECTIONS false;
-- every new lab is a copy of it
CREATE DATABASE neuro_lab TEMPLATE lab_template OWNER neuro_admin;
-- restoring a plain pg_dump script needs a pristine target
CREATE DATABASE climate_restore TEMPLATE template0;go deeper
Recall that new databases are copies of template1 and that template0 is the untouched original kept for special cases.
Explain why template0 is used for plain dump restores and new encodings, and why the source database must have no other sessions.
Show you would keep template1 clean, run a custom template closed to connections, and diagnose the being-accessed error from pg_stat_activity and prepared transactions.
Treat templates as a provisioning mechanism: standard objects per tenant or team, how they are versioned and rolled out, and what stays per-database configuration instead.
## CREATE DATABASE copies a database PostgreSQL never builds a new database from scratch. `CREATE DATABASE climate_lab` makes a copy of a **template database**, by default `template1`, including its schemas, tables, rows, functions and installed extensions. The cluster starts with two templates, made when it is initialised: | | `template1` | `template0` | |---|---|---| | Role | the default source for `CREATE DATABASE` | a pristine copy of the original contents | | May you change it? | yes, for site-wide defaults | no, never after cluster creation | | `datallowconn` | `true` | normally `false` | | `datistemplate` | `true` | `true` | | Typical use | extensions and objects every new database should have | restoring dumps; a different encoding or locale | Neither name is magic. `template1` is only the default value of the `TEMPLATE` option, and you could drop it and recreate it from `template0` after setting its `datistemplate` flag to false. The `postgres` database is also an ordinary copy of `template1`. ## template1: site-local defaults Because new databases are copies, `template1` is where site-wide defaults go. Install an extension in `template1` and every database created afterwards has it. Two consequences matter: - Changes reach **future** databases only. Databases that already exist are untouched. - Junk added by mistake to `template1` spreads quietly into every new database, which is why many sites leave it alone and build a **custom template** instead. ## template0: when to use it `template0` contains only the standard objects of your PostgreSQL version. Clone it with `CREATE DATABASE name TEMPLATE template0` (or `createdb -T template0 name`) in two situations: 1. **Restoring a plain-format `pg_dump` script.** The script recreates every object of the dumped database; if the target already holds site-local objects copied from `template1`, the restore hits duplicates and conflicts. Restoring into a clone of `template0` recreates the original contents exactly. 2. **Choosing a different encoding or locale.** A clone of `template1` must keep `template1`'s encoding and locale settings, because its data and indexes may depend on them. `template0` is known to contain nothing that would be affected, so new settings are allowed there. The encoding and locale options themselves belong to the collations leaf. ## The lockout rule Copying a database while someone writes to it could produce an inconsistent copy, so PostgreSQL refuses: - `CREATE DATABASE` fails at once if **any other session** is connected to the source database, with `source database "template1" is being accessed by other users` (SQLSTATE `55006`, `object_in_use`). The detail line counts the other sessions, and prepared transactions on the source count too. - The session issuing the command may itself be connected to the source; only *other* backends block it. - While the copy runs, new connections to the source database are locked out. The classic cause is a forgotten psql session, or a monitoring tool, left connected to `template1`. ## A custom template for the research cluster In a university cluster where every lab gets its own database, keep `template1` clean and maintain `lab_template` instead: the lab-standard extensions, a `ref` schema with shared lookup tables, and standard functions. Mark it with `IS_TEMPLATE true` so any role with CREATEDB can clone it, and with `ALLOW_CONNECTIONS false` so no stray session blocks the next clone. When you need to update it, allow connections, make the change, and close them again. ## Rebuilding a damaged template1 If objects were added to `template1` by mistake, rebuild it from `template0` while connected to another database such as `postgres`: 1. `ALTER DATABASE template1 IS_TEMPLATE false;` because a database still marked as a template cannot be dropped. 2. `DROP DATABASE template1;` which fails if you, or anyone else, is connected to it. 3. `CREATE DATABASE template1 TEMPLATE template0 IS_TEMPLATE true;` to get a clean default template back. Databases already created from the damaged `template1` keep whatever they copied; only future databases benefit. ## What is not copied - **Database-level GRANTs** (such as CONNECT and CREATE on the database). The new database starts with default database-level permissions. - **Database-level settings** made with `ALTER DATABASE ... SET`. Set them again on the new database. - The **owner**: the creating role, or the one named in `OWNER`, owns the new database. Objects *inside* the database, including their privileges, are copied as they are.
- CREATE DATABASE fails with 'source database "lab_template" is being accessed by other users'. How do you find and fix the cause?Look in `pg_stat_activity` for sessions whose `datname` is the template, and in `pg_prepared_xacts` for prepared transactions in it. Close or terminate them, then retry. To stop it recurring, set `ALLOW_CONNECTIONS false` on the template: existing sessions are not terminated by that flag, but no new ones can open.
- Who may clone a database that is not marked as a template?Only a superuser or the database's owner. Setting `IS_TEMPLATE true` (the `datistemplate` flag) lets any role with the CREATEDB privilege clone it. Both standard templates are marked this way.
- template0 is the place to add site-wide default objects
- Changing template1 updates databases that already exist
- CREATE DATABASE also copies the template's GRANTs and ALTER DATABASE settings
- template1 is special and can never be dropped
- A database can be cloned while other sessions are using it