Using PostGreSQL databases with WESTGRID

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

view raw

wetsgrid-psql.sh

hosted with ❤ by GitHub

Have fun with it.