pg backup: disconnect after 21:00 and before 05:00

plugins/localdump/pgsql.sh

@@ -45,8 +45,12 @@ function pgsql.backup(){
   local snapshotprefix="snapshot_"
   local bUseSnapshot
   local bUseSnapshot4DB
+  local bDisconnect
+  local iHour; 
 
   typeset -i bUseSnapshot=0
+  typeset -i bDisconnect=0
+  typeset -i iHour; iHour=$( date +%H)
 
   create_targetdir
 
@@ -70,6 +74,14 @@ function pgsql.backup(){
     echo "INFO: create database failed for snapshots - dumping databases directly"
   fi
 
+  # disconnect after 21:00 and before 05:00
+  if [ $bUseSnapshot -eq 1 ]; then 
+    if [ $iHour -ge 21 ] || [ $iHour -lt 5 ]; then
+      echo "INFO: clients will be disconnected for snapshot database creation"
+      bDisconnect=1
+    fi
+  fi
+
   sSqlGetDblist="select datname from pg_database where not datistemplate and datallowconn order by datname;"
   for DATABASE in $( su ${PGUSER} -c "psql ${BACKUP_PARAMS} -At -c '$sSqlGetDblist' postgres" 2>/dev/null | grep -v "^$snapshotprefix" )
   do
@@ -86,13 +98,22 @@ function pgsql.backup(){
 
     # drop snapshot db first - just in case
     if [ $bUseSnapshot4DB -eq 1 ]; then 
-      echo -n " snapshot ... "
+      echo -n " snapshot ."
       su ${PGUSER} -c "dropdb ${SNAPSHOTDB}" >/dev/null 2>&1
+      echo -n "."
+      test $bDisconnect -ne 0 && su ${PGUSER} -c "psql ${BACKUP_PARAMS} -c \"
+        SELECT pg_terminate_backend(pg_stat_activity.pid)
+        FROM pg_stat_activity
+        WHERE pg_stat_activity.datname = '${DATABASE}'
+        AND pid <> pg_backend_pid();
+        \"" >/dev/null
+      echo -n "."
       if ! su ${PGUSER} -c "psql ${BACKUP_PARAMS} -c \"CREATE DATABASE ${SNAPSHOTDB} WITH TEMPLATE ${DATABASE};\"" >/dev/null; then
         echo -n "Fallback using origin ... "
         bUseSnapshot4DB=0
         SNAPSHOTDB="${DATABASE}"
       fi
+      echo -n " "
     fi
     if [ $myrc -eq 0 ]; then