doc/src/sgml/manage-ag.sgml

<!-- doc/src/sgml/manage-ag.sgml -->

<chapter id="managing-databases">
 <title>Managing Databases</title>

 <indexterm zone="managing-databases"><primary>database</primary></indexterm>

 <para>
  Every instance of a running <productname>PostgreSQL</productname>
  server manages one or more databases.  Databases are therefore the
  topmost hierarchical level for organizing <acronym>SQL</acronym>
  objects (<quote>database objects</quote>).  This chapter describes
  the properties of databases, and how to create, manage, and destroy
  them.
 </para>

 <sect1 id="manage-ag-overview">
  <title>Overview</title>

  <indexterm zone="manage-ag-overview">
   <primary>schema</primary>
  </indexterm>

  <para>
   A small number of objects, like role, database, and tablespace
   names, are defined at the cluster level and stored in the
   <literal>pg_global</literal> tablespace.  Inside the cluster are
   multiple databases, which are isolated from each other but can access
   cluster-level objects.  Inside each database are multiple schemas,
   which contain objects like tables and functions.  So the full hierarchy
   is: cluster, database, schema, table (or some other kind of object,
   such as a function).
  </para>

  <para>
   When connecting to the database server, a client must specify the
   database name in its connection request.
   It is not possible to access more than one database per
   connection. However, clients can open multiple connections to
   the same database, or different databases.
   Database-level security has two components: access control
   (see <xref linkend="auth-pg-hba-conf"/>), managed at the
   connection level, and authorization control
   (see <xref linkend="ddl-priv"/>), managed via the grant system.
   Foreign data wrappers (see <xref linkend="postgres-fdw"/>)
   allow for objects within one database to act as proxies for objects in
   other database or clusters.
   The older dblink module (see <xref linkend="dblink"/>) provides a similar capability.
   By default, all users can connect to all databases using all connection methods.
  </para>

  <para>
   If one <productname>PostgreSQL</productname> server cluster is planned to contain
   unrelated projects or users that should be, for the most part, unaware
   of each other, it is recommended to put them into separate databases and
   adjust authorizations and access controls accordingly.
   If the projects or users are interrelated, and thus should be able to use
   each other's resources, they should be put in the same database but probably
   into separate schemas;  this provides a modular structure with namespace
   isolation and authorization control.
   More information about managing schemas is in <xref linkend="ddl-schemas"/>.
  </para>

  <para>
   While multiple databases can be created within a single cluster, it is advised
   to consider carefully whether the benefits outweigh the risks and limitations.
   In particular, the impact that having a shared WAL (see <xref linkend="wal"/>)
   has on backup and recovery options. While individual databases in the cluster
   are isolated when considered from the user's perspective, they are closely bound
   from the database administrator's point-of-view.
  </para>

  <para>
   Databases are created with the <command>CREATE DATABASE</command> command
   (see <xref linkend="manage-ag-createdb"/>) and destroyed with the
   <command>DROP DATABASE</command> command
   (see <xref linkend="manage-ag-dropdb"/>).
   To determine the set of existing databases, examine the
   <structname>pg_database</structname> system catalog, for example
<synopsis>
SELECT datname FROM pg_database;
</synopsis>
   The <xref linkend="app-psql"/> program's <literal>\l</literal> meta-command
   and <option>-l</option> command-line option are also useful for listing the
   existing databases.
  </para>

  <note>
   <para>
    The <acronym>SQL</acronym> standard calls databases <quote>catalogs</quote>, but there
    is no difference in practice.
   </para>
  </note>
 </sect1>

 <sect1 id="manage-ag-createdb">
  <title>Creating a Database</title>

  <indexterm><primary>CREATE DATABASE</primary></indexterm>

  <para>
   In order to create a database, the <productname>PostgreSQL</productname>
   server must be up and running (see <xref
   linkend="server-start"/>).
  </para>

  <para>
   Databases are created with the SQL command
   <xref linkend="sql-createdatabase"/>:
<synopsis>
CREATE DATABASE <replaceable>name</replaceable>;
</synopsis>
   where <replaceable>name</replaceable> follows the usual rules for
   <acronym>SQL</acronym> identifiers.  The current role automatically
   becomes the owner of the new database. It is the privilege of the
   owner of a database to remove it later (which also removes all
   the objects in it, even if they have a different owner).
  </para>

  <para>
   The creation of databases is a restricted operation. See <xref
   linkend="role-attributes"/> for how to grant permission.
  </para>

  <para>
   Since you need to be connected to the database server in order to
   execute the <command>CREATE DATABASE</command> command, the
   question remains how the <emphasis>first</emphasis> database at any given
   site can be created. The first database is always created by the
   <command>initdb</command> command when the data storage area is
   initialized. (See <xref linkend="creating-cluster"/>.)  This
   database is called
   <literal>postgres</literal>.<indexterm><primary>postgres</primary></indexterm> So to
   create the first <quote>ordinary</quote> database you can connect to
   <literal>postgres</literal>.
  </para>

  <para>
   A second database,
   <literal>template1</literal>,<indexterm><primary>template1</primary></indexterm>
   is also created during database cluster initialization.  Whenever a
   new database is created within the
   cluster, <literal>template1</literal> is essentially cloned.
   This means that any changes you make in <literal>template1</literal> are
   propagated to all subsequently created databases. Because of this,
   avoid creating objects in <literal>template1</literal> unless you want them
   propagated to every newly created database.  More details
   appear in <xref linkend="manage-ag-templatedbs"/>.
  </para>

  <para>
   As a convenience, there is a program you can
   execute from the shell to create new databases,
   <command>createdb</command>.<indexterm><primary>createdb</primary></indexterm>

<synopsis>
createdb <replaceable class="parameter">dbname</replaceable>
</synopsis>

   <command>createdb</command> does no magic. It connects to the <literal>postgres</literal>
   database and issues the <command>CREATE DATABASE</command> command,
   exactly as described above.
   The <xref linkend="app-createdb"/> reference page contains the invocation
   details. Note that <command>createdb</command> without any arguments will create
   a database with the current user name.
  </para>

  <note>
   <para>
    <xref linkend="client-authentication"/> contains information about
    how to restrict who can connect to a given database.
   </para>
  </note>

  <para>
   Sometimes you want to create a database for someone else, and have them
   become the owner of the new database, so they can
   configure and manage it themselves.  To achieve that, use one of the
   following commands:
<programlisting>
CREATE DATABASE <replaceable>dbname</replaceable> OWNER <replaceable>rolename</replaceable>;
</programlisting>
   from the SQL environment, or:
<programlisting>
createdb -O <replaceable>rolename</replaceable> <replaceable>dbname</replaceable>
</programlisting>
   from the shell.
   Only the superuser is allowed to create a database for
   someone else (that is, for a role you are not a member of).
  </para>
 </sect1>

 <sect1 id="manage-ag-templatedbs">
  <title>Template Databases</title>

  <para>
   <command>CREATE DATABASE</command> actually works by copying an existing
   database.  By default, it copies the standard system database named
   <literal>template1</literal>.<indexterm><primary>template1</primary></indexterm> Thus that
   database is the <quote>template</quote> from which new databases are
   made.  If you add objects to <literal>template1</literal>, 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 <application>PL/Perl</application> in <literal>template1</literal>, it will
   automatically be available in user databases without any extra
   action being taken when those databases are created.
  </para>

  <para>
   There is a second standard system database named
   <literal>template0</literal>.<indexterm><primary>template0</primary></indexterm> This
   database contains the same data as the initial contents of
   <literal>template1</literal>, that is, only the standard objects
   predefined by your version of
   <productname>PostgreSQL</productname>.  <literal>template0</literal>
   should never be changed after the database cluster has been
   initialized.  By instructing
   <command>CREATE DATABASE</command> to copy <literal>template0</literal> instead
   of <literal>template1</literal>, you can create a <quote>pristine</quote> user
   database (one where no user-defined objects exist and where the system
   objects have not been altered) that contains none of the site-local additions in
   <literal>template1</literal>.  This is particularly handy when restoring a
   <literal>pg_dump</literal> dump: the dump script should be restored in a
   pristine database to ensure that one recreates the correct contents
   of the dumped database, without conflicting with objects that
   might have been added to <literal>template1</literal> later on.
  </para>

  <para>
   Another common reason for copying <literal>template0</literal> instead
   of <literal>template1</literal> is that new encoding and locale settings
   can be specified when copying <literal>template0</literal>, whereas a copy
   of <literal>template1</literal> must use the same settings it does.
   This is because <literal>template1</literal> might contain encoding-specific
   or locale-specific data, while <literal>template0</literal> is known not to.
  </para>

  <para>
   To create a database by copying <literal>template0</literal>, use:
<programlisting>
CREATE DATABASE <replaceable>dbname</replaceable> TEMPLATE template0;
</programlisting>
   from the SQL environment, or:
<programlisting>
createdb -T template0 <replaceable>dbname</replaceable>
</programlisting>
   from the shell.
  </para>

  <para>
   It is possible to create additional template databases, and indeed
   one can copy any database in a cluster by specifying its name
   as the template for <command>CREATE DATABASE</command>.  It is important to
   understand, however, that this is not (yet) intended as
   a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
   The principal limitation is that no other sessions can be connected to
   the source database while it is being copied.  <command>CREATE
   DATABASE</command> will fail if any other connection exists when it starts;
   during the copy operation, new connections to the source database
   are prevented.
  </para>

  <para>
   Two useful flags exist in <literal>pg_database</literal><indexterm><primary>pg_database</primary></indexterm> for each
   database: the columns <literal>datistemplate</literal> and
   <literal>datallowconn</literal>.  <literal>datistemplate</literal>
   can be set to indicate that a database is intended as a template for
   <command>CREATE DATABASE</command>.  If this flag is set, the database can be
   cloned by any user with <literal>CREATEDB</literal> privileges; if it is not set,
   only superusers and the owner of the database can clone it.
   If <literal>datallowconn</literal> is false, then no new connections
   to that database will be allowed (but existing sessions are not terminated
   simply by setting the flag false).  The <literal>template0</literal>
   database is normally marked <literal>datallowconn = false</literal> to prevent its modification.
   Both <literal>template0</literal> and <literal>template1</literal>
   should always be marked with <literal>datistemplate = true</literal>.
  </para>

  <note>
   <para>
    <literal>template1</literal> and <literal>template0</literal> do not have any special
    status beyond the fact that the name <literal>template1</literal> is the default
    source database name for <command>CREATE DATABASE</command>.
    For example, one could drop <literal>template1</literal> and recreate it from
    <literal>template0</literal> without any ill effects.  This course of action
    might be advisable if one has carelessly added a bunch of junk in
    <literal>template1</literal>. (To delete <literal>template1</literal>,
    it must have <literal>pg_database.datistemplate = false</literal>.)
   </para>

   <para>
    The <literal>postgres</literal> 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
    <literal>template1</literal> and can be dropped and recreated if necessary.
   </para>
  </note>
 </sect1>

 <sect1 id="manage-ag-config">
  <title>Database Configuration</title>

  <para>
   Recall from <xref linkend="runtime-config"/> that the
   <productname>PostgreSQL</productname> server provides a large number of
   run-time configuration variables.  You can set database-specific
   default values for many of these settings.
  </para>

  <para>
   For example, if for some reason you want to disable the
   <acronym>GEQO</acronym> optimizer for a given database, you'd
   ordinarily have to either disable it for all databases or make sure
   that every connecting client is careful to issue <literal>SET geqo
   TO off</literal>.  To make this setting the default within a particular
   database, you can execute the command:
<programlisting>
ALTER DATABASE mydb SET geqo TO off;
</programlisting>
   This will save the setting (but not set it immediately).  In
   subsequent connections to this database it will appear as though
   <literal>SET geqo TO off;</literal> had been executed just before the
   session started.
   Note that users can still alter this setting during their sessions; it
   will only be the default.  To undo any such setting, use
   <literal>ALTER DATABASE <replaceable>dbname</replaceable> RESET
   <replaceable>varname</replaceable></literal>.
  </para>
 </sect1>

 <sect1 id="manage-ag-dropdb">
  <title>Destroying a Database</title>

  <para>
   Databases are destroyed with the command
   <xref linkend="sql-dropdatabase"/>:<indexterm><primary>DROP DATABASE</primary></indexterm>
<synopsis>
DROP DATABASE <replaceable>name</replaceable>;
</synopsis>
   Only the owner of the database, or
   a superuser, can drop a database. Dropping a database removes all objects
   that were
   contained within the database. The destruction of a database cannot
   be undone.
  </para>

  <para>
   You cannot execute the <command>DROP DATABASE</command> command
   while connected to the victim database. You can, however, be
   connected to any other database, including the <literal>template1</literal>
   database.
   <literal>template1</literal> would be the only option for dropping the last user database of a
   given cluster.
  </para>

  <para>
   For convenience, there is also a shell program to drop
   databases, <xref linkend="app-dropdb"/>:<indexterm><primary>dropdb</primary></indexterm>
<synopsis>
dropdb <replaceable class="parameter">dbname</replaceable>
</synopsis>
   (Unlike <command>createdb</command>, it is not the default action to drop
   the database with the current user name.)
  </para>
 </sect1>

 <sect1 id="manage-ag-tablespaces">
  <title>Tablespaces</title>

  <indexterm zone="manage-ag-tablespaces">
   <primary>tablespace</primary>
  </indexterm>

  <para>
   Tablespaces in <productname>PostgreSQL</productname> allow database administrators to
   define locations in the file system where the files representing
   database objects can be stored. Once created, a tablespace can be referred
   to by name when creating database objects.
  </para>

  <para>
   By using tablespaces, an administrator can control the disk layout
   of a <productname>PostgreSQL</productname> installation. This is useful in at
   least two ways. First, if the partition or volume on which the
   cluster was initialized runs out of space and cannot be extended,
   a tablespace can be created on a different partition and used
   until the system can be reconfigured.
  </para>

  <para>
   Second, tablespaces allow an administrator to use knowledge of the
   usage pattern of database objects to optimize performance. For
   example, an index which is very heavily used can be placed on a
   very fast, highly available disk, such as an expensive solid state
   device. At the same time a table storing archived data which is
   rarely used or not performance critical could be stored on a less
   expensive, slower disk system.
  </para>

  <warning>
   <para>
     Even though located outside the main PostgreSQL data directory,
     tablespaces are an integral part of the database cluster and
     <emphasis>cannot</emphasis> be treated as an autonomous collection
     of data files. They are dependent on metadata contained in the main
     data directory, and therefore cannot be attached to a different
     database cluster or backed up individually.  Similarly, if you lose
     a tablespace (file deletion, disk failure, etc), the database cluster
     might become unreadable or unable to start.  Placing a tablespace
     on a temporary file system like a RAM disk risks the reliability of
     the entire cluster.
   </para>
  </warning>

  <para>
   To define a tablespace, use the <xref
   linkend="sql-createtablespace"/>
   command, for example:<indexterm><primary>CREATE TABLESPACE</primary></indexterm>:
<programlisting>
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
</programlisting>
   The location must be an existing, empty directory that is owned by
   the <productname>PostgreSQL</productname> operating system user.  All objects subsequently
   created within the tablespace will be stored in files underneath this
   directory.  The location must not be on removable or transient storage,
   as the cluster might fail to function if the tablespace is missing
   or lost.
  </para>

  <note>
   <para>
    There is usually not much point in making more than one
    tablespace per logical file system, since you cannot control the location
    of individual files within a logical file system.  However,
    <productname>PostgreSQL</productname> does not enforce any such limitation, and
    indeed it is not directly aware of the file system boundaries on your
    system.  It just stores files in the directories you tell it to use.
   </para>
  </note>

  <para>
   Creation of the tablespace itself must be done as a database superuser,
   but after that you can allow ordinary database users to use it.
   To do that, grant them the <literal>CREATE</literal> privilege on it.
  </para>

  <para>
   Tables, indexes, and entire databases can be assigned to
   particular tablespaces. To do so, a user with the <literal>CREATE</literal>
   privilege on a given tablespace must pass the tablespace name as a
   parameter to the relevant command. For example, the following creates
   a table in the tablespace <literal>space1</literal>:
<programlisting>
CREATE TABLE foo(i int) TABLESPACE space1;
</programlisting>
  </para>

  <para>
   Alternatively, use the <xref linkend="guc-default-tablespace"/> parameter:
<programlisting>
SET default_tablespace = space1;
CREATE TABLE foo(i int);
</programlisting>
   When <varname>default_tablespace</varname> is set to anything but an empty
   string, it supplies an implicit <literal>TABLESPACE</literal> clause for
   <command>CREATE TABLE</command> and <command>CREATE INDEX</command> commands that
   do not have an explicit one.
  </para>

  <para>
   There is also a <xref linkend="guc-temp-tablespaces"/> parameter, which
   determines the placement of temporary tables and indexes, as well as
   temporary files that are used for purposes such as sorting large data
   sets.  This can be a list of tablespace names, rather than only one,
   so that the load associated with temporary objects can be spread over
   multiple tablespaces.  A random member of the list is picked each time
   a temporary object is to be created.
  </para>

  <para>
   The tablespace associated with a database is used to store the system
   catalogs of that database.  Furthermore, it is the default tablespace
   used for tables, indexes, and temporary files created within the database,
   if no <literal>TABLESPACE</literal> clause is given and no other selection is
   specified by <varname>default_tablespace</varname> or
   <varname>temp_tablespaces</varname> (as appropriate).
   If a database is created without specifying a tablespace for it,
   it uses the same tablespace as the template database it is copied from.
  </para>

  <para>
   Two tablespaces are automatically created when the database cluster
   is initialized.  The
   <literal>pg_global</literal> tablespace is used for shared system catalogs. The
   <literal>pg_default</literal> tablespace is the default tablespace of the
   <literal>template1</literal> and <literal>template0</literal> databases (and, therefore,
   will be the default tablespace for other databases as well, unless
   overridden by a <literal>TABLESPACE</literal> clause in <command>CREATE
   DATABASE</command>).
  </para>

  <para>
   Once created, a tablespace can be used from any database, provided
   the requesting user has sufficient privilege. This means that a tablespace
   cannot be dropped until all objects in all databases using the tablespace
   have been removed.
  </para>

  <para>
   To remove an empty tablespace, use the <xref
   linkend="sql-droptablespace"/>
   command.
  </para>

  <para>
   To determine the set of existing tablespaces, examine the
   <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname>
   </link> system catalog, for example
<synopsis>
SELECT spcname FROM pg_tablespace;
</synopsis>
   The <xref linkend="app-psql"/> program's <literal>\db</literal> meta-command
   is also useful for listing the existing tablespaces.
  </para>

  <para>
   <productname>PostgreSQL</productname> makes use of symbolic links
   to simplify the implementation of tablespaces. This
   means that tablespaces can be used <emphasis>only</emphasis> on systems
   that support symbolic links.
  </para>

  <para>
   The directory <filename>$PGDATA/pg_tblspc</filename> contains symbolic links that
   point to each of the non-built-in tablespaces defined in the cluster.
   Although not recommended, it is possible to adjust the tablespace
   layout by hand by redefining these links. Under no circumstances perform
   this operation while the server is running. Note that in PostgreSQL 9.1
   and earlier you will also need to update the <structname>pg_tablespace</structname>
   catalog with the new locations. (If you do not, <literal>pg_dump</literal> will
   continue to output the old tablespace locations.)
  </para>

 </sect1>
</chapter>