# Mark Huang <mlhuang@cs.princeton.edu>
# Copyright (C) 2006 The Trustees of Princeton University
#
-# $Id: db,v 1.5 2007/01/30 11:29:36 thierry Exp $
+# $Id$
#
# Source function library and configuration
# Export so that we do not have to specify -p to psql invocations
export PGPORT=$PLC_DB_PORT
-### updates the subversion column in plc_db_version by applying
-### all migrations scripts in /usr/share/plc_api/migrations/nnn-up-*
-### between the current_subversion+1 and the last one
-### migration indices must be contiguous
-function migrate_db () {
-
- ### check for (or create) the subversion column in plc_db_version
- psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME > /dev/null
- [ "$?" = 0 ] || psql -U $PLC_DB_USER -c 'ALTER TABLE plc_db_version ADD subversion integer DEFAULT 0' $PLC_DB_NAME
-
- # get current subversion - OUCH, THIS IS UGLY
- current_subversion=$(psql -U $PLC_DB_USER -c 'select (subversion) from plc_db_version' $PLC_DB_NAME | \
- grep '^ *[0-9][0-9]*$')
- current_subversion=$(($current_subversion))
- upgrade_index=$current_subversion
-
- while true; do
- upgrade_index=$(printf "%03d" $(($upgrade_index+1)))
- upgrade_scripts=$(ls -1 /usr/share/plc_api/migrations/${upgrade_index}-up-* 2> /dev/null)
- if [ -z "$upgrade_scripts" ] ; then
- echo "DB current migration is $current_subversion"
- psql -U $PLC_DB_USER -c "UPDATE plc_db_version SET subversion=$current_subversion" $PLC_DB_NAME
- break
- else
- for script in $upgrade_scripts; do
- # is this an sql script
- if [ $(basename "$script") != $(basename $script .sql) ] ; then
- echo "Applying SQL migration script $script"
- psql -U $PLC_DB_USER -f "$script" $PLC_DB_NAME
- elif [ -x "$script" ] ; then
- echo "Running executable migration script $script"
- "$script"
- else
- echo "WARNING - script $script ignored"
- fi
- done
- current_subversion=$(($upgrade_index))
+# Updates the database by applying all migration scripts in
+# /usr/share/plc_api/migrations/N-up-*, where N is greater than the
+# current subversion. At least one of the migration scripts with the
+# same N must update plc_db_version.subversion.
+function migrate_db()
+{
+ subversion=$(psql -U $PLC_DB_USER --quiet --tuples-only --no-align -c \
+ "SELECT subversion FROM plc_db_version LIMIT 1" \
+ $PLC_DB_NAME 2>/dev/null || echo 0)
+ shopt -s nullglob
+ for file in /usr/share/plc_api/migrations/[0-9]*-up-* ; do
+ script=$(basename $file)
+ index=${script%-up*}
+ extension=${script##*.}
+ if [ $index -gt $subversion ] ; then
+ if [ "$extension" = "sql" ] ; then
+ dialog " - $script (dbdumped)"
+ dump_planetlab_db "before-$script"
+ psql -U $PLC_DB_USER -f $file $PLC_DB_NAME
+ elif [ -x $file ] ; then
+ dialog " - $script (dbdumped)"
+ dump_planetlab_db "before-$script"
+ $file
+ else
+ dialog "\nWarning: migration $file not executable"
+ fi
+ check
fi
done
}
-#### dumps the database for future restoration
-# uses /root/dumps since /root is mounted on /data/root
-function dump_db () {
+function checkpoint_planetlab_db()
+{
+ dumpfile=$1
+ pg_dump -U $PLC_DB_USER $PLC_DB_NAME > $dumpfile
+ check
+}
- dump=/root/dumps/$(date +'%Y-%m-%d-%H-%M.sql')
- mkdir -p $(dirname $dump)
- pg_dump --user=$PLC_DB_USER $PLC_DB_NAME > $dump
-
+function restore_planetlab_db()
+{
+ dumpfile=$1
+ if [ -n "$dumpfile" ] ; then
+ [ -f "$dumpfile" ] && psql -a -U $PLC_DB_USER $PLC_DB_NAME < $dumpfile
+ check
+ fi
}
-function clean_dumps () {
- find /root/dumps -name '[0-9]*.sql' -atime +15 | xargs rm
+# use a single date of this script invocation for the dump_*_db functions.
+DATE=$(date +"%Y-%m-%d-%H-%M-%S")
+
+# Dumps the database - optional argument to specify filename suffix
+function dump_planetlab_db()
+{
+ if [ -n "$1" ] ; then suffix="-$1" ; else suffix="" ; fi
+ dumpfile=/var/lib/pgsql/backups/$(date +"${PLC_DB_NAME}.${DATE}${suffix}.sql")
+ checkpoint_planetlab_db $dumpfile
+}
+
+function restore_drupal_db()
+{
+ dumpfile=$1
+ if [ -n "$dumpfile" ] ; then
+ [ -f "$dumpfile" ] && psql -a -U $PLC_DB_USER drupal < $1
+ check
+ fi
+}
+
+function checkpoint_drupal_db()
+{
+ dumpfile=$1
+ pg_dump -U $PLC_DB_USER drupal > $dumpfile
+ check
+}
+
+function dump_drupal_db()
+{
+ dumpfile=/var/lib/pgsql/backups/$(date +"drupal.${DATE}.sql")
+ checkpoint_drupal_db $dumpfile
+ check
+}
+
+# Clean up old backups
+function clean_dumps()
+{
+ find /var/lib/pgsql/backups '(' -name "$PLC_DB_NAME.*.sql" -o -name "drupal.*.sql" ')' -a -atime +15 | xargs rm -f
+ check
}
-##########
if [ "$PLC_DB_ENABLED" != "1" ] ; then
- exit 0
+ exit 0
fi
case "$1" in
start)
-
MESSAGE=$"Bootstrapping the database"
dialog "$MESSAGE"
+ # Apply schema updates
+ migrate_db
+
# Update the maintenance account username. This can't be
# done through the api-config script since it uses the
# maintenance account to access the API. The maintenance
db-config
check
- # Handle migrations
- migrate_db
-
result "$MESSAGE"
;;
migrate)
-
MESSAGE=$"Migrating the database"
dialog "$MESSAGE"
;;
dump)
-
- MESSAGE=$"Dumping the database"
+ MESSAGE=$"Dumping the databases in /var/lib/pgsql/backups"
dialog "$MESSAGE"
- dump_db
+ dump_planetlab_db
+ dump_drupal_db
result "$MESSAGE"
;;
- clean-dump)
+ checkpoint)
+ MESSAGE=$"Checkpointing the databases"
+ checkpoint_planetlab_db $2
+ checkpoint_drupal_db $3
+ ;;
+
+ restore)
+ MESSAGE=$"Restoring the databases from checkpoint files"
+ restore_planetlab_db $2
+ restore_drupal_db $3
+ ;;
+ clean-dump)
MESSAGE=$"Cleaning old database dumps"
dialog "$MESSAGE"
echo "Usage: $0 [start|migrate|dump|clean-dump]"
exit 1
;;
-
-
esac
exit $ERRORS