1
<!-- doc/src/sgml/manage-ag.sgml -->
3
<chapter id="managing-databases">
4
<title>Managing Databases</title>
6
<indexterm zone="managing-databases"><primary>database</></>
9
Every instance of a running <productname>PostgreSQL</productname>
10
server manages one or more databases. Databases are therefore the
11
topmost hierarchical level for organizing <acronym>SQL</acronym>
12
objects (<quote>database objects</quote>). This chapter describes
13
the properties of databases, and how to create, manage, and destroy
17
<sect1 id="manage-ag-overview">
18
<title>Overview</title>
20
<indexterm zone="manage-ag-overview">
21
<primary>schema</primary>
25
A database is a named collection of <acronym>SQL</acronym> objects
26
(<quote>database objects</quote>). Generally, every database
27
object (tables, functions, etc.) belongs to one and only one
28
database. (However there are a few system catalogs, for example
29
<literal>pg_database</>, that belong to a whole cluster and
30
are accessible from each database within the cluster.) More
31
accurately, a database is a collection of schemas and the schemas
32
contain the tables, functions, etc. So the full hierarchy is:
33
server, database, schema, table (or some other kind of object,
38
When connecting to the database server, a client must specify in
39
its connection request the name of the database it wants to connect
40
to. It is not possible to access more than one database per
41
connection. However, an application is not restricted in the number of
42
connections it opens to the same or other databases. Databases are
43
physically separated and access control is managed at the
44
connection level. If one <productname>PostgreSQL</> server
45
instance is to house projects or users that should be separate and
46
for the most part unaware of each other, it is therefore
47
recommendable to put them into separate databases. If the projects
48
or users are interrelated and should be able to use each other's
49
resources, they should be put in the same database but possibly
50
into separate schemas. Schemas are a purely logical structure and who can
51
access what is managed by the privilege system. More information about
52
managing schemas is in <xref linkend="ddl-schemas">.
56
Databases are created with the <command>CREATE DATABASE</> command
57
(see <xref linkend="manage-ag-createdb">) and destroyed with the
58
<command>DROP DATABASE</> command
59
(see <xref linkend="manage-ag-dropdb">).
60
To determine the set of existing databases, examine the
61
<structname>pg_database</> system catalog, for example
63
SELECT datname FROM pg_database;
65
The <xref linkend="app-psql"> program's <literal>\l</> meta-command
66
and <option>-l</> command-line option are also useful for listing the
72
The <acronym>SQL</> standard calls databases <quote>catalogs</>, but there
73
is no difference in practice.
78
<sect1 id="manage-ag-createdb">
79
<title>Creating a Database</title>
81
<indexterm><primary>CREATE DATABASE</></>
84
In order to create a database, the <productname>PostgreSQL</>
85
server must be up and running (see <xref
86
linkend="server-start">).
90
Databases are created with the SQL command
91
<xref linkend="sql-createdatabase">:
93
CREATE DATABASE <replaceable>name</>;
95
where <replaceable>name</> follows the usual rules for
96
<acronym>SQL</acronym> identifiers. The current role automatically
97
becomes the owner of the new database. It is the privilege of the
98
owner of a database to remove it later (which also removes all
99
the objects in it, even if they have a different owner).
103
The creation of databases is a restricted operation. See <xref
104
linkend="role-attributes"> for how to grant permission.
108
Since you need to be connected to the database server in order to
109
execute the <command>CREATE DATABASE</command> command, the
110
question remains how the <emphasis>first</> database at any given
111
site can be created. The first database is always created by the
112
<command>initdb</> command when the data storage area is
113
initialized. (See <xref linkend="creating-cluster">.) This
115
<literal>postgres</>.<indexterm><primary>postgres</></> So to
116
create the first <quote>ordinary</> database you can connect to
117
<literal>postgres</>.
122
<literal>template1</literal>,<indexterm><primary>template1</></>
123
is also created during database cluster initialization. Whenever a
124
new database is created within the
125
cluster, <literal>template1</literal> is essentially cloned.
126
This means that any changes you make in <literal>template1</> are
127
propagated to all subsequently created databases. Because of this,
128
avoid creating objects in <literal>template1</> unless you want them
129
propagated to every newly created database. More details
130
appear in <xref linkend="manage-ag-templatedbs">.
134
As a convenience, there is a program you can
135
execute from the shell to create new databases,
136
<command>createdb</>.<indexterm><primary>createdb</></>
139
createdb <replaceable class="parameter">dbname</replaceable>
142
<command>createdb</> does no magic. It connects to the <literal>postgres</>
143
database and issues the <command>CREATE DATABASE</> command,
144
exactly as described above.
145
The <xref linkend="app-createdb"> reference page contains the invocation
146
details. Note that <command>createdb</> without any arguments will create
147
a database with the current user name.
152
<xref linkend="client-authentication"> contains information about
153
how to restrict who can connect to a given database.
158
Sometimes you want to create a database for someone else, and have him
159
become the owner of the new database, so he can
160
configure and manage it himself. To achieve that, use one of the
163
CREATE DATABASE <replaceable>dbname</> OWNER <replaceable>rolename</>;
165
from the SQL environment, or:
167
createdb -O <replaceable>rolename</> <replaceable>dbname</>
170
Only the superuser is allowed to create a database for
171
someone else (that is, for a role you are not a member of).
175
<sect1 id="manage-ag-templatedbs">
176
<title>Template Databases</title>
179
<command>CREATE DATABASE</> actually works by copying an existing
180
database. By default, it copies the standard system database named
181
<literal>template1</>.<indexterm><primary>template1</></> Thus that
182
database is the <quote>template</> from which new databases are
183
made. If you add objects to <literal>template1</>, these objects
184
will be copied into subsequently created user databases. This
185
behavior allows site-local modifications to the standard set of
186
objects in databases. For example, if you install the procedural
187
language <application>PL/Perl</> in <literal>template1</>, it will
188
automatically be available in user databases without any extra
189
action being taken when those databases are created.
193
There is a second standard system database named
194
<literal>template0</>.<indexterm><primary>template0</></> This
195
database contains the same data as the initial contents of
196
<literal>template1</>, that is, only the standard objects
197
predefined by your version of
198
<productname>PostgreSQL</productname>. <literal>template0</>
199
should never be changed after the database cluster has been
200
initialized. By instructing
201
<command>CREATE DATABASE</> to copy <literal>template0</> instead
202
of <literal>template1</>, you can create a <quote>virgin</> user
203
database that contains none of the site-local additions in
204
<literal>template1</>. This is particularly handy when restoring a
205
<literal>pg_dump</> dump: the dump script should be restored in a
206
virgin database to ensure that one recreates the correct contents
207
of the dumped database, without conflicting with objects that
208
might have been added to <literal>template1</> later on.
212
Another common reason for copying <literal>template0</> instead
213
of <literal>template1</> is that new encoding and locale settings
214
can be specified when copying <literal>template0</>, whereas a copy
215
of <literal>template1</> must use the same settings it does.
216
This is because <literal>template1</> might contain encoding-specific
217
or locale-specific data, while <literal>template0</> is known not to.
221
To create a database by copying <literal>template0</literal>, use:
223
CREATE DATABASE <replaceable>dbname</> TEMPLATE template0;
225
from the SQL environment, or:
227
createdb -T template0 <replaceable>dbname</>
233
It is possible to create additional template databases, and indeed
234
one can copy any database in a cluster by specifying its name
235
as the template for <command>CREATE DATABASE</>. It is important to
236
understand, however, that this is not (yet) intended as
237
a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
238
The principal limitation is that no other sessions can be connected to
239
the source database while it is being copied. <command>CREATE
240
DATABASE</> will fail if any other connection exists when it starts;
241
during the copy operation, new connections to the source database
246
Two useful flags exist in <literal>pg_database</literal><indexterm><primary>pg_database</></> for each
247
database: the columns <literal>datistemplate</literal> and
248
<literal>datallowconn</literal>. <literal>datistemplate</literal>
249
can be set to indicate that a database is intended as a template for
250
<command>CREATE DATABASE</>. If this flag is set, the database can be
251
cloned by any user with <literal>CREATEDB</> privileges; if it is not set,
252
only superusers and the owner of the database can clone it.
253
If <literal>datallowconn</literal> is false, then no new connections
254
to that database will be allowed (but existing sessions are not terminated
255
simply by setting the flag false). The <literal>template0</literal>
256
database is normally marked <literal>datallowconn = false</> to prevent its modification.
257
Both <literal>template0</literal> and <literal>template1</literal>
258
should always be marked with <literal>datistemplate = true</>.
263
<literal>template1</> and <literal>template0</> do not have any special
264
status beyond the fact that the name <literal>template1</> is the default
265
source database name for <command>CREATE DATABASE</>.
266
For example, one could drop <literal>template1</> and recreate it from
267
<literal>template0</> without any ill effects. This course of action
268
might be advisable if one has carelessly added a bunch of junk in
269
<literal>template1</>. (To delete <literal>template1</literal>,
270
it must have <literal>pg_database.datistemplate = false</>.)
274
The <literal>postgres</> database is also created when a database
275
cluster is initialized. This database is meant as a default database for
276
users and applications to connect to. It is simply a copy of
277
<literal>template1</> and can be dropped and recreated if necessary.
282
<sect1 id="manage-ag-config">
283
<title>Database Configuration</title>
286
Recall from <xref linkend="runtime-config"> that the
287
<productname>PostgreSQL</> server provides a large number of
288
run-time configuration variables. You can set database-specific
289
default values for many of these settings.
293
For example, if for some reason you want to disable the
294
<acronym>GEQO</acronym> optimizer for a given database, you'd
295
ordinarily have to either disable it for all databases or make sure
296
that every connecting client is careful to issue <literal>SET geqo
297
TO off</literal>. To make this setting the default within a particular
298
database, you can execute the command:
300
ALTER DATABASE mydb SET geqo TO off;
302
This will save the setting (but not set it immediately). In
303
subsequent connections to this database it will appear as though
304
<literal>SET geqo TO off;</literal> had been executed just before the
306
Note that users can still alter this setting during their sessions; it
307
will only be the default. To undo any such setting, use
308
<literal>ALTER DATABASE <replaceable>dbname</> RESET
309
<replaceable>varname</></literal>.
313
<sect1 id="manage-ag-dropdb">
314
<title>Destroying a Database</title>
317
Databases are destroyed with the command
318
<xref linkend="sql-dropdatabase">:<indexterm><primary>DROP DATABASE</></>
320
DROP DATABASE <replaceable>name</>;
322
Only the owner of the database, or
323
a superuser, can drop a database. Dropping a database removes all objects
325
contained within the database. The destruction of a database cannot
330
You cannot execute the <command>DROP DATABASE</command> command
331
while connected to the victim database. You can, however, be
332
connected to any other database, including the <literal>template1</>
334
<literal>template1</> would be the only option for dropping the last user database of a
339
For convenience, there is also a shell program to drop
340
databases, <xref linkend="app-dropdb">:<indexterm><primary>dropdb</></>
342
dropdb <replaceable class="parameter">dbname</replaceable>
344
(Unlike <command>createdb</>, it is not the default action to drop
345
the database with the current user name.)
349
<sect1 id="manage-ag-tablespaces">
350
<title>Tablespaces</title>
352
<indexterm zone="manage-ag-tablespaces">
353
<primary>tablespace</primary>
357
Tablespaces in <productname>PostgreSQL</> allow database administrators to
358
define locations in the file system where the files representing
359
database objects can be stored. Once created, a tablespace can be referred
360
to by name when creating database objects.
364
By using tablespaces, an administrator can control the disk layout
365
of a <productname>PostgreSQL</> installation. This is useful in at
366
least two ways. First, if the partition or volume on which the
367
cluster was initialized runs out of space and cannot be extended,
368
a tablespace can be created on a different partition and used
369
until the system can be reconfigured.
373
Second, tablespaces allow an administrator to use knowledge of the
374
usage pattern of database objects to optimize performance. For
375
example, an index which is very heavily used can be placed on a
376
very fast, highly available disk, such as an expensive solid state
377
device. At the same time a table storing archived data which is
378
rarely used or not performance critical could be stored on a less
379
expensive, slower disk system.
383
To define a tablespace, use the <xref
384
linkend="sql-createtablespace">
385
command, for example:<indexterm><primary>CREATE TABLESPACE</></>:
387
CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
389
The location must be an existing, empty directory that is owned by
390
the <productname>PostgreSQL</> operating system user. All objects subsequently
391
created within the tablespace will be stored in files underneath this
397
There is usually not much point in making more than one
398
tablespace per logical file system, since you cannot control the location
399
of individual files within a logical file system. However,
400
<productname>PostgreSQL</> does not enforce any such limitation, and
401
indeed it is not directly aware of the file system boundaries on your
402
system. It just stores files in the directories you tell it to use.
407
Creation of the tablespace itself must be done as a database superuser,
408
but after that you can allow ordinary database users to use it.
409
To do that, grant them the <literal>CREATE</> privilege on it.
413
Tables, indexes, and entire databases can be assigned to
414
particular tablespaces. To do so, a user with the <literal>CREATE</>
415
privilege on a given tablespace must pass the tablespace name as a
416
parameter to the relevant command. For example, the following creates
417
a table in the tablespace <literal>space1</>:
419
CREATE TABLE foo(i int) TABLESPACE space1;
424
Alternatively, use the <xref linkend="guc-default-tablespace"> parameter:
426
SET default_tablespace = space1;
427
CREATE TABLE foo(i int);
429
When <varname>default_tablespace</> is set to anything but an empty
430
string, it supplies an implicit <literal>TABLESPACE</> clause for
431
<command>CREATE TABLE</> and <command>CREATE INDEX</> commands that
432
do not have an explicit one.
436
There is also a <xref linkend="guc-temp-tablespaces"> parameter, which
437
determines the placement of temporary tables and indexes, as well as
438
temporary files that are used for purposes such as sorting large data
439
sets. This can be a list of tablespace names, rather than only one,
440
so that the load associated with temporary objects can be spread over
441
multiple tablespaces. A random member of the list is picked each time
442
a temporary object is to be created.
446
The tablespace associated with a database is used to store the system
447
catalogs of that database. Furthermore, it is the default tablespace
448
used for tables, indexes, and temporary files created within the database,
449
if no <literal>TABLESPACE</> clause is given and no other selection is
450
specified by <varname>default_tablespace</> or
451
<varname>temp_tablespaces</> (as appropriate).
452
If a database is created without specifying a tablespace for it,
453
it uses the same tablespace as the template database it is copied from.
457
Two tablespaces are automatically created when the database cluster
459
<literal>pg_global</> tablespace is used for shared system catalogs. The
460
<literal>pg_default</> tablespace is the default tablespace of the
461
<literal>template1</> and <literal>template0</> databases (and, therefore,
462
will be the default tablespace for other databases as well, unless
463
overridden by a <literal>TABLESPACE</> clause in <command>CREATE
468
Once created, a tablespace can be used from any database, provided
469
the requesting user has sufficient privilege. This means that a tablespace
470
cannot be dropped until all objects in all databases using the tablespace
475
To remove an empty tablespace, use the <xref
476
linkend="sql-droptablespace">
481
To determine the set of existing tablespaces, examine the
482
<structname>pg_tablespace</> system catalog, for example
484
SELECT spcname FROM pg_tablespace;
486
The <xref linkend="app-psql"> program's <literal>\db</> meta-command
487
is also useful for listing the existing tablespaces.
491
<productname>PostgreSQL</> makes use of symbolic links
492
to simplify the implementation of tablespaces. This
493
means that tablespaces can be used <emphasis>only</> on systems
494
that support symbolic links.
498
The directory <filename>$PGDATA/pg_tblspc</> contains symbolic links that
499
point to each of the non-built-in tablespaces defined in the cluster.
500
Although not recommended, it is possible to adjust the tablespace
501
layout by hand by redefining these links. Two warnings: do not do so
502
while the server is running; and after you restart the server,
503
update the <structname>pg_tablespace</> catalog with the new
504
locations. (If you do not, <literal>pg_dump</> will continue to output
505
the old tablespace locations.)
added
removed
doc/src/sgml/manage-ag.sgml
