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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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