manage-ag.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

   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</>.
    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>

   <para>
    The <literal>postgres</> 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</> and may be dropped and recreated if required.
   </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 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</> RESET
   <replaceable>varname</>;</literal>.
  </para>
 </sect1>

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

  <para>
   Databases are destroyed with the command 
   <xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title">:<indexterm><primary>DROP DATABASE</></>
<synopsis>
DROP DATABASE <replaceable>name</>;
</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</>
   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, <xref linkend="app-dropdb">:<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>

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

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

   <para>
    Tablespaces in <productname>PostgreSQL</> 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</> 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>

   <para>
    To define a tablespace, use the <xref
    linkend="sql-createtablespace" endterm="sql-createtablespace-title">
    command, for example:<indexterm><primary>CREATE TABLESPACE</></>
<programlisting>
CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
</programlisting>
    The location must be an existing, empty directory that is owned by
    the <productname>PostgreSQL</> system user.  All objects subsequently
    created within the tablespace will be stored in files underneath this
    directory.
   </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</> 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 make use of it.
    To do that, grant them the <literal>CREATE</> 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</>
    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</>:
<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</> is set to anything but an empty
    string, it supplies an implicit <literal>TABLESPACE</> clause for
    <command>CREATE TABLE</> and <command>CREATE INDEX</> commands that
    do not have an explicit one.
   </para>

   <para>
    The tablespace associated with a database is used to store the system
    catalogs of that database, as well as any temporary files created by
    server processes using that database.  Furthermore, it is the default
    tablespace selected for tables and indexes created within the database,
    if no <literal>TABLESPACE</> clause is given (either explicitly or via
    <varname>default_tablespace</>) when the objects are created.
    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 by <literal>initdb</>. The
    <literal>pg_global</> tablespace is used for shared system catalogs. The
    <literal>pg_default</> tablespace is the default tablespace of the
    <literal>template1</> and <literal>template0</> databases (and, therefore,
    will be the default tablespace for other databases as well, unless
    overridden by a <literal>TABLESPACE</> clause in <command>CREATE
    DATABASE</>).
   </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" endterm="sql-droptablespace-title">
    command.
   </para>

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

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

   <para>
    The directory <filename>$PGDATA/pg_tblspc</> 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.  Two warnings: do not do so
    while the postmaster is running; and after you restart the postmaster,
    update the <structname>pg_tablespace</> catalog to show the new
    locations.  (If you do not, <literal>pg_dump</> will continue to show
    the old tablespace locations.)
   </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:
-->