Backup script for PostgreSQL to S3 on Linux

Pubblicato il di

Thanks to: https://zaiste.net/posts/backup_postgresql_to_amazon_s3/

First configure s3:
apt-get install s3cmd
Set Amazon access & secret keys using –configure option. Enable encryption for data transferring as well as HTTPS.

s3cmd –configure

#Example of Backup.sh with options for retention (folder name)

APP=$1

DB_NAME=$2
DB_USER=$3
DB_PASS=$4
AWS_RET=$5

BUCKET_NAME=”your.s3.backup/$AWS_RET”

TIMESTAMP=$(date +%F_%T | tr ‘:’ ‘-‘)
TEMP_FILE=$(mktemp XXXXXXXXXX.dump)
TEMP_TARGZ=$(mktemp tar.XXXXXXXXXX)

S3_FILE=”s3://$BUCKET_NAME/$APP/$APP-backup-$TIMESTAMP.tar.gz”

PGPASSWORD=$DB_PASS pg_dump -Fc –no-acl -h localhost -U $DB_USER $DB_NAME > $TEMP_FILE
tar -czvf $TEMP_TARGZ $TEMP_FILE
s3cmd put $TEMP_TARGZ $S3_FILE
#!s3cmd put $TEMP_FILE $S3_FILE –encrypt
rm “$TEMP_FILE”
rm “$TEMP_TARGZ”

#! Save the file to backup.sh and make it executable with chmod +x.
#! Test if it works with properly set positional parameters,
#! e.g. #! ./backup.sh acme acme_db acme_user acme_pass

#! Add a cron entry using crontab -e and set it to run daily at 11pm
#! o 23 * * * /path/to/backup.sh acme acme_db acme_user acme_pass