PostgreSQL Setup

Finding documentation

• http://www.postgresql.org/docs/current/interactive/index.html

Setting up the database server

    $ dpkg -l "postgresql*"    
       ... (Find the exact names of latest packages)...
    $ sudo apt-get install postgresql-8.2 postgresql-client-8.2

In /etc/postgresql/8.2/main, modify postgresql.conf to listen for TCP requests on all interfaces,

    listen_addresses = '*'
    port = 5432

Add the following line to pg_hba.conf to accept MD5 hashes of passwords over the network from UW machines,

    host    all         all         129.97.0.0/16           md5

Administration Hints

A few hints on using pg_ctl to control the database. These are just informal notes after a frustrating session trying to figure out how to get the database to reload after editing the pg_hba.conf.

To make the database reload its configuration files:

sudo -u postgres /usr/lib/postgresql/8.2/bin/pg_ctl reload -D pgdata

Note however that this seems to be capable of crashing the process if it is unable to read crucial config files such as pg_hba.conf upon reloading. Thus leading me to investigate the next point:

To start the database when it is down:

sudo -u postgres /usr/lib/postgresql/8.2/bin/pg_ctl start -D pgdata -o '--config-file=postgresql.conf'

I'm not absolutely sure that this gives equivalent results to rebooting the machine, but it seems to work properly when run from /etc/postgresql/8.2/users (or /apps as appropriate). The tricky bit is that our postgresql.conf is not in the actual data directory.

See also

• GradAdmissionSetup for setting up a database.

-- IlguizLatypov - 13 Aug 2007