#!/bin/bash
|
|
# dependencies
|
|
function check-postgresql-deps {
|
if [ ! -e /usr/bin/pg_dump ]; then
|
debug "It seems that you dont have psql installed. You may have problems to backup remote databases"
|
fi
|
numberRegex='^[0-9]+$'
|
if ! [[ $(id -u postgres 2>&1) =~ $numberRegex ]] ; then
|
debug "It seems that you dont have the user postgres on your computer. You may have problems to backup local postgres databases"
|
fi
|
if [ -r ~/.pgpass ]; then
|
debug "function check-postgresql-deps: ~/.pgpass exists, doing reomte backup."
|
else
|
# If the ~/.pgpass-file is missig, deactivate remote backup of postgresql.
|
POSTGRES_BACKUP_REMOTE=FLASE
|
fi
|
}
|
|
# PostgreSQL local
|
|
function pg_local_all {
|
# Debugoutput id DEBUG is enabled
|
debug "Dump all from Postgres local"
|
# Dump all databases in one file as user postgres (Debian)
|
sudo -H -i -u postgres pg_dumpall > "$TMP_DIR"/local_dumpall.pgsql
|
debug "Diff alldumps from Postgres local"
|
# Check if there are differences between the actual dump and the last dump
|
backup_file_handler "$TMP_DIR $BACKUP_DIR" local_dumpall.pgsql
|
}
|
|
function pg_local_single {
|
# Get a list with all databases on localhost. Do it as user postgres (Debian)
|
# parallelize the following
|
for DB in $(sudo -H -i -u postgres /usr/bin/psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname;" postgres)
|
do
|
debug "Dumping local database $DB"
|
# Dump each database in a single file
|
sudo -H -i -u postgres /usr/bin/pg_dump -Fp "$DB" > "$TMP_DIR"/local_"$DB".pgsql
|
backup_file_handler "$TMP_DIR" "$BACKUP_DIR" local_"$DB".pgsql
|
echo ""
|
done
|
}
|
|
# Do the local backupjobs
|
|
function run_local_pg_backups {
|
# Test if the current backupuser has access via sudo to postgres
|
# This part has to be improved!
|
sudo -l -U "$BACKUPUSER" | grep postgres
|
if [ $? -eq 0 ];then
|
# If the user has access vi sudo to postgres and a full dump is configured, do it.
|
if [ "$POSTGRES_BACKUP_LOCAL_DUMP_ALL" == "TRUE" ]; then
|
pg_local_all
|
fi
|
# If the user has access vi sudo to postgres and dumps for each database is configured , do it.
|
if [ "$POSTGRES_BACKUP_LOCAL_SINGLE" == "TRUE" ]; then
|
pg_local_single
|
fi
|
else
|
# If the user is not permitted to acces the postgresdatabases vi sudo throw an errormessage
|
echo "" | $LOGGER
|
echo "The backupuser does not the permission to act as user postgres" | $LOGGER
|
echo "Please add the following line to your /etc/sudoers:" | $LOGGER
|
echo "backupuser ALL=(postgres) NOPASSWD:/usr/bin/psql,/usr/bin/pg_dump,/usr/bin/pg_dumpall" | $LOGGER
|
echo "" | $LOGGER
|
fi
|
}
|
|
################################################################################
|
|
# Postgres remote
|
#
|
# All databases on remotehosts defined in the ~/.pgpass file will be backuped.
|
# So the ~/.pgpass is the configurationfile for this part!
|
|
function dump_remote_pgdb {
|
# Translate params ;-)
|
TRGTHOST=$1
|
TRGTPORT=$2
|
TRGTDB=$3
|
TRGTBDUSER=$4
|
# If debug is enabled, check the translated params
|
debug "PostgreSQL:"
|
debug " Host: $TRGTHOST"
|
debug " Port: $TRGTPORT"
|
debug " Database: $TRGTDB"
|
debug " User: $TRGTBDUSER"
|
debug "Testing TLS-Connection"
|
# Check if the connection to the postgres-server are encryptet (here we force with sslmode=require)
|
psql -U "$TRGTBDUSER" postgresql://"$TRGTHOST":"$TRGTPORT"/"$TRGTDB"?sslmode=require -c '\conninfo' | grep TLS > /dev/null 2>&1
|
if [ "$?" -eq "0" ]; then
|
debug "Dumping remote database $TRGTHOST-$TRGTDB"
|
# If we successfuly testet the encrypted connection to the postgres-server we try to force the sslmode
|
# I don't know if the following statement really effect to pg_dump :-(
|
export PGSSLMODE=require
|
# Dump the databases which are defined in the params
|
/usr/bin/pg_dump -U "$TRGTBDUSER" -h "$TRGTHOST" -p "$TRGTPORT" "$TRGTDB" > "$TMP_DIR"/"$TRGTHOST"-"$TRGTDB".pgql
|
backup_file_handler "$TMP_DIR" "$BACKUP_DIR" "$TRGTHOST"-"$TRGTDB".pgql
|
else
|
# If no encrypted connection to the postgres-server can be established throw an errormessage
|
echo "" | $LOGGER
|
echo "Could not establish a TLS encrypted connection the the databasehost." | $LOGGER
|
echo "Please configure the connections with hostssl in pg_hba.conf." | $LOGGER
|
echo "" | $LOGGER
|
fi
|
echo ""
|
}
|
|
function run_remote_pg_backups {
|
# Check if the remoebackup for postgres is configured
|
if [ "$POSTGRES_BACKUP_REMOTE" = "TRUE" ]; then
|
# If yes the check for the ~/.pgpass-file. Here are the remotedatabases specified
|
if [ -r ~/.pgpass ]; then
|
# parallelize the following
|
while read -r LINE; do
|
# For each entry do the backup
|
debug "run dump with params $LINE"
|
# CAUTION: No doublequotes in the following line. The var $LINE has to be splittet!
|
dump_remote_pgdb $LINE
|
# To get the params for the function the .pgpass-file is striped from the comments,
|
# the ":" are replaces against whitespaces and only the first four coloums are used
|
# so we give "host port database user" to the function
|
done <<< "$(cat ~/.pgpass | grep -v '#' | tr ":" " " | cut -d " " -f1,2,3,4)"
|
else
|
# If the ~/.pgpass-file is missig, throw an errormessage
|
echo "" | $LOGGER
|
echo "The ~/.pgpass file is missing, no remote postgres databases will be backuped." | $LOGGER
|
echo "If you want do backup postgres reomte databases, please create a ~/.pgpass file in the homedirectory of your backupuser (https://wiki.postgresql.org/wiki/Pgpass)." | $LOGGER
|
echo "" | $LOGGER
|
fi
|
fi
|
echo ""
|
}
|
|
function postgresql-main {
|
if [ "$POSTGRES_BACKUP_LOCAL" = "TRUE" ]; then
|
run_local_pg_backups
|
fi
|
if [ "$POSTGRES_BACKUP_REMOTE" = "TRUE" ]; then
|
run_remote_pg_backups
|
fi
|
}
|
