|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.3 Template Databases
CREATE DATABASE actually works by copying an existing
database. By default, it copies the standard system database named
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
database contains the same data as the initial contents of
template1, that is, only the standard objects
predefined by your version of
should never be changed after
initdb. By instructing
CREATE DATABASE to copy
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
To create a database by copying
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 may copy any database in a cluster 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.
The principal limitation is that no other sessions can be connected to
the source database while it is being copied.
DATABASE will fail if any other connection exists when it starts;
otherwise, new connections to the source database are locked out
CREATE DATABASE completes.
Two useful flags exist in
database: the columns
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
any user with
CREATEDB privileges; if it is not set, only superusers
and the owner of the database may clone it.
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
database is normally marked
datallowconn = false to prevent modification of it.
should always be marked with
datistemplate = true.
template0do not have any special status beyond the fact that the name
template1is the default source database name for
CREATE DATABASE. For example, one could drop
template1and recreate it from
template0without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in
template1. (To delete
template1, it must have
datistemplate = false.)
postgresdatabase is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of
template1and may be dropped and recreated if required.
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|