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.