For hw1, you will use a shared installation of PostgreSQL (7.3)
binaries. The installation is compiled for SUN's Solaris architectures
and located at /software/postgresql-7.3.2-0/pkg/.
The first step is to initialize a 'database cluster', which is where
PostgreSQL will store all your data files. You can specify the
directory of the cluster at the command using the -D path option. In
your home directory
Type the command: initdb -D pgdata-p port_num
option. Pick up a unique port number, not being used by anyone else,
i.e., try a value and if it doesn't work try another. See below how to
start the postgres server.
PostgreSQL comes with a utility called pg_ctl which
allows for the safe starting and stopping of the server.
Type the command: /usr/local/bin/pg_ctl start -D pgdata -l
logfile -o "-N 4 -p XXXX"
The option -D specifies the data directory, -l gives the logfile
name, and -o passes the quoted text to the server, which
in this case the -N option tells the server to have at
most 4 connections, and -p XXXX gives the port number. YOU MUST CHOOSE
a value for XXXX (e.g., 5000). Keep in mind that disticnt port numbers
must be used by
each student (so if your command fails, try another port number).
To check if everything started fine.
Type the command: ps ae
If you see a process with the name postmaster then you are all set. If not you must try a different port number.
Type the command: createdb -p XXXX test
dropdb -p XXXX test
psql which enables
interactive processing of SQL commands.
Type the command: psql -p XXXX test
Notice that the parameter "test" to psql is the name of database on
the server. Once psql is started you can enter any SQL command.
Commands can be multi-line and MUST have a semicolon at the end. psql
has its own prompt, which includes the database name and either a
equals sign or hyphen followed by a pound sign. The hyphen tells you
that a partial SQL statement has been entered. For example: (the
following is just to show what psql looks like when you type SQL
command, there is no table named student unless you create it)
User=# SELECT firstname, lastname
User-# FROM students;
Notice that after the first line, the prompt changed. This is particularly helpful when you enter a multi-line statement. PostgreSQL will compute the entire answer BEFORE it shows any results. When running long queries, it may appear as though the server has frozen, but it may just be working. psql stores that last query you ran in a buffer. You can access the buffer and modify the query. This is especially helpful if it is a long command and you made a mistake. By default EMACS is used as the editor, however you can change this by setting the EDITOR environmental variable. To access the buffer:
NOTE:In your SQL queries, you need to put table names (or attribute names) in double-quotes if they include capital letters. Otherwise you'll get an error from PostgreSQL that it doesn't recognize the table (attribute).User=# \e
Once you finish editing the buffer, exit and SAVE. psql will automatically run the query. Typing SQL directly into psql can be error-prone and leaves no log of the commands you ran. You may want to put a number of SQL commands into a file and then have psql run each of those commands. The file can contain multi-lined statements along with as much white space as you want. Once the file is ready, from within psql you can execute the file: for example:
User=# \i schema.sql
When you are finished and want to exit psql:
User=# \q
psql has a number of useful features, read the documentation for psql at: http://www.postgresql.org/docs/. We highly recommend at least skimming the online documentation.
PostgreSQL maintains its own database of users. By default the only
user created is the same as the UNIX login of the person who ran the initdb
command (i.e. your account). This account has all permissions. The
account is not created with a password, so you can assign one. At the
psql prompt type:
ALTER USER userid WITH PASSWORD 'password';
pg_ctl -D pgdata -p XXXX stop
Above are basic steps you usually need to set up your database server.