Documentation

The PostgreSQL website has a variety of documentation including a book called PostgreSQL: Introduction and Concepts.

See also /usr/share/doc/postgresql-docs-7.3.4/index.html

DoC Teaching Postgres Setup

Our teaching Postgres database server runs on a machine called db 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:

1. 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.
2. 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 everyone in October 2003, and are mailed to all subsequent new user accounts when they are created. 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. It is possible for us to set you up additional databases, but we would encourage you to use the single database that we have already created for you, as it simplifies the "who owns what" logic. Within your existing database you may create multiple schemas which are very similar to separate databases in most respects. If you think you absolutely must have separate databases, you are welcome to email help@doc.ic.ac.uk giving reasons why a schema is not sufficient and suggesting a database name of the form username_suffix.
3. Finally, group project students may request group project databases and usernames (indeed, if the Lab Organiser requests it, we will bulk create all of these when creating the group project directories and groups). Usually, group project Postgres databases and usernames will have the same name as the full Unix group name (for example, g03v36207).

Client-side Authentication

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:

setenv PGHOST db
setenv PGPORT 5432
setenv PGUSER {your_user_name}
setenv PGDATABASE {your_database_name}
unsetenv PGPASSWORD

Second, create ~/.pgpass with the contents:

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:

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:

setenv PGUSER lab
setenv PGDATABASE films
setenv PGPASSWORD lab

Once you've set up these files, you should source ~/.pg to start using the database. If you want this to happen automatically on login, add source ~/.pg to 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 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

Access Methods

• Perl DBI:

  • use DBI;
    $dbh = DBI->connect("dbi:Pg:dbname=$database;host=db;port=5432", $username, $password);

• JDBC
  • We have written a whole Q&A section on Java JDC, 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,database,user,password)
    	  

More information

© CSG / 2004