CREATE DATABASE actually works by copying an existing
database. By default, it copies the standard system database named
template1. Thus that database is the "template"
from which new databases are made. If you add objects to
template1, these objects
will be copied into subsequently created user databases. This
behavior allows site-local modifications to the standard set of
objects in databases. For example, if you install the procedural
language PL/pgSQL in template1, it will
automatically be available in user databases without any extra action
being taken when those databases are made.
There is a second standard system database named template0.
This database contains the same data as the initial contents of
template1, that is, only the standard objects predefined by
your version of PostgreSQL.
template0 should never be changed
after initdb. By instructing CREATE DATABASE to
copy template0 instead of template1, you can
create a "virgin" user database that contains none of the
site-local additions in template1. This is particularly
handy when restoring a pg_dump dump: the dump script should
be restored in a virgin database to ensure that one recreates the
correct contents of the dumped database, without any conflicts with
additions that may now be present in template1.
To create a database by copying template0, use
CREATE DATABASE dbname TEMPLATE template0;
from the SQL environment, or
createdb -T template0 dbname
from the shell.
It is possible to create additional template databases, and indeed
one might copy any database in an installation by specifying its name
as the template for CREATE DATABASE. It is important to
understand, however, that this is not (yet) intended as
a general-purpose "COPY DATABASE" facility. In particular, it is
essential that the source database be idle (no data-altering transactions
in progress)
for the duration of the copying operation. CREATE DATABASE
will check
that no backend processes (other than itself) are connected to
the source database at the start of the operation, but this does not
guarantee that changes cannot be made while the copy proceeds, which
would result in an inconsistent copied database. Therefore,
we recommend that databases used as templates be treated as read-only.
Two useful flags exist in pg_database for each
database: the columns datistemplate and
datallowconn. datistemplate
may be set to indicate that a database is intended as a template for
CREATE DATABASE. If this flag is set, the database may be
cloned by
any user with CREATEDB privileges; if it is not set, only superusers
and the owner of the database may clone it.
If datallowconn is false, then no new connections
to that database will be allowed (but existing sessions are not killed
simply by setting the flag false). The template0
database is normally marked datallowconn =
false to prevent modification of it.
Both template0 and template1
should always be marked with datistemplate =
true.
After preparing a template database, or making any changes to one,
it is a good idea to perform
VACUUM FREEZE or VACUUM FULL FREEZE in that
database. If this is done when there are no other open transactions
in the same database, then it is guaranteed that all tuples in the
database are "frozen" and will not be subject to transaction
ID wraparound problems. This is particularly important for a database
that will have datallowconn set to false, since it
will be impossible to do routine maintenance VACUUMs on
such a database.
See Section 8.2.3 for more information.
Note: template1 and template0 do not have any special
status beyond the fact that the name template1 is the default
source database name for CREATE DATABASE and the default
database-to-connect-to for various programs such as createdb.
For example, one could drop template1 and recreate it from
template0 without any ill effects. This course of action
might be advisable if one has carelessly added a bunch of junk in
template1.
