<!-- $Header: /cvsroot/pgsql/doc/src/sgml/Attic/manage.sgml,v 1.18 2001/11/18 00:38:00 tgl 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 tasks within <ProductName>Postgres</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>Postgres</ProductName>. <Application>createdb</Application> is a shell script provided to give the same functionality from the Unix command line. </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> 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>Postgres</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 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> <ListItem> <Para> writing a program in other languages for which there are available interface libraries. </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 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 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 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: -->