plc.d/db

   1 #!/bin/bash
   2 #
   3 # priority: 900
   4 #
   5 # Bootstrap the database
   6 #
   7 # Mark Huang <mlhuang@cs.princeton.edu>
   8 # Copyright (C) 2006 The Trustees of Princeton University
   9 #
  10
  11 # Source function library and configuration
  12 . /etc/plc.d/functions
  13 . /etc/planetlab/plc_config
  14
  15 # Be verbose
  16 set -x
  17
  18 # Export so that we do not have to specify -p to psql invocations
  19 export PGPORT=$PLC_DB_PORT
  20
  21 # Install extensions
  22 function extend_db() {
  23         shopt -s nullglob
  24         for file in /usr/share/plc_api/extensions/*-up*; do
  25                 script=${file##*/}
  26                 name=${script%-up*}
  27                 extension=${script##*.}
  28                 version=$(psql -U $PLC_DB_USER --quiet --tuples-only --no-align -c \
  29                         "SELECT version FROM plc_db_extensions WHERE name='$name' LIMIT 1" \
  30                         $PLC_DB_NAME 2>/dev/null | awk 'BEGIN { ver=0 } /^[0-9]+$/ { ver=$1 } END { print ver }')
  31                 if [ $version -eq 0 ]; then
  32                         if [ "$extension" = "sql" ]; then
  33                                 dialog " - $script (dbdumped)"
  34                                 dump_planetlab_db "before-$script"
  35                                 psql -U $PLC_DB_USER -f $file $PLC_DB_NAME
  36                         elif [ -x $file ]; then
  37                                 dialog " - $script (dbdumped)"
  38                                 dump_planetlab_db "before-$script"
  39                                 $file
  40                         else
  41                                 dialog "\nWarning: extension $file not executable"
  42                         fi
  43                         check
  44                 fi
  45                 for file in /usr/share/plc_api/extensions/$name/migrations/[0-9]*-up-*; do
  46                         script=${file##*/}
  47                         index=${script%-up-*}
  48                         extension=${script##*.}
  49                         if [ $index -gt $version ]; then
  50                                 if [ "$extension" = "sql" ]; then
  51                                         dialog " - $script (dbdumped)"
  52                                         dump_planetlab_db "before-$script"
  53                                         psql -U $PLC_DB_USER -f $file $PLC_DB_NAME
  54                                 elif [ -x $file ]; then
  55                                         dialog " - $script (dbdumped)"
  56                                         dump_planetlab_db "before-$script"
  57                                         $file
  58                                 else
  59                                         dialog "\nWarning: migration $file not executable"
  60                                 fi
  61                                 check
  62                         fi
  63                 done
  64         done
  65 }
  66
  67 # Updates the database by applying all migration scripts in
  68 # /usr/share/plc_api/migrations/N-up-*, where N is greater than the
  69 # current subversion. At least one of the migration scripts with the
  70 # same N must update plc_db_version.subversion.
  71 function migrate_db() {
  72         subversion=$(psql -U $PLC_DB_USER --quiet --tuples-only --no-align -c \
  73                 "SELECT subversion FROM plc_db_version LIMIT 1" \
  74                 $PLC_DB_NAME 2>/dev/null || echo 0)
  75         shopt -s nullglob
  76         for file in /usr/share/plc_api/migrations/[0-9]*-up-*; do
  77                 script=$(basename $file)
  78                 index=${script%-up*}
  79                 extension=${script##*.}
  80                 if [ $index -gt $subversion ]; then
  81                         if [ "$extension" = "sql" ]; then
  82                                 dialog " - $script (dbdumped)"
  83                                 dump_planetlab_db "before-$script"
  84                                 psql -U $PLC_DB_USER -f $file $PLC_DB_NAME
  85                         elif [ -x $file ]; then
  86                                 dialog " - $script (dbdumped)"
  87                                 dump_planetlab_db "before-$script"
  88                                 $file
  89                         else
  90                                 dialog "\nWarning: migration $file not executable"
  91                         fi
  92                         check
  93                 fi
  94         done
  95 }
  96
  97 function checkpoint_planetlab_db() {
  98         dumpfile=$1
  99         pg_dump -U $PLC_DB_USER $PLC_DB_NAME >$dumpfile
 100         check
 101 }
 102
 103 function restore_planetlab_db() {
 104         dumpfile=$1
 105         if [ -n "$dumpfile" ]; then
 106                 [ -f "$dumpfile" ] && psql -a -U $PLC_DB_USER $PLC_DB_NAME <$dumpfile
 107                 check
 108         fi
 109 }
 110
 111 # use a single date of this script invocation for the dump_*_db functions.
 112 DATE=$(date +"%Y-%m-%d-%H-%M-%S")
 113
 114 # Dumps the database - optional argument to specify filename suffix
 115 function dump_planetlab_db() {
 116         if [ -n "$1" ]; then suffix="-$1"; else suffix=""; fi
 117         dumpfile=/var/lib/pgsql/backups/$(date +"${PLC_DB_NAME}.${DATE}${suffix}.sql")
 118         checkpoint_planetlab_db $dumpfile
 119 }
 120
 121 function restore_drupal_db() {
 122         dumpfile=$1
 123         if [ -n "$dumpfile" ]; then
 124                 [ -f "$dumpfile" ] && psql -a -U $PLC_DB_USER drupal <$1
 125                 check
 126         fi
 127 }
 128
 129 function checkpoint_drupal_db() {
 130         dumpfile=$1
 131         pg_dump -U $PLC_DB_USER drupal >$dumpfile
 132         check
 133 }
 134
 135 function dump_drupal_db() {
 136         dumpfile=/var/lib/pgsql/backups/$(date +"drupal.${DATE}.sql")
 137         checkpoint_drupal_db $dumpfile
 138         check
 139 }
 140
 141 # Clean up old backups
 142 function clean_dump() {
 143         local days="$1"; shift
 144         [[ -z "$days" ]] && days=15
 145         find /var/lib/pgsql/backups '(' -name "${PLC_DB_NAME}*.sql" -o -name "drupal*.sql" ')' -a -mtime +$days | xargs rm -f
 146         check
 147 }
 148
 149 [ $PLC_DB_ENABLED -ne 1 ] && exit 0
 150 case "$1" in
 151 start)
 152         MESSAGE=$"Bootstrapping the database"
 153         dialog "$MESSAGE"
 154
 155         # Apply schema updates
 156         migrate_db
 157         extend_db
 158
 159         # Update the maintenance account username. This can't be
 160         # done through the api-config script since it uses the
 161         # maintenance account to access the API. The maintenance
 162         # account should be person_id 1 since it is created by the
 163         # DB schema itself.
 164         psql -U $PLC_DB_USER -c "UPDATE persons SET email='$PLC_API_MAINTENANCE_USER' WHERE person_id=1" $PLC_DB_NAME
 165
 166         # Update the Drupal site_name variable
 167         # also turn off drupal native user registration
 168         psql -U $PLC_DB_USER drupal <<EOF
 169 DELETE FROM variable WHERE name = 'site_name';
 170 INSERT INTO variable (name, value) VALUES ('site_name', 's:${#PLC_NAME}:"$PLC_NAME";');
 171 DELETE FROM variable WHERE name = 'user_register';
 172 INSERT INTO variable (name, value) VALUES ('user_register', 's:1:"0";');
 173 DELETE FROM cache;
 174 EOF
 175
 176         # Bootstrap the DB
 177         db-config
 178         check
 179
 180         result "$MESSAGE"
 181         ;;
 182
 183 migrate)
 184         MESSAGE=$"Migrating the database"
 185         dialog "$MESSAGE"
 186
 187         migrate_db
 188         result "$MESSAGE"
 189         ;;
 190
 191 stop)
 192         MESSAGE="Ignoring request to stop myplc databases"
 193         dialog "$MESSAGE"
 194         result ""
 195         ;;
 196
 197 dump)
 198         MESSAGE=$"Dumping the databases in /var/lib/pgsql/backups"
 199         dialog "$MESSAGE"
 200
 201         dump_planetlab_db
 202         dump_drupal_db
 203         result "$MESSAGE"
 204         ;;
 205
 206 clean-dump)
 207         MESSAGE=$"Cleaning old database dumps"
 208         dialog "$MESSAGE"
 209         # clean_dump has a builtin default, so no problem
 210         clean_dump $2
 211         result "$MESSAGE"
 212         ;;
 213
 214 dump-and-clean)
 215         MESSAGE=$"dumping databases and cleaning old dumps"
 216         dialog "$MESSAGE"
 217         dump_planetlab_db
 218         dump_drupal_db
 219         clean_dump $2
 220         result "$MESSAGE"
 221         ;;
 222
 223 checkpoint)
 224         MESSAGE=$"Checkpointing the databases"
 225         checkpoint_planetlab_db $2
 226         checkpoint_drupal_db $3
 227         ;;
 228
 229 restore)
 230         MESSAGE=$"Restoring the databases from checkpoint files"
 231         restore_planetlab_db $2
 232         restore_drupal_db $3
 233         ;;
 234
 235 *)
 236         echo "Usage: $0 [start|migrate|stop|dump|clean-dump|dump-and-clean|checkpoint|restore]"
 237         exit 1
 238         ;;
 239 esac
 240
 241 exit $ERRORS