<!--
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/manage.sgml,v 1.17 2001/09/28 08:15:35 thomas Exp $
-->

 <Chapter Id="manage">
  <Title>Managing a Database</Title>

  <comment>
   This section is currently a thinly disguised copy of the
   Tutorial. Needs to be augmented.
    - thomas 1998-01-12
  </comment>

  <Para>
   Although the <FirstTerm>site administrator</FirstTerm> is responsible for overall management 
   of the <ProductName>Postgres</ProductName> installation, some databases within the
   installation may be managed by another person, 
   designated the <FirstTerm>database administrator</FirstTerm>.
   This assignment of responsibilities occurs when a database is created. 
   A user may be assigned explicit privileges to create databases and/or to create new users. 
   A user assigned both privileges can perform most administrative task 
   within <ProductName>Postgres</ProductName>, but will
   not by default have the same operating system privileges as the site administrator.
  </Para>

  <Para>
   The Database Administrator's Guide covers these topics in more detail.
  </Para>

  <Sect1 id="db-creation">
   <Title>Database Creation</Title>

   <Para>
    Databases are created by the <Command>CREATE DATABASE</Command> issued from
    within <ProductName>Postgres</ProductName>. <Application>createdb</Application> is a command-line
    utility provided to give the same functionality from outside <ProductName>Postgres</ProductName>.
   </Para>

   <Para>
    The <ProductName>Postgres</ProductName> backend must be running for either method
    to succeed, and the user issuing the command must be the <ProductName>Postgres</ProductName>
    <FirstTerm>superuser</FirstTerm> or have been assigned database creation privileges by the
    superuser.
   </Para>

   <Para>
    To create a new database named <literal>mydb</literal> from the command line, type
<ProgramListing>
% createdb mydb
</ProgramListing>

    and to do the same from within <Application>psql</Application> type
<ProgramListing>
=> CREATE DATABASE mydb;
</ProgramListing>
   </Para>

   <Para>
    If you do not have the privileges required to create a database, you will see
    the following:
<ProgramListing>
ERROR:  CREATE DATABASE: Permission denied.
</ProgramListing>
   </Para>

   <Para>
    <ProductName>Postgres</ProductName>  allows  you to create any number of databases
    at a  given  site  and  you  automatically  become  the
    database  administrator  of  the database you just created.  
    Database names must  have  an  alphabetic  first
    character and are limited to 32 characters in length.
   </Para>

  </Sect1>

  <Sect1 id="altern-locations">
   <Title>Alternate Database 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 actually
    occurs through the database backend, so that any location specified must
    be accessible by the backend.
   </Para>

   <Para>
    Alternate database locations are created and referenced by an environment variable 
    which gives the absolute path to the intended storage location.  
    This environment variable must have been defined before the postmaster was started 
    and the location it points to must be writable by the administrator account.
    Consult with the site administrator
    regarding preconfigured alternative database locations.  
    Any valid environment variable name may be used to reference an alternate location, 
    although using variable names with a prefix of <envar>PGDATA</envar> is recommended 
    to avoid confusion 
    and conflict with other variables.
   </Para>

   <Note>
    <Para>
     In previous versions of <ProductName>Postgres</ProductName>, 
     it was also permissible to use an absolute path name to specify 
     an alternate storage location.  
     Although the environment variable style of specification
     is to be preferred since it allows the site administrator more flexibility in
     managing disk storage, it is also possible to use an absolute path 
     to specify an alternate location.  
     The administrator's guide discusses how to enable this feature.
    </Para>
   </Note>

   <Para>
    For security and integrity reasons,
    any path or environment variable specified has some
    additional path fields appended.
    Alternate database locations must be prepared by running 
    <Application>initlocation</Application>.
   </Para>

   <Para>
    To create a data storage area using the environment variable 
    <envar>PGDATA2</envar> (for this example set to <filename>/alt/postgres</filename>), 
    ensure that <FileName>/alt/postgres</FileName> already exists and is writable by 
    the Postgres administrator account.
    Then, from the command line, type
<ProgramListing>
% initlocation PGDATA2
The location will be initialized with username "postgres".
This user will own all the files and must also own the server process.

Creating directory /alt/postgres/data
Creating directory /alt/postgres/data/base

initlocation is complete.
You can now create a database using
   CREATE DATABASE &lt;name&gt; WITH LOCATION = 'PGDATA2'
in SQL, or
   createdb &lt;name&gt; -D 'PGDATA2'
from the shell.
</ProgramListing>
   </Para>

   <Para>
    To create a database in the alternate storage area <envar>PGDATA2</envar>
    from the command line, use the following command: 
<ProgramListing>
% createdb -D PGDATA2 mydb
</ProgramListing>

    and to do the same from within <Application>psql</Application> type
<ProgramListing>
=> CREATE DATABASE mydb WITH LOCATION = 'PGDATA2';
</ProgramListing>
   </Para>

   <Para>
    If you do not have the privileges required to create a database, you will see
    the following:
<ProgramListing>
ERROR:  CREATE DATABASE: permission denied
</ProgramListing>
   </Para>

   <Para>
    If the specified location does not exist or the database backend does not have
    permission to access it or to write to directories under it, you will see
    the following:
<ProgramListing>
ERROR:  The database path '/no/where' is invalid. This may be due to a character that is not allowed or because the chosen path isn't permitted for databases. 
</ProgramListing>
   </Para>

  </Sect1>

<Sect1 id="db-accessing">
<Title>Accessing a Database</Title>

<Para>
     Once you have constructed a database, you can access it
     by:

<ItemizedList Mark="bullet" Spacing="compact">
<ListItem>
<Para>
running the <ProductName>PostgreSQL</ProductName>  interactive terminal
<Application>psql</Application> which allows you to interactively
enter, edit, and execute <Acronym>SQL</Acronym> commands.
</Para>
</ListItem>
<ListItem>
<Para>
      writing a  C  program  using  the  <application>LIBPQ</application> subroutine
        library.   This  allows  you  to submit <Acronym>SQL</Acronym> commands
        from C and get answers and status messages  back  to
        your  program.   This interface is discussed further
        in <citetitle>The PostgreSQL Programmer's Guide</citetitle>.
</Para>
</ListItem>
</ItemizedList>

You might want to start up <Application>psql</Application>, 
to try out the examples in this manual.
 It can be activated for the <Database>mydb</Database>
     database by typing the command:
<ProgramListing>
% psql mydb
</ProgramListing>

     You will be greeted with the following message:
<ProgramListing>
Welcome to psql, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

mydb=>
</ProgramListing>
</Para>

<Para>
This prompt indicates that <command>psql</command> is listening
  to you and that you can type <Acronym>SQL</Acronym> queries into a
     workspace maintained by the terminal monitor.
     The <Application>psql</Application> program responds to escape  codes  that  begin
     with  the  backslash  character, <literal>\</literal>.  For example, you
     can get help on the syntax of various
 <ProductName>PostgreSQL</ProductName> <Acronym>SQL</Acronym> commands by typing:
<ProgramListing>
mydb=> \h
</ProgramListing>

     Once  you  have finished entering your queries into the
     workspace, you can pass the contents of  the  workspace
     to the <ProductName>Postgres</ProductName> server by typing:
<ProgramListing>
mydb=> \g
</ProgramListing>

     This  tells  the  server  to process the query.  If you
     terminate your query with a semicolon, the  <literal>\g</literal> is  not
     necessary.   
<Application>psql</Application> will automatically process semicolon terminated queries.
     To read queries from a file,  say  <filename>myFile</filename>,  instead  of
     entering them interactively, type:
<ProgramListing>
mydb=> \i fileName
</ProgramListing>

     To get out of <Application>psql</Application> and return to Unix, type
<ProgramListing>
mydb=> \q
</ProgramListing>

     and  <Application>psql</Application>  will  quit  and  return  you to your command
     shell. (For more escape codes, type <Command>\?</Command> at  the  <command>psql</command>
     prompt.)
     White  space  (i.e.,  spaces, tabs and newlines) may be
     used freely in <Acronym>SQL</Acronym> queries.  Single-line comments  are  denoted  by
     <literal>--</literal>.   Everything  after the dashes up to the end of the
     line is ignored. Multiple-line comments, and comments within a line,
     are denoted by <literal>/* ... */</literal>.
</Para>

</Sect1>
     
<Sect1 id="db-destroy">
<Title>Destroying a Database</Title>

<Para>
     If you are the owner of the  database
     <Database>mydb</Database>,  you can destroy it using the following Unix command:
<ProgramListing>
% dropdb mydb
</ProgramListing>
     This action physically removes all of the Unix files
     associated  with  the database and cannot be undone, so
     this should only be done with a great deal of forethought.
</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:
-->
