Skip to content

Using PostGreSQL databases with WESTGRID

03/01/2013
During my phd I used westgrid clusters to do some of my computations. Often I needed to interact with a database. It took me a while to get the whole thing working so I thought to share the script with you. Some nice features are that it is trying to figure out an open port that you can run the database on. You never know if something else is running on the port on the node you got assigned already.


#!/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

Have fun with it.

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: