This is a template for using a postgresql database on a WESTGRID cluster

wetsgrid-psql.sh

#!/bin/bash
#PBS -S /bin/bash
##PBS -l procs=1
#PBS -l nodes=1:ppn=1
#PBS -l walltime=48:00:00
#PBS -m bae
#PBS -M your@email.com
#PBS -l mem=6gb
#PBS -l file=5gb

# you or a wrapper script should fill in the details
I=XXXXXX
DB_USER=xxx
DB_PASSWD=zzz
DB_NAME=yyy # db created 
DUMP_DEST=/dir/to/dumps
PSQL_BIN=/dir/to/psql/bin
LOCAL_DB_DIR=$TMPDIR/dir



##########
#
# database set-up
#
##########

# init db
mkdir -p $LOCAL_DB_DIR
$PSQL_BIN/initdb --encoding=UTF8 --locale=en_US.UTF8 -D $LOCAL_DB_DIR

# start db
# and trying to find a usable port
DB_PORT=5431
while [ `netstat -an | grep tcp | awk '{if ($6 == "LISTEN") print $4}' | awk -F: '{print $2}' | grep $DB_PORT | wc -l` -lt 1 ]
do
DB_PORT=$(( $DB_PORT + 1 ))
done

$PSQL_BIN/postgres -D $LOCAL_DB_DIR --checkpoint_completion_target=0.9 --checkpoint_segments=256 --checkpoint_timeout=300 --autovacuum=TRUE --fsync=FALSE -p $DB_PORT&

# wait until postgres is started
while [ `psql -p $DB_PORT -l | wc -c` -lt 1 ]
do
sleep 1
done

$PSQL_BIN/createuser -p $DB_PORT -s -d -r -l $DB_USER
$PSQL_BIN/psql -p $DB_PORT -c "ALTER USER $DB_USER WITH PASSWORD '$DB_PASSWD'" postgres
$PSQL_BIN/createdb -p $DB_PORT $DB_NAME

############
#
# you should do your stuff here
#
# $DB_PORT is the port the database runs on
#
############
############

############
#
# After run clean up
#
############

# dump db
$PSQL_BIN/pg_dump -p $DB_PORT --no-owner --file=$DB_NAME.dump $DB_NAME

# stop db
$PSQL_BIN/pg_ctl -D $LOCAL_DB_DIR stop

# copy db to home
mv -f $DB_NAME.dump $DUMP_DEST

# cleanup
rm -rf $TMPDIR