Pgsql dump with snapshots

• postgres backup uses a temporary snapshot database
• set gzip compression from 9 to 4

plugins/localdump/pgsql.sh

@@ -14,6 +14,7 @@
 # 2022-03-17         v1.3  WIP: add lines with prefix __DB__
 # 2023-09-20         v1.4  FIX could not change directory to "/root": Permission denied
 # 2024-10-02  ah     v1.5  rename backup and restore function
+# 2024-12-13  ah     v1.6  backup uses a snapshot db
 # ================================================================================
 
 if [ -z "$BACKUP_TARGETDIR" ]; then
@@ -34,9 +35,14 @@ fi
 # FUNCTION
 # --------------------------------------------------------------------------------
 
-
+# backup all databases
 function pgsql.backup(){
 
+  local DATABASE
+  local SNAPSHOTDB
+  local OUTFILE
+  local snapshotprefix="snapshot_"
+
   create_targetdir
 
   # ----- change owner of directory because of su command
@@ -45,24 +51,44 @@ function pgsql.backup(){
   # ----- GO
 
   # prevent could not change directory to "/root": Permission denied
+  (
   cd /tmp
+
   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)
+  for DATABASE in $( su ${PGUSER} -c "psql ${BACKUP_PARAMS} -At -c '$sSqlGetDblist' postgres" 2>/dev/null | grep -v "^$snapshotprefix" )
   do
     echo -n "__DB__${SERVICENAME} backup $DATABASE ... "
+    SNAPSHOTDB="${snapshotprefix}${DATABASE}"
     OUTFILE="${BACKUP_TARGETDIR}/$(get_outfile ${DATABASE}).sql"
-    if su ${PGUSER} -c "pg_dump ${BACKUP_PARAMS} -Fp ${DATABASE} >$OUTFILE"; then
-      fetchrc >/dev/null
-      db._compressDumpfile "$OUTFILE"
+
+    echo -n " snapshot ... "
+    # drop snapshot db first - just in case
+    su ${PGUSER} -c "dropdb ${SNAPSHOTDB}" >/dev/null 2>&1
+    su ${PGUSER} -c "psql ${BACKUP_PARAMS} -c \"CREATE DATABASE ${SNAPSHOTDB} WITH TEMPLATE ${DATABASE};\"" >/dev/null
+    fetchrc >/dev/null
+    if [ $myrc -eq 0 ]; then
+
+      echo -n "backup ... "
+      if su ${PGUSER} -c "pg_dump ${BACKUP_PARAMS} -Fp ${SNAPSHOTDB} >$OUTFILE"; then
+        fetchrc >/dev/null
+        echo -n "delete snapshot ... "
+        su ${PGUSER} -c "dropdb ${SNAPSHOTDB}"
+
+        db._compressDumpfile "$OUTFILE"
+      else
+        fetchrc
+        su ${PGUSER} -c "dropdb ${SNAPSHOTDB}"
+      fi
     else
-      fetchrc
+      cecho error "ERROR!"
     fi
 
   done
-  cd -
+  )
 }
 
 
+
 # restore database dump file into database
 # param  string  database dump file (gzipped)
 # param  string  optional: database to import; default: database is parsed from file