This document covers the use of the PostgreSQL database server running on igor.gold.ac.uk, the Department of Computing Linux server. The PostgreSQL server is available for use by all students in the Department of Computing.

Your PostgreSQL Account

All users of the PostgreSQL service must have an account created for them on the PostgreSQL server, which runs on the Department of Computing's Linux server - igor. Students on certain courses in the Department of Computing may have an account created for them automatically. If you find you are unable to connect to the PostgreSQL server after following the instructions below, you may need to request an account by contacting the departmental systems support team.

Connecting to PostgreSQL

The standard method of interacting with the PostgreSQL database system is to connect to igor.gold.ac.uk using the Windows Secure Shell Client (or a similar ssh client) and run the psql command-line utility. You can use psql to create and update your databases and to select data from your database tables using SQL queries. You can see the online help for the psql utility by typing man psql at the command-line prompt on igor.

The following shows you would connect to a database called mydatabase on the PostgreSQL server using psql on igor:

    psql mydatabase

The tables in each database that you create are separate from each other and you can only execute SQL query and update commands on the database that you are currently connected to.

You can also connect to the PostgreSQL server from your Java programs on igor. To do this, your program will need to load the PostgreSQL JDBC driver package, which is done by the following method call:

    Class.forName("org.postgresql.Driver");

You will also need to specify the URL for the connection, which will be something like this:

    jdbc:postgresql://igor.gold.ac.uk/mydatabase

To connect directly to the Postgresql server on igor.gold.ac.uk via the internet (ie. from a computer not directly connected to the Goldsmiths network), you should configure your Postgresql client program to use port 2345 instead of the default Postgresql port 5432.

For example, to use the psql command included with a Postgresql installation on your own computer to connect to a database on igor called mydatabase using the username ma001abc:

    psql -h igor.gold.ac.uk -p 2345 -U ma001abc mydatabase

Creating Databases

Before you can connect to the PostgreSQL server, you first need to create a database to connect to. You can create as many databases as you wish and each database can contain any number of database tables.

You can create a database at the command-line on igor using the createdb command and specifying a name for the database. For example, to create a database called mydatabase, you would type:

    createdb mydatabase

You can use any one-word name for your database as long as it has not been used already by another user. Once you have created your database, you can connect to it using the psql command, as described above.

Further Information

For more general information about PostgreSQL, visit the PostgreSQL website.