manage.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/manage.sgml,v 1.21 2002/01/20 22:19:56 petere 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>PostgreSQL</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 tasks
   within <ProductName>PostgreSQL</ProductName>, but will
   not by default have the same operating system privileges as the site administrator.
  </Para>

  <Para>
   The <citetitle>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>
    command issued from within
    <ProductName>PostgreSQL</ProductName>. <Application>createdb</Application>
    is a shell script provided to give the same functionality from the
    Unix command line.
   </Para>

   <Para>
    The <ProductName>PostgreSQL</ProductName> backend must be running for either method
    to succeed, and the user issuing the command must be the <ProductName>PostgreSQL</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>
    You  automatically  become  the
    database  administrator  of  the database you just created.  
    Database names must  have  an  alphabetic  first
    character and are limited to 31 characters in length.
    <ProductName>PostgreSQL</ProductName>  allows  you to create any number of
    databases at a  given  site.  
   </Para>

  <Para>
   The <citetitle>Administrator's Guide</> discusses database creation
   in more detail, including advanced options of the <command>CREATE
   DATABASE</> command.
  </Para>

  </Sect1>

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

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

    <itemizedlist spacing="compact" mark="bullet">
     <listitem>
      <para>
       Running the <productname>PostgreSQL</productname> interactive
       terminal program, called <application>psql</application>, which allows you
       to interactively enter, edit, and execute
       <acronym>SQL</acronym> commands.
      </para>
     </listitem>

     <listitem>
      <para>
       Using an existing graphical frontend tool like
       <application>PgAccess</application> or
       <application>ApplixWare</application> (via
       <acronym>ODBC</acronym>) to create and manipulate a database.
       These possibilities are not covered in this tutorial.
      </para>
     </listitem>

     <listitem>
      <para>
       Writing a custom application, using one of the several
       available language bindings.  These possibilities are discussed
       further in <citetitle>The PostgreSQL Programmer's
       Guide</citetitle>.
      </para>
     </listitem>
    </itemizedlist>

You probably 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
     work space maintained by the terminal monitor.
The <Application>psql</Application> program itself responds to special
commands  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
     work space, you can pass the contents of  the  work space
     to the <ProductName>PostgreSQL</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 myFile
</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 SQL command
<ProgramListing>
=> DROP DATABASE mydb;
</ProgramListing>
     or the Unix shell script
<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:
-->