1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>19.3.Template Databases</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="managing-databases.html" title="Chapter19.Managing Databases">
<link rel="prev" href="manage-ag-createdb.html" title="19.2.Creating a Database">
<link rel="next" href="manage-ag-config.html" title="19.4.Database Configuration">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="manage-ag-templatedbs"></a>19.3.Template Databases</h2></div></div></div>
<p> <code class="command">CREATE DATABASE</code> actually works by copying an existing
database. By default, it copies the standard system database named
<code class="literal">template1</code>.<a name="id659247"></a> Thus that
database is the “<span class="quote">template</span>” from which new databases are
made. If you add objects to <code class="literal">template1</code>, 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 <code class="literal">template1</code>, 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
<code class="literal">template0</code>.<a name="id659296"></a> This
database contains the same data as the initial contents of
<code class="literal">template1</code>, that is, only the standard objects
predefined by your version of
<span class="productname">PostgreSQL</span>. <code class="literal">template0</code>
should never be changed after <code class="command">initdb</code>. By instructing
<code class="command">CREATE DATABASE</code> to copy <code class="literal">template0</code> instead
of <code class="literal">template1</code>, you can create a “<span class="quote">virgin</span>” user
database that contains none of the site-local additions in
<code class="literal">template1</code>. This is particularly handy when restoring a
<code class="literal">pg_dump</code> 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 <code class="literal">template1</code>.
</p>
<p> To create a database by copying <code class="literal">template0</code>, use
</p>
<pre class="programlisting">CREATE DATABASE <em class="replaceable"><code>dbname</code></em> TEMPLATE template0;</pre>
<p>
from the SQL environment, or
</p>
<pre class="programlisting">createdb -T template0 <em class="replaceable"><code>dbname</code></em></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 <code class="command">CREATE DATABASE</code>. It is important to
understand, however, that this is not (yet) intended as
a general-purpose “<span class="quote"><code class="command">COPY DATABASE</code></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. <code class="command">CREATE DATABASE</code>
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 <code class="literal">pg_database</code><a name="id659461"></a> for each
database: the columns <code class="literal">datistemplate</code> and
<code class="literal">datallowconn</code>. <code class="literal">datistemplate</code>
may be set to indicate that a database is intended as a template for
<code class="command">CREATE DATABASE</code>. If this flag is set, the database may be
cloned by
any user with <code class="literal">CREATEDB</code> privileges; if it is not set, only superusers
and the owner of the database may clone it.
If <code class="literal">datallowconn</code> 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 <code class="literal">template0</code>
database is normally marked <code class="literal">datallowconn = false</code> to prevent modification of it.
Both <code class="literal">template0</code> and <code class="literal">template1</code>
should always be marked with <code class="literal">datistemplate = true</code>.
</p>
<p> After preparing a template database, or making any changes to one,
it is a good idea to perform <code class="command">VACUUM FREEZE</code> 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 <code class="literal">datallowconn</code> set to false, since it
will be impossible to do routine maintenance <code class="command">VACUUM</code> in
such a database.
See <a href="maintenance.html#vacuum-for-wraparound" title="22.1.3.Preventing transaction ID wraparound failures">Section22.1.3, “Preventing transaction ID wraparound failures”</a> for more information.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> <code class="literal">template1</code> and <code class="literal">template0</code> do not have any special
status beyond the fact that the name <code class="literal">template1</code> is the default
source database name for <code class="command">CREATE DATABASE</code>.
For example, one could drop <code class="literal">template1</code> and recreate it from
<code class="literal">template0</code> without any ill effects. This course of action
might be advisable if one has carelessly added a bunch of junk in
<code class="literal">template1</code>.
</p>
<p> The <code class="literal">postgres</code> database 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
<code class="literal">template1</code> and may be dropped and recreated if required.
</p>
</div>
</div></body>
</html>
|
