Using PostgreSQL at IMADA

In DM26, we will be using the relational database system PostgreSQL. This note explains how to connect to the system at IMADA.

dbhost

The PostgreSQL system resides on the machine dbhost, and you first need to log in to that (using your normal IMADA password):

ssh dbhost

This assumes you are already logged into some other IMADA machine (which itself can be done using ssh from e.g. a machine at home (dbhost itself does not seem to accept ssh connections from non-IMADA machines)).

If you are using ssh for the first time between the two machines in question, you will be asked whether you want to continue the ssh connection (answer "yes"), and you will receive a warning about addition to a list of known hosts (ignore the warning).

The PostgreSQL System

The system is composed of a database server part (the program postmaster), and a client part (the program psql). You will only be using psql.

The PostgreSQL system has it own set of usernames and passwords, which are unrelated to the usernames and passwords of IMADA accounts. For all participants with an existing IMADA account, an PostgreSQL account has been created, with the same username as their IMADA account, and with a password which has been mailed to them. If you do not yet have an IMADA account, contact staff in order to get one, and then contact the lecturer for PostgreSQL account creation. If you have an account, but are unable to connect, contact the lecturer.

Creating Databases

Your first action should be to create a database. Each database is a collection (empty at the beginning) of relations. You can create a new database with the name dbname using the command

createdb dbname

If the command createdb is reported as not found, you will need to set your PATH variable - see below. In the createdb command, if dbname is omitted, the name will default to your IMADA username.

Databases can be destroyed again using the command

dropdb dbname

Again, you may need to set your PATH variable - see below.

Using Databases

When using PostgreSQL, you connect to one database at a time. Your start a connection to a database by

psql dbname

This will put you in interactive mode, which allows you to define relations, insert tuples, and make queries by issuing SQL commands at the prompt.

Beside SQL commands, psql has a set of so-called "meta commands". Among these are

\d : Show a list all relations in the current database.
\q : Quit psql.
\i filename : Read SQL commands from filename and execute them.
\o filename : Write output to filename.

Alternatively, input and output files can be specified when starting psql:

psql -f infile -o outfile

Setting your PATH variable

If your shell is not able to find the PostgreSQL commands createdb, dropdb, and psql, you will need to update your PATH variable. The method depends on the type of shell you are using (which you can find out by the command echo $SHELL).

If your are using tcsh, add the line

setenv PATH ${PATH}:/usr/local/postgresql/bin

to the file .tcshrc in your home directory (creating the file if it does not exist).

If your are using bash, add the line

export PATH=$PATH:/usr/local/postgresql/bin

to the file .bashrc in your home directory (creating the file if it does not exist).

Further Information

Here is a list of easy SQL examples (ps, pdf).

PostgreSQL comes with ample documentation. See for instance the SQL description and the refence section (including the man page of psql).