Automatizando backups do PostgreSQL

Leitura recomendada

PostgreSql command line utilities without provide password

Sending messages to slack with bash script

Vamos lá

Script para automatizar backups do Postgres (plain + custom) e copiar para outro servidor (ex.: staging). Agende no cron junto com o script de restore.

O script produz dois formatos:

• Plain Backup: gzip de um script SQL.
• Custom Backup: archive custom compatível com pg_restore.

#!/bin/bash

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

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

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

# 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/jcottobboni/backups/database/postgresql/
SUFFIX="-daily"
FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"

# Database name to backup
DATABASE=db_name

# 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

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

# Number of days to keep daily backups
DAYS_TO_KEEP=15

#### SETTINGS FOR COPY BACKUPS ####
STAGING_ADDRESS='user@ip'

STAGING_FOLDER="$FINAL_BACKUP_DIR"


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

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

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


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

function perform_backups() {
  ./slack-cli chat send "Starting backup to database ${DATABASE}" '##slack-channel-name'
  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;

  echo -e "\n\nPerforming full backup of $DATABASE"
  echo -e "--------------------------------------------\n"

    if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
    then
      echo "Plain backup of $DATABASE"

      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
    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
  ./slack-cli chat send "Finishing Backup Produção for database: ${DATABASE}" '#slack-channel-name'
  echo -e "\nDatabase backup complete!"
}

function copy_backups_to_staging_server()
{
  echo "Making backup directory in $FINAL_BACKUP_DIR on Staging Server"

  if ! ssh $STAGING_ADDRESS "mkdir -p $FINAL_BACKUP_DIR"; then
    echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
    exit 1;
  fi;
  scp $FINAL_BACKUP_DIR$DATABASE.sql.gz $STAGING_ADDRESS:$STAGING_FOLDER
}

# DAILY BACKUPS

# Delete daily backups older than DAYS_TO_KEEP
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'

perform_backups
copy_backups_to_staging_server

Save this content in file called pg_backup.sh, and give permission to execute

chmod +x pg_backup.sh

O scp copia o backup para outro servidor (staging neste exemplo). Para copiar sem senha, adicione sua chave SSH:

ssh-copy-id -i ~/.ssh/id_rsa.pub "user"@"server-address-or-ip"

Schedule with cron:

crontab -e

# PostgreSQL Backup
00 00 * * * /path/to/script/pg_backup.sh

The script also prunes backups older than DAYS_TO_KEEP. Confirm paths and users before scheduling to avoid overwriting the wrong target.