This section is currently a thinly disguised copy of the Tutorial. Needs to be augmented. - thomas 1998-01-12 Although the site administrator is responsible for overall management of the Postgres installation, some databases within the installation may be managed by another person, designated the database administrator. 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 Postgres, but will not by default have the same operating system privileges as the site administrator. The Database Administrator's Guide covers these topics in more detail. Databases are created by the create database issued from within Postgres. createdb is a command-line utility provided to give the same functionality from outside Postgres. The Postgres backend must be running for either method to succeed, and the user issuing the command must be the Postgres superuser or have been assigned database creation privileges by the superuser. To create a new database named mydb from the command line, type % createdb mydb and to do the same from within psql type * CREATE DATABASE mydb; If you do not have the privileges required to create a database, you will see the following: % createdb mydb WARN:user "your username" is not allowed to create/destroy databases createdb: database creation failed on mydb. Postgres 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. 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. Either an absolute path name or an environment variable may be specified as a location. Any environment variable specifying an alternate location must have been defined before the backend was started. Consult with the site administrator regarding preconfigured alternate database locations. The environment variable style of specification is to be preferred since it allows the site administrator more flexibility in managing disk storage. 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 initlocation. To create a data storage area in /alt/postgres/data, ensure that /alt/postgres already exists. From the command line, type % initlocation /alt/postgres/data Creating Postgres database system directory /alt/postgres/data Creating Postgres database system directory /alt/postgres/data/base To do the same using an environment variable PGDATA2, type % initlocation $PGDATA2 Creating Postgres database system directory /alt/postgres/data Creating Postgres database system directory /alt/postgres/data/base To create a database in the alternate storage area /alt/postgres/data from the command line, type % createdb -D /alt/postgres/data mydb or % createdb -D PGDATA2 mydb and to do the same from within psql type * CREATE DATABASE mydb WITH LOCATION = 'PGDATA2'; If you do not have the privileges required to create a database, you will see the following: % createdb mydb WARN:user "your username" is not allowed to create/destroy databases createdb: database creation failed on mydb. 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: % createdb -D /alt/postgres/data mydb ERROR: Unable to create database directory /alt/postgres/data/base/mydb createdb: database creation failed on mydb. Once you have constructed a database, you can access it by: running the Postgres terminal monitor programs (e.g. psql) which allows you to interactively enter, edit, and execute SQL commands. writing a C program using the LIBPQ subroutine library. This allows you to submit SQL commands from C and get answers and status messages back to your program. This interface is discussed further in section ??. You might want to start up psql, to try out the examples in this manual. It can be activated for the mydb database by typing the command: % psql mydb You will be greeted with the following message: Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: template1 mydb=> This prompt indicates that the terminal monitor is listening to you and that you can type SQL queries into a workspace maintained by the terminal monitor. The psql program responds to escape codes that begin with the backslash character, \ For example, you can get help on the syntax of various Postgres SQL commands by typing: mydb=> \h Once you have finished entering your queries into the workspace, you can pass the contents of the workspace to the Postgres server by typing: mydb=> \g This tells the server to process the query. If you terminate your query with a semicolon, the \g is not necessary. psql will automatically process semicolon terminated queries. To read queries from a file, say myFile, instead of entering them interactively, type: mydb=> \i fileName To get out of psql and return to UNIX, type mydb=> \q and psql will quit and return you to your command shell. (For more escape codes, type \h at the monitor prompt.) White space (i.e., spaces, tabs and newlines) may be used freely in SQL queries. Single-line comments are denoted by --. Everything after the dashes up to the end of the line is ignored. Multiple-line comments, and comments within a line, are denoted by /* ... */ TBD If you are the database administrator for the database mydb, you can destroy it using the following UNIX command: % destroydb mydb 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.