A good backup strategy is essential to any database.  Recently, we implemented a simple PostgreSQL backup using pg_dump and AzCopy on Ubuntu 18.

The backup process will:

  • backup the database once per day
  • keep backups and logs for 21 days
  • remove old backups
  • copy the backups to Azure Blob Storage once per week for long term storage

Note: this strategy assumes that the data drive is a cloud drive and is redundant within the data center.  If not, you should copy the backups to Azure Blob Storage or S3 every day.  It may be advisable to have backups on a separate cloud provider than your VM.

Steps:

1.  Create backup directory.  Ideally, this would be on a different drive than the database.

sudo mkdir /backupdrive/postgresql

2. Install AzCopy

sudo mkdir /backupdrive/azcopy
cd /backupdrive/azcopy
sudo wget -O azcopy.tar.gz https://aka.ms/downloadazcopy-v10-linux
sudo tar -xf azcopy.tar.gz
sudo rm azcopy.tar.gz
sudo cp azcopy_linux_amd64*/* .
sudo rm azcopy_linux_amd64*/*
sudo rmdir azcopy_linux_amd64*

3. Create a backup script.  This script archives the output of the backup to a log file, then removes log files and backup files older than 21 days

sudo nano /backupdrive/postgresql/backup.sh

Contents:

DATESTAMP=$(date +%Y-%m-%d)
DAYOFWEEK=$(date +%A)
FILENAME=$DATESTAMP.bak

#Remove old files
sudo find /backupdrive/postgresql -mtime +21 -type f -exec rm -f {} \; 2> /backupdrive/postgresql/$DATESTAMP.cleanup.log 1>&2

#Start the backup
sudo -u postgres /usr/lib/postgresql/12/bin/pg_dump -v -F c -d {DBNAME} -h localhost -p {DB PORT} -U {DB USER} -f /backupdrive/postgresql/$FILENAME 2> /backupdrive/postgresql/$DATESTAMP.backup.log 1>&2

#Grant postgres user ownership of the backup
sudo chown postgres -R /backupdrive/postgresql

#Once per week, copy those files to long term blob storage
if [ $DAYOFWEEK = 'Saturday' ] ; then
  echo "Starting Copy to Azure Blob"
  /backupdrive/azcopy/azcopy copy /backupdrive/postgresql/$FILENAME "{BLOB URL WITH SAS" --recursive --block-blob-tier="cold"
else
  echo "Skipping Copy to Azure Blob"
fi

Make the script executable

sudo chmod +x /backupdrive/postgresql/backup.sh

4.  Schedule the backups

sudo nano /etc/crontab

Add the following line, customize the time you want the backups to run.  This will run at 11:00pm UTC every day.

00 23   * * *   root /backupdrive/postgresql/backup.sh

Thanks for reading!  Have any questions or thoughts?  We'd love to hear them.