<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>Template Databases</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.4.1 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Managing Databases"
HREF="managing-databases.html"><LINK
REL="PREVIOUS"
TITLE="Creating a Database"
HREF="manage-ag-createdb.html"><LINK
REL="NEXT"
TITLE="Database Configuration"
HREF="manage-ag-config.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2003-12-22T03:48:47"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 7.4.1 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="manage-ag-createdb.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="managing-databases.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 18. Managing Databases</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="managing-databases.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="manage-ag-config.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="MANAGE-AG-TEMPLATEDBS"
>18.3. Template Databases</A
></H1
><P
> <TT
CLASS="COMMAND"
>CREATE DATABASE</TT
> actually works by copying an existing
database. By default, it copies the standard system database named
<TT
CLASS="LITERAL"
>template1</TT
>.<A
NAME="AEN17424"
></A
> Thus that
database is the <SPAN
CLASS="QUOTE"
>"template"</SPAN
> from which new databases are
made. If you add objects to <TT
CLASS="LITERAL"
>template1</TT
>, 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 <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> in <TT
CLASS="LITERAL"
>template1</TT
>, it will
automatically be available in user databases without any extra
action being taken when those databases are made.
</P
><P
> There is a second standard system database named
<TT
CLASS="LITERAL"
>template0</TT
>.<A
NAME="AEN17432"
></A
> This
database contains the same data as the initial contents of
<TT
CLASS="LITERAL"
>template1</TT
>, that is, only the standard objects
predefined by your version of
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>. <TT
CLASS="LITERAL"
>template0</TT
>
should never be changed after <TT
CLASS="COMMAND"
>initdb</TT
>. By instructing
<TT
CLASS="COMMAND"
>CREATE DATABASE</TT
> to copy <TT
CLASS="LITERAL"
>template0</TT
> instead
of <TT
CLASS="LITERAL"
>template1</TT
>, you can create a <SPAN
CLASS="QUOTE"
>"virgin"</SPAN
> user
database that contains none of the site-local additions in
<TT
CLASS="LITERAL"
>template1</TT
>. This is particularly handy when restoring a
<TT
CLASS="LITERAL"
>pg_dump</TT
> 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 <TT
CLASS="LITERAL"
>template1</TT
>.
</P
><P
> To create a database by copying <TT
CLASS="LITERAL"
>template0</TT
>, use
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE DATABASE <VAR
CLASS="REPLACEABLE"
>dbname</VAR
> TEMPLATE template0;</PRE
><P>
from the SQL environment, or
</P><PRE
CLASS="PROGRAMLISTING"
>createdb -T template0 <VAR
CLASS="REPLACEABLE"
>dbname</VAR
></PRE
><P>
from the shell.
</P
><P
> It is possible to create additional template databases, and indeed
one might copy any database in a cluster by specifying its name
as the template for <TT
CLASS="COMMAND"
>CREATE DATABASE</TT
>. It is important to
understand, however, that this is not (yet) intended as
a general-purpose <SPAN
CLASS="QUOTE"
>"<TT
CLASS="COMMAND"
>COPY DATABASE</TT
>"</SPAN
> 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. <TT
CLASS="COMMAND"
>CREATE DATABASE</TT
>
will check
that no session (other than itself) is 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.
</P
><P
> Two useful flags exist in <TT
CLASS="LITERAL"
>pg_database</TT
><A
NAME="AEN17458"
></A
> for each
database: the columns <TT
CLASS="LITERAL"
>datistemplate</TT
> and
<TT
CLASS="LITERAL"
>datallowconn</TT
>. <TT
CLASS="LITERAL"
>datistemplate</TT
>
may be set to indicate that a database is intended as a template for
<TT
CLASS="COMMAND"
>CREATE DATABASE</TT
>. If this flag is set, the database may be
cloned by
any user with <TT
CLASS="LITERAL"
>CREATEDB</TT
> privileges; if it is not set, only superusers
and the owner of the database may clone it.
If <TT
CLASS="LITERAL"
>datallowconn</TT
> 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 <TT
CLASS="LITERAL"
>template0</TT
>
database is normally marked <TT
CLASS="LITERAL"
>datallowconn = false</TT
> to prevent modification of it.
Both <TT
CLASS="LITERAL"
>template0</TT
> and <TT
CLASS="LITERAL"
>template1</TT
>
should always be marked with <TT
CLASS="LITERAL"
>datistemplate = true</TT
>.
</P
><P
> After preparing a template database, or making any changes to one,
it is a good idea to perform
<TT
CLASS="COMMAND"
>VACUUM FREEZE</TT
> or <TT
CLASS="COMMAND"
>VACUUM FULL FREEZE</TT
> in that
database. If this is done when there are no other open transactions
in the same database, then it is guaranteed that all rows in the
database are <SPAN
CLASS="QUOTE"
>"frozen"</SPAN
> and will not be subject to transaction
ID wraparound problems. This is particularly important for a database
that will have <TT
CLASS="LITERAL"
>datallowconn</TT
> set to false, since it
will be impossible to do routine maintenance <TT
CLASS="COMMAND"
>VACUUM</TT
> in
such a database.
See <A
HREF="maintenance.html#VACUUM-FOR-WRAPAROUND"
>Section 21.1.3</A
> for more information.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> <TT
CLASS="LITERAL"
>template1</TT
> and <TT
CLASS="LITERAL"
>template0</TT
> do not have any special
status beyond the fact that the name <TT
CLASS="LITERAL"
>template1</TT
> is the default
source database name for <TT
CLASS="COMMAND"
>CREATE DATABASE</TT
> and the default
database-to-connect-to for various programs such as <TT
CLASS="COMMAND"
>createdb</TT
>.
For example, one could drop <TT
CLASS="LITERAL"
>template1</TT
> and recreate it from
<TT
CLASS="LITERAL"
>template0</TT
> without any ill effects. This course of action
might be advisable if one has carelessly added a bunch of junk in
<TT
CLASS="LITERAL"
>template1</TT
>.
</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="manage-ag-createdb.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="manage-ag-config.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Creating a Database</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="managing-databases.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Database Configuration</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
