manage-ag.sgml

PostgreSQL7.4.6 for Linux

  <para>
   Two useful flags exist in <literal>pg_database</literal><indexterm><primary>pg_database</></> for each
   database: the columns <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 = false</> to prevent modification of it.
   Both <literal>template0</literal> and <literal>template1</literal>
   should always be marked with <literal>datistemplate = 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 rows 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</> in
   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 programs such as <command>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>
 </sect1>

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

  <para>
   Recall from <xref linkend="runtime-config"> that the
   <productname>PostgreSQL</> 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 you can
   execute the command
<programlisting>
ALTER DATABASE mydb SET geqo TO off;
</programlisting>
   This will save the setting (but not set it immediately) and in
   subsequent connections it will appear as though <literal>SET geqo
   TO off;</literal> had been called right before the session started.
   Note that users can still alter this setting during the session; it
   will only be the default.  To undo any such setting, use
   <literal>ALTER DATABASE <replaceable>dbname</> RESET
   <replaceable>varname</>;</literal>.
  </para>
 </sect1>

 <sect1 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. But 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 also
    <xref linkend="postmaster-shutdown"> and <xref
    linkend="postmaster-start">.) To set an environment variable, type
<programlisting>
PGDATA2=/home/postgres/data
export PGDATA2
</programlisting>
    in Bourne shells, or
<programlisting>
setenv PGDATA2 /home/postgres/data
</programlisting>
    in <command>csh</> or <command>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
<programlisting>
initlocation PGDATA2
</programlisting>
    (<emphasis>not</emphasis> <literal>initlocation
    $PGDATA2</literal>).  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>
 </sect1>

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

  <para>
   Databases are destroyed with the command <command>DROP
   DATABASE</command>:<indexterm><primary>DROP DATABASE</></>
<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.
   <literal>template1</> 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:<indexterm><primary>dropdb</></>
<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:
-->