From c8cfb0cea88fec22f5aa0582fe846b46baf77eb1 Mon Sep 17 00:00:00 2001 From: Thomas G. Lockhart Date: Sun, 1 Mar 1998 08:16:16 +0000 Subject: SGML source for new documentation. --- doc/src/sgml/manage.sgml | 294 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 294 insertions(+) create mode 100644 doc/src/sgml/manage.sgml (limited to 'doc/src/sgml/manage.sgml') diff --git a/doc/src/sgml/manage.sgml b/doc/src/sgml/manage.sgml new file mode 100644 index 00000000000..332dc2459da --- /dev/null +++ b/doc/src/sgml/manage.sgml @@ -0,0 +1,294 @@ + +Managing a Database + + + + +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. + + + +Database Creation + + +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. + + + + + +Alternate Database Locations + + +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. + + + + + +Accessing a Database + + + 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 /* ... */ + + + +Database Privileges + + + + + +Table Privileges + + +TBD + + + + + + + +Destroying a Database + + + 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. + + + + -- cgit v1.2.3