These notes cover the common case of connecting to db.doc.ic.ac.uk, our general purpose and teaching database. Other database servers exist (most obviously, db-research.doc.ic.ac.uk)
The PostgreSQL website has a variety of documentation including a book called PostgreSQL: Introduction and Concepts.
Connecting from offsite
Connecting to db.doc.ic.ac.uk from offsite should work fine, as long as you ensure that the connection uses SSL. Please see the Postgres offsite connection guide for more details.
DoC Teaching Postgres Setup
Our teaching Postgres database server runs on a machine called db.doc.ic.ac.uk on port 5432. It provides access by all DoC users, and additional Postgres specific usernames, to each of many databases -- each one a set of tables etc. Within our teaching Postgres database server, there are three categories of database:
- There are a few shared databases used for teaching - for example, the standard films example database much used in lab exercises, and some databases associated with a particular lecture course.
- All DoC users have been allocated their own Postgres database, username and password. The Postgres database name and Postgres username are the same as your DoC username, and details of the Postgres password were emailed out to each new user when they join DoC. Note that the Postgres password is completely separate from DoC Unix (Kerberos) and Windows passwords. If you have lost or forgotten the details please contact the help desk. If you remember your password but have forgotten the notes that were in the email on how to use the system there is a sample copy of the email here. Inside PSQL, you can change your password with the ALTER USER command. Within your existing database you may create multiple schemas which are very similar to separate databases in most respects.
- Finally, when it comes to group projects, the Lab Organiser can decide whether or not all groups in the class are likely to need per-group databases. If they ask us to do so, we will bulk create a database username and database per group, as part of the process of creating the group project directories and groups. Usually, group project Postgres databases and usernames have the same name - comprising the full Unix group name with "_u" appended (for example, g206002107_u now we're using new Banner codes like 60021). We'd prefer not to create individual group project databases and users for individual groups, as through the wonders of automation that is as much work as for the whole class.
Client-side Authentication
Postgres has it's own user account system with separate passwords. As we said in (2) above, here at DoC your database name and Postgres username are usually the same name as your DoC username.
The Postgres clients (psql, pg_dump etc) can either ask for some/all of these settings when you run them, or take them from a set of environment variables and a data file. For convenience, we strongly recommend that you set these up once and for all by creating two files - first, ~/.pg with the following contents:
# Use this set of commands if your shell is bash (most users).
export PGHOST=db.doc.ic.ac.uk
export PGPORT=5432
export PGUSER={your_user_name}
export PGDATABASE={your_database_name}
unset PGPASSWORD
# Use this set of commands if your shell is (t)csh.
setenv PGHOST db.doc.ic.ac.uk
setenv PGPORT 5432
setenv PGUSER {your_user_name}
setenv PGDATABASE {your_database_name}
unsetenv PGPASSWORD
NOTE: Please ensure that there is a NEWLINE at the end of the last character of the last line ("D" of "PGPASSWORD") - some editors (pico!) don't bother to write this. Result: when you later . ~/.pg (bash) or source ~/.pg (tcsh) the LAST INCOMPLETE line is IGNORED.
Second, create ~/.pgpass with the contents (or, append this line to your ~/.pgpass if it already exists):
db:*:*:{your_user_name}:{your_postgres_password}
Note: or course, you must replace the {your_user_name}, {your_database_name} and {your_postgres_password} with your postgres username, database name and password! Assuming your database name and username are the same as your Unix username, you could write this as:
# bash export PGUSER=$user export PGDATABASE=$user
# (t)csh setenv PGUSER $user setenv PGDATABASE $user
Then you must ensure that your ~/.pgpass file is not accessible by others - to do this, simply type the following Unix command:
chmod go= ~/.pgpass
Note: Postgres used to have an environment variable called PGPASSWORD, this still works but is now deprecated. You will notice that the ~/.pg shown above includes unsetenv PGPASSWORD to make sure that a spurious PGPASSWORD setting doesn't override your ~/.pgpass password. A particular circumstance where this often occurs is when, in order to make Postgres-based labs easier, the Lab Organisers set up some default Postgres settings for you - usually:
# bash export PGUSER=lab export PGDATABASE=films export PGPASSWORD=lab
# (t)csh setenv PGUSER lab setenv PGDATABASE films setenv PGPASSWORD lab
This is perfectly sensible, but when you come to use your own database you must make sure you are not using a mixture of these settings and the ones shown above. So make sure unset PGPASSWORD (bash) or unsetenv PGPASSWORD (csh) is included in the ~/.pg file as shown above (NOTE: with a trailing newline!) and it'll work fine.
Once you've set up these files, you should . ~/.pg (bash) or source ~/.pg (tcsh) to start using the database. If you want this to happen automatically on login, add . ~/.pg' right at the end of your ~/.bashrc file or source ~/.pg right at the end of your ~/.cshrc file. This will then override the films/lab/lab settings completely - so of course you would have to change them back to do some lab exercises.
Having sourced the ~/.pg file and created the ~/.pgpass file, you can just run the Postgres interactive SQL program psql (with no arguments) to access your database, or psql database to access another database. If you have not provided Postgres with your Postgres password by any means, you will be prompted for a password. This usually means your setup files are broken - so fix them and try again.
Postgres Access from Programs
Suppose you want to write programs which access Postgres, in any language you like (eg. Perl, Java, Ruby, PHP) that supports Postgres database access. All these languages provide some kind of Postgres library or drive. Each language provides some kind of connect to database function, which typically takes as arguments all five of the postgres settings discussed above - which server, which port (sometimes this can be omitted and defaults to 5432), which database on the server, which database user and which database server. Suppose we want to connect to the readonly films database as user lab, password lab:
- Perl DBI:
use DBI;
my $username = my $password = "lab";
$dbh = DBI->connect("dbi:Pg:dbname=lab;host=db;port=5432", $username, $password);
die "couldn't connect to films\n" unless $dbh;
- PHP:
$link=pg_connect("host=db port=5432 user=lab password=lab dbname=films" );
if( ! $link )
{
die("couldn't connect to films");
}
-
Java JDBC: We have written a whole Guide section on Java JDBC, covering access both to Postgres and Microsoft SQL server. See it for a nice worked example.
- Ruby: We believe something like the following should work:
require 'postgres'
db = PGconn.connect('db',5432,nil,nil,"films","lab","lab")
[The last two parameters are the username and password]
