skip to content

In PostgreSQL, what are template0 and template1, and when should CREATE DATABASE use TEMPLATE template0 instead of the default?

level: middleimportance: should knowfreq 46%

answer

  1. CREATE DATABASE copies a database
  2. site-local additions propagate
  3. pristine and closed to connections
  4. restoring a plain dump
  5. nobody connected to the source

basics

~20 s

CREATE 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 s

PostgreSQL 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
sql
-- 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

for a junior

Recall that new databases are copies of template1 and that template0 is the untouched original kept for special cases.

for a middle

Explain why template0 is used for plain dump restores and new encodings, and why the source database must have no other sessions.

for a senior

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.

for a principal

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