path: root/doc/src/sgml/manage-ag.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.20 2001/11/28 20:49:10 petere Exp $
-->

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

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

 <para>
  A database is a named collection of SQL objects (<quote>database
  objects</quote>).  Generally, every database object (tables, functions,
  etc.) belongs to one and only one database.  (But there are a few system
  catalogs, for example <literal>pg_database</>, that belong to a whole
  installation and are accessible from each database within the
  installation.)
  An application that connects
  to the database server specifies in its connection request the
  name of the database it wants to connect to. It is not possible to
  access more than one database per connection. (But an application
  is not restricted in the number of connections it opens to the same
  or other databases.)
 </para>

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

 <para>
  In order to create or drop databases, the <productname>PostgreSQL</>
  <application>postmaster</> must be up and running (see <xref
  linkend="postmaster-start">).
 </para>

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

  <para>
   Databases are created with the query language command
   <command>CREATE DATABASE</command>:
<synopsis>
CREATE DATABASE <replaceable>name</>
</synopsis>
   where <replaceable>name</> follows the usual rules for SQL identifiers.
   The current user automatically becomes
   the owner of the new database. It is the privilege of the owner of
   a database to remove it later on (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="user-attributes"> for how to grant permission.
  </para>

  <formalpara>
   <title>Bootstrapping:</title>
   <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</> database at any given
    site can be created. The first database is always created by the
    <command>initdb</> command when the data storage area is
    initialized. (See <xref linkend="creating-cluster">.) By convention
    this database is called <literal>template1</>. So
    to create the first <quote>real</> database you can connect to
    <literal>template1</>.
   </para>
  </formalpara>

  <para>
   The name <quote>template1</quote> is no accident: When a new
   database is created, the template database is essentially cloned.
   This means that any changes you make in <literal>template1</> are
   propagated to all subsequently created databases. This implies that
   you should not use the template database for real work, but when
   used judiciously this feature can be convenient.  More details appear
   below.
  </para>

  <para>
   As an extra convenience, there is also a program that you can
   execute from the shell to create new databases,
   <filename>createdb</>.

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

   <command>createdb</> does no magic. It connects to the template1
   database and issues the <command>CREATE DATABASE</> command,
   exactly as described above. It uses the <application>psql</> program
   internally. The reference page on <command>createdb</> contains the invocation
   details. Note that <command>createdb</> without any arguments will create
   a database with the current user name, which may or may not be what
   you want.
  </para>

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

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

  <para>
   There is a second standard system database named <literal>template0</>.
   This database contains the same data as the initial contents of
   <literal>template1</>, that is, only the standard objects predefined by
   your version of <productname>PostgreSQL</productname>.
     <literal>template0</> should never be changed 
   after <literal>initdb</>.  By instructing <command>CREATE DATABASE</> to
   copy <literal>template0</> instead of <literal>template1</>, you can
   create a <quote>virgin</> user database that contains none of the
   site-local additions in <literal>template1</>.  This is particularly
   handy when restoring a <literal>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 <literal>template1</>.
  </para>

  <para>
   It is possible to create additional template databases, and indeed
   one might copy any database in an installation by specifying its name
   as the template for <command>CREATE DATABASE</>.  It is important to
   understand, however, that this is not (yet) intended as
   a general-purpose <quote>COPY DATABASE</quote> 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.  <command>CREATE DATABASE</>
   will check
   that no backend processes (other than itself) are 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.
  </para>

  <para>
   Two useful flags exist in <literal>pg_database</literal> for each
   database: <literal>datistemplate</literal> and
   <literal>datallowconn</literal>.  <literal>datistemplate</literal>
   may be set to indicate that a database is intended as a template for
   <command>CREATE DATABASE</>.  If this flag is set, the database may be
   cloned by 
   any user with <literal>CREATEDB</> privileges; if it is not set, only superusers
   and the owner of the database may clone it.
   If <literal>datallowconn</literal> 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 <literal>template0</literal>
   database is normally marked <literal>datallowconn</literal> =
   <literal>false</> to prevent modification of it.
   Both <literal>template0</literal> and <literal>template1</literal>
   should always be marked with <literal>datistemplate</literal> =
   <literal>true</>.
  </para>

  <para>
   After preparing a template database, or making any changes to one,
   it is a good idea to perform
   <command>VACUUM FREEZE</> or <command>VACUUM FULL FREEZE</> in that
   database.  If this is done when there are no other open transactions
   in the same database, then it is guaranteed that all tuples in the
   database are <quote>frozen</> and will not be subject to transaction
   ID wraparound problems.  This is particularly important for a database
   that will have <literal>datallowconn</literal> set to false, since it
   will be impossible to do routine maintenance <command>VACUUM</>s on
   such a database.
   See <xref linkend="vacuum-for-wraparound"> for more information.
  </para>

  <note>
   <para>
    <literal>template1</> and <literal>template0</> do not have any special
    status beyond the fact that the name <literal>template1</> is the default
    source database name for <command>CREATE DATABASE</> and the default
    database-to-connect-to for various scripts such as <literal>createdb</>.
    For example, one could drop <literal>template1</> and recreate it from
    <literal>template0</> without any ill effects.  This course of action
    might be advisable if one has carelessly added a bunch of junk in
    <literal>template1</>.
   </para>
  </note>

  </sect2>

  <sect2 id="manage-ag-alternate-locs">
   <title>Alternative Locations</title>

   <para>
    It is possible to create a database in a location other than the
    default location for the installation. Remember that all database access
    occurs through the 
    database server, so any location specified must be
    accessible by the server.
   </para>

   <para>
    Alternative database locations are referenced by an environment
    variable which gives the absolute path to the intended storage
    location. This environment variable must be present in the server's
    environment, so it must have been defined before the server
    was started.  (Thus, the set of available alternative locations is
    under the site administrator's control; ordinary users can't
    change it.)  Any valid environment variable name may
    be used to reference an alternative location, although using
    variable names with a prefix of <literal>PGDATA</> is recommended
    to avoid confusion and conflict with other variables.
   </para>

   <para>
    To create the variable in the environment of the server process
    you must first shut down the server, define the variable,
    initialize the data area, and finally restart the server. (See
    <xref linkend="postmaster-shutdown"> and <xref
    linkend="postmaster-start">.) To set an environment variable, type
    <informalexample>
<programlisting>
PGDATA2=/home/postgres/data
export PGDATA2
</programlisting>
    </informalexample>
    in Bourne shells, or
    <informalexample>
<programlisting>
setenv PGDATA2 /home/postgres/data
</programlisting>
    </informalexample>
    in <application>csh</> or <application>tcsh</>. You have to make sure that this environment
    variable is always defined in the server environment, otherwise
    you won't be able to access that database. Therefore you probably
    want to set it in some sort of shell start-up file or server
    start-up script.
   </para>

   <para>
    <indexterm><primary>initlocation</></>
    To create a data storage area in <envar>PGDATA2</>, ensure that
    the containing directory (here, <filename>/home/postgres</filename>)
    already exists and is writable
    by the user account that runs the server (see <xref
    linkend="postgres-user">). Then from the command line, type
    <informalexample>
<programlisting>
initlocation PGDATA2
</programlisting>
    </informalexample>
    Then you can restart the server.
   </para>

   <para>
    To create a database within the new location, use the command
<synopsis>
CREATE DATABASE <replaceable>name</> WITH LOCATION = '<replaceable>location</>'
</synopsis>
    where <replaceable>location</> is the environment variable you
    used, <envar>PGDATA2</> in this example. The <command>createdb</>
    command has the option <option>-D</> for this purpose.
   </para>

   <para>
    Databases created in alternative locations can be
    accessed and dropped like any other database.
   </para>

   <note>
    <para>
     It can also be possible to specify absolute paths directly to the
     <command>CREATE DATABASE</> command without defining environment
     variables. This is disallowed by default because it is a security
     risk. To allow it, you must compile <productname>PostgreSQL</> with
     the C preprocessor macro <literal>ALLOW_ABSOLUTE_DBPATHS</>
     defined. One way to do this is to run the compilation step like
     this:
<programlisting>
gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all
</programlisting>
    </para>
   </note>

  </sect2>
 </sect1>

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

  <para>
   Databases are destroyed with the command <command>DROP DATABASE</command>:
<synopsis>
DROP DATABASE <replaceable>name</>
</synopsis>
   Only the owner of the database (i.e., the user that created it), 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</>
   database, 
   which 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:
<synopsis>
dropdb <replaceable class="parameter">dbname</replaceable>
</synopsis>
   (Unlike <command>createdb</>, it is not the default action to drop
   the database with the current user name.)
  </para>
 </sect1>
</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->