+
3. GETTING STARTED WITH POSTGRES
++ This section discusses how to start POSTGRES and set up + your own environment so that you can use frontend + applications. We assume POSTGRES has already been + successfully installed. (Refer to the installation notes + for how to install POSTGRES.) +
+ Some of the steps listed in this section will apply to + all POSTGRES users, and some will apply primarily to + the site database administrator. This site administrator + is the person who installed the software, created + the database directories and started the postmaster + process. This person does not have to be the UNIX + superuser, "root," or the computer system administrator. + In this section, items for end users are labelled + "User" and items intended for the site administrator + are labelled "Admin." + Throughout this manual, any examples that begin with + the character ``%'' are commands that should be typed + at the UNIX shell prompt. Examples that begin with the + character ``*'' are commands in the POSTGRES query + language, POSTGRES SQL. + +
3.1. Admin/User: Setting Up Your Environment
+
+ Figure 2. shows how the POSTGRES distribution is laid
+ out when installed in the default way. For simplicity,
+ we will assume that POSTGRES has been installed in the
+ directory /usr/local/postgres95. Therefore, wherever
+ you see the directory /usr/local/postgres95 you should
+ substitute the name of the directory where POSTGRES is
+ actually installed.
+ All POSTGRES commands are installed in the directory
+ /usr/local/postgres95/bin. Therefore, you should add
+ this directory to your shell command path. If you use
+ a variant of the Berkeley C shell, such as csh or tcsh,
+ you would add
+% set path = ( /usr/local/postgres95/bin $path ) ++ in the .login file in your home directory. If you use + a variant of the Bourne shell, such as sh, ksh, or + bash, then you would add +
+ % PATH=/usr/local/postgres95/bin:$PATH + % export PATH ++ to the .profile file in your home directory. + From now on, we will assume that you have added the + POSTGRES bin directory to your path. In addition, we + will make frequent reference to "setting a shell + variable" or "setting an environment variable" throughout + this document. If you did not fully understand the + last paragraph on modifying your search path, you + should consult the UNIX manual pages that describe your + shell before going any further. + +
3.2. Admin: Starting the Postmaster
+ It should be clear from the preceding discussion that + nothing can happen to a database unless the postmaster + process is running. As the site administrator, there + are a number of things you should remember before + starting the postmaster. These are discussed in the + section of this manual titled, "Administering POSTGRES." + However, if POSTGRES has been installed by following + the installation instructions exactly as written, the + following simple command is all you should + need to start the postmaster: +% postmaster & ++ The postmaster occasionally prints out messages which + are often helpful during troubleshooting. If you wish + to view debugging messages from the postmaster, you can + start it with the -d option and redirect the output to + the log file: +
% postmaster -d >& pm.log & ++ If you do not wish to see these messages, you can type +
% postmaster -S ++ and the postmaster will be "S"ilent. Notice that there + is no ampersand ("&") at the end of the last example. + +
3.3. Admin: Adding and Deleting Users
+ The createuser command enables specific users to access + POSTGRES. The destroyuser command removes users and + prevents them from accessing POSTGRES. Note that these + commands only affect users with respect to POSTGRES; + they have no effect administration of users that the + operating system manages. + +3.4. User: Starting Applications
+ Assuming that your site administrator has properly + started the postmaster process and authorized you to + use the database, you (as a user) may begin to start up + applications. As previously mentioned, you should add + /usr/local/postgres95/bin to your shell search path. + In most cases, this is all you should have to do in + terms of preparation.1 + If you get the following error message from a POSTGRES + command (such as psql or createdb): +connectDB() failed: Is the postmaster running at 'localhost' on port '4322'? ++ it is usually because (1) the postmaster is not running, or (2) you are attempting to connect to the wrong + server host. + If you get the following error message: +
FATAL 1:Feb 17 23:19:55:process userid (2360) != + database owner (268) ++ it means that the site administrator started the postmaster as the wrong user. Tell him to restart it as + the POSTGRES superuser. + +
3.5. User: Managing a Database
+ Now that POSTGRES is up and running we can create some + databases to experiment with. Here, we describe the + basic commands for managing a database. + +3.5.1. Creating a Database
+ Let's say you want to create a database named mydb. + You can do this with the following command: +% createdb 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 16 characters in length. + Not every user has authorization to become a database + administrator. If POSTGRES refuses to create databases + for you, then the site administrator needs to grant you + permission to create databases. Consult your site + administrator if this occurs. + +
3.5.2. Accessing a Database
+ Once you have constructed a database, you can access it + by: +-
+
- running the POSTGRES terminal monitor programs ( + monitor or 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 ??. +
% psql mydb ++ You will be greeted with the following message: +
Welcome to the POSTGRES95 interactive sql monitor: + + 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: mydb + + 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. Comments are denoted by + --. Everything after the dashes up to the end of the + line is ignored. + +
3.5.3. 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 fore-thought. + +
+
+ +1. If your site administrator has not set things up in the +default way, you may have some more work to do. For example, if the database server machine is a remote machine, you +will need to set the PGHOST environment variable to the name +of the database server machine. The environment variable +PGPORT may also have to be set. The bottom line is this: if +you try to start an application program and it complains +that it cannot connect to the postmaster, you should immediately consult your site administrator to make sure that your +environment is properly set up. + +
+ +[ TOC ] +[ Previous ] +[ Next ] + +