Principe backup PostgreSql

Cet article présente un principe de sauvegarde des bases PostgreSql.

Votre avis

Nobody voted on this yet  You need to enable JavaScript to vote

Tweet

Configuration

Un compte spécifique est utilisé pour effectuer la sauvegarde des bases. Dans le cadre de cet article, l'exécution s'effectue sur la même machine où est installé PostgreSql. L'authentification sera effectuée en mode confiance, mais uniquement sur cette machine. Il faut configurer le fichier /etc/postgresql/12/main/pg_hba.conf.

# Custom user access
# backup user
# For localhost host name with user backup
host    all             backup          ::1/128                 trust

Ainsi si la machine, il n'est pas nécessaire de fournir de mot de passe au compte backup, et cela quelque soit le compte système en cours d'utilisation.

Rôle

Afin de faciliter les gestions des permissions, un rôle viewers est créé, comme indiqué dans l'article de création de rôle.

postgres=# CREATE ROLE VIEWERS;
CREATE ROLE

Utilisateurs

Un compte utilisateur backup est créé, comme indiqué dans l'article de création de rôle.

postgres=# CREATE USER backup;
CREATE ROLE

A noter qu'aucun mot de passe n'est fourni lors de la création de ce compte. L'objectif est de ne l'utiliser que sur la machine, et de passer par le mode confiance comme indiqué dans le paragraphe Configuration.

Ce compte sera utilisé pour exécuter tous les dump des bases. Pour faciliter les droits d'accès, il est ajouté au rôle viewers.

postgres=# GRANT viewers TO backup;
GRANT ROLE

Droits d'accès

La sauvegarde sera effectuée par des dumps des données sur l'ensemble des tables. Il est donc nécessaire que le compte est au moins le privilège SELECT sur toutes les tables. En fonction des produits, de nouvelles tables peuvent être mises à disposition lors de mise à jour. Afin de faciliter cette gestion des privilèges, il est préférable de positionner les privilèges par défaut sur les différentes bases, comme indiqué dans l'article de gestion des rôles.

A noter que le mise à jour des droits par défaut doit être réalisé avec le compte qui doit faire des mises à jour du schéma. Dans le case de SonarQube, le compte d'accès est sonar. Il faut donc exécuter la commande avec le compte sonar.

postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewers;
ALTER DEFAULT PRIVILEGES

Attention, si la base est déjà existante, l'affectation des privilèges par défaut n'affecte pas les tables existantes. Il peut être nécessaire d'affecter les permissions au rôle viewer sur l'ensemble de ces tables.

postgres=# GRANT SELECT ON ALL TABLES IN schema public TO viewers;
GRANT

Scripts

Sur le site officiel, deux scripts de sauvegarde sont fournis avec un fichier de configuration. Ceux-ci sont utilisés pour leur simplicité.

Préparation environnement

Les scripts sont déployés dans le répertoire /var/opt/autopostgresqlbackup préalablement créé.

#sudo mkdir -p /var/opt/autopostgresqlbackup
#sudo chown root:root /var/opt/autopostgresqlbackup
#sudo chmod 770 /var/opt/autopostgresqlbackup

Dans le cadre de cet article, un groupe système backup, attention c'est différent du compte backup créé sous PostgreSql, et il est possible de donner les droits à l'arborescence.

#sudo chown root:backup /var/opt/autopostgresqlbackup

Les permissions mises en place sur les deux script sh doivent permettre la modification et exécution pour le propriétaire et le groupe.

#sudo chmod 770 /var/opt/autopostgresqlbackup/*.sh

Script pg_backup.sh

Ce script permet de réaliser une sauvegarde de la base.

#!/bin/bash

###########################
####### LOAD CONFIG #######
###########################

while [ $# -gt 0 ]; do
    case $1 in
        -c)
            if [ -r "$2" ]; then
                source "$2"
                shift 2
            else
                ${ECHO} "Unreadable config file \"$2\"" 1>&2
                exit 1
            fi
            ;;
        *)
            ${ECHO} "Unknown Option \"$1\"" 1>&2
            exit 2
            ;;
    esac
done

if [ $# = 0 ]; then
    SCRIPTPATH=$(cd ${0%/*} && pwd -P)
    source $SCRIPTPATH/pg_backup.config
fi;

###########################
#### PRE-BACKUP CHECKS ####
###########################

# Make sure we're running as the required backup user
if [ "$BACKUP_USER" != "" -a "$(id -un)" != "$BACKUP_USER" ]; then
    echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
    exit 1;
fi;


###########################
### INITIALISE DEFAULTS ###
###########################

if [ ! $HOSTNAME ]; then
    HOSTNAME="localhost"
fi;

if [ ! $USERNAME ]; then
    USERNAME="postgres"
fi;


###########################
#### START THE BACKUPS ####
###########################


FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"

echo "Making backup directory in $FINAL_BACKUP_DIR"

if ! mkdir -p $FINAL_BACKUP_DIR; then
    echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
    exit 1;
fi;


#######################
### GLOBALS BACKUPS ###
#######################

echo -e "\n\nPerforming globals backup"
echo -e "--------------------------------------------\n"

if [ $ENABLE_GLOBALS_BACKUPS = "yes" ]
then
    echo "Globals backup"

    set -o pipefail
    if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
        echo "[!!ERROR!!] Failed to produce globals backup" 1>&2
    else
        mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress $FINAL_BACKUP_DIR"globals".sql.gz
    fi
    set +o pipefail
else
    echo "None"
fi


###########################
### SCHEMA-ONLY BACKUPS ###
###########################

for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
    SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
done

SCHEMA_ONLY_QUERY="select datname from pg_database where false $SCHEMA_ONLY_CLAUSE order by datname;"

echo -e "\n\nPerforming schema-only backups"
echo -e "--------------------------------------------\n"

SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`

echo -e "The following databases were matched for schema-only backup:\n${SCHEMA_ONLY_DB_LIST}\n"

for DATABASE in $SCHEMA_ONLY_DB_LIST
do
    echo "Schema-only backup of $DATABASE"

    set -o pipefail
    if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
        echo "[!!ERROR!!] Failed to backup database schema of $DATABASE" 1>&2
    else
        mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
    fi
    set +o pipefail
done


###########################
###### FULL BACKUPS #######
###########################

for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
    EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ '$SCHEMA_ONLY_DB'"
done

FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by datname;"

echo -e "\n\nPerforming full backups"
echo -e "--------------------------------------------\n"

for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" postgres`
do
    if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
    then
        echo "Plain backup of $DATABASE"

        set -o pipefail
        if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
        fi
        set +o pipefail
    fi

    if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
    then
        echo "Custom backup of $DATABASE"

        if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
            echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
        fi
    fi

done

echo -e "\nAll database backups complete!"

Script pg_backup_rotated.sh

Le principe de ce script est similaire au précédent sauf qu'il permet d'effectuer des rotations journalières / hebdomadaires et mensuelles des sauvegardes.

#!/bin/bash

###########################
####### LOAD CONFIG #######
###########################

while [ $# -gt 0 ]; do
    case $1 in
        -c)
            CONFIG_FILE_PATH="$2"
            shift 2
            ;;
        *)
            ${ECHO} "Unknown Option \"$1\"" 1>&2
            exit 2
            ;;
    esac
done

if [ -z $CONFIG_FILE_PATH ] ; then
    SCRIPTPATH=$(cd ${0%/*} && pwd -P)
    CONFIG_FILE_PATH="${SCRIPTPATH}/pg_backup.config"
fi

if [ ! -r ${CONFIG_FILE_PATH} ] ; then
    echo "Could not load config file from ${CONFIG_FILE_PATH}" 1>&2
    exit 1
fi

source "${CONFIG_FILE_PATH}"

###########################
#### PRE-BACKUP CHECKS ####
###########################

# Make sure we're running as the required backup user
if [ "$BACKUP_USER" != "" -a "$(id -un)" != "$BACKUP_USER" ] ; then
    echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
    exit 1
fi


###########################
### INITIALISE DEFAULTS ###
###########################

if [ ! $HOSTNAME ]; then
    HOSTNAME="localhost"
fi;

if [ ! $USERNAME ]; then
    USERNAME="postgres"
fi;


###########################
#### START THE BACKUPS ####
###########################

function perform_backups()
{
    SUFFIX=$1
    FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"

    echo "Making backup directory in $FINAL_BACKUP_DIR"

    if ! mkdir -p $FINAL_BACKUP_DIR; then
        echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
        exit 1;
    fi;
    
    #######################
    ### GLOBALS BACKUPS ###
    #######################

    echo -e "\n\nPerforming globals backup"
    echo -e "--------------------------------------------\n"

    if [ $ENABLE_GLOBALS_BACKUPS = "yes" ]
    then
        echo "Globals backup"

        set -o pipefail
        if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to produce globals backup" 1>&2
        else
            mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress $FINAL_BACKUP_DIR"globals".sql.gz
        fi
        set +o pipefail
    else
        echo "None"
    fi


    ###########################
    ### SCHEMA-ONLY BACKUPS ###
    ###########################
    
    for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
    do
        SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
    done
    
    SCHEMA_ONLY_QUERY="select datname from pg_database where false $SCHEMA_ONLY_CLAUSE order by datname;"
    
    echo -e "\n\nPerforming schema-only backups"
    echo -e "--------------------------------------------\n"
    
    SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`
    
    echo -e "The following databases were matched for schema-only backup:\n${SCHEMA_ONLY_DB_LIST}\n"
    
    for DATABASE in $SCHEMA_ONLY_DB_LIST
    do
        echo "Schema-only backup of $DATABASE"
        set -o pipefail
        if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to backup database schema of $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
        fi
        set +o pipefail
    done
    
    
    ###########################
    ###### FULL BACKUPS #######
    ###########################

    for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
    do
        EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ '$SCHEMA_ONLY_DB'"
    done

    FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by datname;"

    echo -e "\n\nPerforming full backups"
    echo -e "--------------------------------------------\n"

    for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" postgres`
    do
        if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
        then
            echo "Plain backup of $DATABASE"
     
            set -o pipefail
            if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
                echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
            else
                mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
            fi
            set +o pipefail
                        
        fi

        if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
        then
            echo "Custom backup of $DATABASE"
    
            if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
                echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE"
            else
                mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
            fi
        fi

    done

    echo -e "\nAll database backups complete!"
}

# MONTHLY BACKUPS

DAY_OF_MONTH=`date +%d`

if [ $DAY_OF_MONTH -eq 1 ];
then
    # Delete all expired monthly directories
    find $BACKUP_DIR -maxdepth 1 -name "*-monthly" -exec rm -rf '{}' ';'
                
    perform_backups "-monthly"
    
    exit 0;
fi

# WEEKLY BACKUPS

DAY_OF_WEEK=`date +%u` #1-7 (Monday-Sunday)
EXPIRED_DAYS=`expr $((($WEEKS_TO_KEEP * 7) + 1))`

if [ $DAY_OF_WEEK = $DAY_OF_WEEK_TO_KEEP ];
then
    # Delete all expired weekly directories
    find $BACKUP_DIR -maxdepth 1 -mtime +$EXPIRED_DAYS -name "*-weekly" -exec rm -rf '{}' ';'
                
    perform_backups "-weekly"
    
    exit 0;
fi

# DAILY BACKUPS

# Delete daily backups 7 days old or more
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'

perform_backups "-daily"

Configuration

Les deux scripts s'appuient sur un fichier de configuration à fournir en paramètre de la ligne de commande.

##############################
## POSTGRESQL BACKUP CONFIG ##
##############################

# Optional system user to run backups as.  If the user the script is running as doesn't match this
# the script terminates.  Leave blank to skip check.
BACKUP_USER=

# Optional hostname to adhere to pg_hba policies.  Will default to "localhost" if none specified.
HOSTNAME=

# Optional username to connect to database as.  Will default to "postgres" if none specified.
USERNAME=

# This dir will be created if it doesn't exist.  This must be writable by the user the script is
# running as.
BACKUP_DIR=/home/backups/database/postgresql/
 
# List of strings to match against in database name, separated by space or comma, for which we only
# wish to keep a backup of the schema, not the data. Any database names which contain any of these
# values will be considered candidates. (e.g. "system_log" will match "dev_system_log_2010-01")
SCHEMA_ONLY_LIST=""

# Will produce a custom-format backup if set to "yes"
ENABLE_CUSTOM_BACKUPS=yes

# Will produce a gzipped plain-format backup if set to "yes"
ENABLE_PLAIN_BACKUPS=yes

# Will produce gzipped sql file containing the cluster globals, like users and passwords, if set to "yes"
ENABLE_GLOBALS_BACKUPS=yes


#### SETTINGS FOR ROTATED BACKUPS ####

# Which day to take the weekly backup from (1-7 = Monday-Sunday)
DAY_OF_WEEK_TO_KEEP=5

# Number of days to keep daily backups
DAYS_TO_KEEP=7

# How many weeks to keep weekly backups
WEEKS_TO_KEEP=5

######################################

Les éléments de configuration sont utilisés ainsi.

Paramètre	Valeur
BACKUP_USER	Optionnel Utilisateur devant exécuter le script.
HOSTNAME	Optionnel Nom de la machine sur laquelle est exécuté le script, pour vérification avec les droits établis dans le fichier pg_hba.conf de PostgreSql.
USERNAME	Optionnel Nom d'utilisateur se connectant à la base de données.
BACKUP_DIR	Emplacement des sauvegardes
SCHEMA_ONLY_LIST	Liste des schémas pour lesquels une sauvegarde de la définition, sans les données, est réalisée.
ENABLE_CUSTOM_BACKUPS	Effectue une sauvegarde au format custom.
ENABLE_PLAIN_BACKUPS	Effectue une sauvegarde "plein texte" et compressée.
ENABLE_GLOBALS_BACKUPS	Effectue une sauvegarde des paramètres globaux de la base.
DAY_OF_WEEK_TO_KEEP	Spécifique pour rotation. Identifiant du jour lors duquel la rotation hebdomadaire est réalisée.
DAYS_TO_KEEP	Spécifique pour rotation. Nombre de jours à conserver pour les rotations journalières.
WEEKS_TO_KEEP	Spécifique pour rotation. Nombre de semaine à conserver pour les rotations hebdomadaires.

Personnalisation scripts

Dans les scripts standards, les sauvegardes des données sont réalisées sur l'ensemble des schémas de la base. Une personnalisation est mise en place afin de filtrer les schémas sur lesquels la sauvegarde est déclenchée, inspirée de ce qui est fait la sauvegarde du schéma uniquement.

Script pg_backup.sh

#!/bin/bash

###########################
####### LOAD CONFIG #######
###########################

while [ $# -gt 0 ]; do
    case $1 in
        -c)
            if [ -r "$2" ]; then
                source "$2"
                shift 2
            else
                ${ECHO} "Unreadable config file \"$2\"" 1>&2
                exit 1
            fi
            ;;
        *)
            ${ECHO} "Unknown Option \"$1\"" 1>&2
            exit 2
            ;;
    esac
done

if [ $# = 0 ]; then
    SCRIPTPATH=$(cd ${0%/*} && pwd -P)
    source $SCRIPTPATH/pg_backup.config
fi;

###########################
#### PRE-BACKUP CHECKS ####
###########################

# Make sure we're running as the required backup user
if [ "$BACKUP_USER" != "" -a "$(id -un)" != "$BACKUP_USER" ]; then
    echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
    exit 1;
fi;


###########################
### INITIALISE DEFAULTS ###
###########################

if [ ! $HOSTNAME ]; then
    HOSTNAME="localhost"
fi;

if [ ! $USERNAME ]; then
    USERNAME="postgres"
fi;


###########################
#### START THE BACKUPS ####
###########################


FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"

echo "Making backup directory in $FINAL_BACKUP_DIR"

if ! mkdir -p $FINAL_BACKUP_DIR; then
    echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
    exit 1;
fi;


#######################
### GLOBALS BACKUPS ###
#######################

echo -e "\n\nPerforming globals backup"
echo -e "--------------------------------------------\n"

if [ $ENABLE_GLOBALS_BACKUPS = "yes" ]
then
    echo "Globals backup"

    set -o pipefail
    if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
        echo "[!!ERROR!!] Failed to produce globals backup" 1>&2
    else
        mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress $FINAL_BACKUP_DIR"globals".sql.gz
    fi
    set +o pipefail
else
    echo "None"
fi


###########################
### SCHEMA-ONLY BACKUPS ###
###########################

for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
    SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
done

SCHEMA_ONLY_QUERY="select datname from pg_database where false $SCHEMA_ONLY_CLAUSE order by datname;"

echo -e "\n\nPerforming schema-only backups"
echo -e "--------------------------------------------\n"

SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`

echo -e "The following databases were matched for schema-only backup:\n${SCHEMA_ONLY_DB_LIST}\n"

for DATABASE in $SCHEMA_ONLY_DB_LIST
do
    echo "Schema-only backup of $DATABASE"

    set -o pipefail
    if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
        echo "[!!ERROR!!] Failed to backup database schema of $DATABASE" 1>&2
    else
        mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
    fi
    set +o pipefail
done


###########################
###### FULL BACKUPS #######
###########################

for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
do
    EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ '$SCHEMA_ONLY_DB'"
done

for SCHEMA_FITLER_DB in ${SCHEMA_FILTER//,/ }
do
    SCHEMA_FILTER_CLAUSE="$SCHEMA_FILTER_CLAUSE and datname = '$SCHEMA_FITLER_DB'"
done
 
FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE $SCHEMA_FILTER_CLAUSE order by datname;"

echo -e "\n\nPerforming full backups"
echo -e "--------------------------------------------\n"

for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" postgres`
do
    if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
    then
        echo "Plain backup of $DATABASE"

        set -o pipefail
        if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
        fi
        set +o pipefail
    fi

    if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
    then
        echo "Custom backup of $DATABASE"

        if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
            echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
        fi
    fi

done

echo -e "\nAll database backups complete!"

Script pg_backup_rotated.sh

#!/bin/bash

###########################
####### LOAD CONFIG #######
###########################

while [ $# -gt 0 ]; do
    case $1 in
        -c)
            CONFIG_FILE_PATH="$2"
            shift 2
            ;;
        *)
            ${ECHO} "Unknown Option \"$1\"" 1>&2
            exit 2
            ;;
    esac
done

if [ -z $CONFIG_FILE_PATH ] ; then
    SCRIPTPATH=$(cd ${0%/*} && pwd -P)
    CONFIG_FILE_PATH="${SCRIPTPATH}/pg_backup.config"
fi

if [ ! -r ${CONFIG_FILE_PATH} ] ; then
    echo "Could not load config file from ${CONFIG_FILE_PATH}" 1>&2
    exit 1
fi

source "${CONFIG_FILE_PATH}"

###########################
#### PRE-BACKUP CHECKS ####
###########################

# Make sure we're running as the required backup user
if [ "$BACKUP_USER" != "" -a "$(id -un)" != "$BACKUP_USER" ] ; then
    echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
    exit 1
fi


###########################
### INITIALISE DEFAULTS ###
###########################

if [ ! $HOSTNAME ]; then
    HOSTNAME="localhost"
fi;

if [ ! $USERNAME ]; then
    USERNAME="postgres"
fi;


###########################
#### START THE BACKUPS ####
###########################

function perform_backups()
{
    SUFFIX=$1
    FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"

    echo "Making backup directory in $FINAL_BACKUP_DIR"

    if ! mkdir -p $FINAL_BACKUP_DIR; then
        echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
        exit 1;
    fi;

    #######################
    ### GLOBALS BACKUPS ###
    #######################

    echo -e "\n\nPerforming globals backup"
    echo -e "--------------------------------------------\n"

    if [ $ENABLE_GLOBALS_BACKUPS = "yes" ]
    then
        echo "Globals backup"

        set -o pipefail
        if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to produce globals backup" 1>&2
        else
            mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress $FINAL_BACKUP_DIR"globals".sql.gz
        fi
        set +o pipefail
    else
        echo "None"
    fi


    ###########################
    ### SCHEMA-ONLY BACKUPS ###
    ###########################

    for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
    do
        SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
    done

    SCHEMA_ONLY_QUERY="select datname from pg_database where false $SCHEMA_ONLY_CLAUSE order by datname;"

    echo -e "\n\nPerforming schema-only backups"
    echo -e "--------------------------------------------\n"

    SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`

    echo -e "The following databases were matched for schema-only backup:\n${SCHEMA_ONLY_DB_LIST}\n"

    for DATABASE in $SCHEMA_ONLY_DB_LIST
    do
        echo "Schema-only backup of $DATABASE"
        set -o pipefail
        if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
            echo "[!!ERROR!!] Failed to backup database schema of $DATABASE" 1>&2
        else
            mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
        fi
        set +o pipefail
    done


    ###########################
    ###### FULL BACKUPS #######
    ###########################

    for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
    do
        EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ '$SCHEMA_ONLY_DB'"
    done
 
    for SCHEMA_FITLER_DB in ${SCHEMA_FILTER//,/ }
    do
        SCHEMA_FILTER_CLAUSE="$SCHEMA_FILTER_CLAUSE and datname = '$SCHEMA_FITLER_DB'"
    done

    FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE $SCHEMA_FILTER_CLAUSE order by datname;"

    echo -e "\n\nPerforming full backups"
    echo -e "--------------------------------------------\n"

    for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" postgres`
    do
        if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
        then
            echo "Plain backup of $DATABASE"
     
            set -o pipefail
            if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
                echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
            else
                mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
            fi
            set +o pipefail
            
        fi

        if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
        then
            echo "Custom backup of $DATABASE"

            if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
                echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE"
            else
                mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
            fi
        fi

    done

    echo -e "\nAll database backups complete!"
}

# MONTHLY BACKUPS

DAY_OF_MONTH=`date +%d`

if [ $DAY_OF_MONTH -eq 1 ];
then
    # Delete all expired monthly directories
    find $BACKUP_DIR -maxdepth 1 -name "*-monthly" -exec rm -rf '{}' ';'

    perform_backups "-monthly"

    exit 0;
fi

# WEEKLY BACKUPS

DAY_OF_WEEK=`date +%u` #1-7 (Monday-Sunday)
EXPIRED_DAYS=`expr $((($WEEKS_TO_KEEP * 7) + 1))`

if [ $DAY_OF_WEEK = $DAY_OF_WEEK_TO_KEEP ];
then
    # Delete all expired weekly directories
    find $BACKUP_DIR -maxdepth 1 -mtime +$EXPIRED_DAYS -name "*-weekly" -exec rm -rf '{}' ';'

    perform_backups "-weekly"

    exit 0;
fi

# DAILY BACKUPS

# Delete daily backups 7 days old or more
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'

perform_backups "-daily"

Configuration

La nouvelle variable SCHEMA_FILTER a été introduite dans les scripts de sauvegarde afin de filtrer les schémas sur lesquels elle est exécutée. Le fonctionnement est le même que pour la variable SCHEMA_ONLY_LIST et il suffit de renseigner les schémas séparés par un espace.

##############################
## POSTGRESQL BACKUP CONFIG ##
##############################

# Optional system user to run backups as.  If the user the script is running as doesn't match this
# the script terminates.  Leave blank to skip check.
BACKUP_USER=

# Optional hostname to adhere to pg_hba policies.  Will default to "localhost" if none specified.
HOSTNAME=

# Optional username to connect to database as.  Will default to "postgres" if none specified.
USERNAME=

# This dir will be created if it doesn't exist.  This must be writable by the user the script is
# running as.
BACKUP_DIR=/home/backups/database/postgresql/
 
# List of strings to match against in database name, separated by space or comma, for which we
# perform backup of the schema.
SCHEMA_FILTER="" 

# List of strings to match against in database name, separated by space or comma, for which we only
# wish to keep a backup of the schema, not the data. Any database names which contain any of these
# values will be considered candidates. (e.g. "system_log" will match "dev_system_log_2010-01")
SCHEMA_ONLY_LIST=""

# Will produce a custom-format backup if set to "yes"
ENABLE_CUSTOM_BACKUPS=yes

# Will produce a gzipped plain-format backup if set to "yes"
ENABLE_PLAIN_BACKUPS=yes

# Will produce gzipped sql file containing the cluster globals, like users and passwords, if set to "yes"
ENABLE_GLOBALS_BACKUPS=yes


#### SETTINGS FOR ROTATED BACKUPS ####

# Which day to take the weekly backup from (1-7 = Monday-Sunday)
DAY_OF_WEEK_TO_KEEP=5

# Number of days to keep daily backups
DAYS_TO_KEEP=7

# How many weeks to keep weekly backups
WEEKS_TO_KEEP=5

######################################

Voir aussi

Documentation officielle: https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux