Backup PostgreSQL Database to Backblaze B2 object storage

I have a VM in an external datacenter, and I needed to backup my PostgreSQL database somewhere else. I use Backblaze as a Veeam B&R repository, so I decided to create a new bucket there for my database dumps. The below script can easily be modified for MySQL/MariaDB as well, if needed.

#!/bin/bash
DIR=/home/user/pgsql_dump
SQLFILENAME=dbname-$(date +%Y%m%d-%H%M).sql
GZFILENAME=dbname-$(date +%Y%m%d-%H%M).7z
PGDLOG=/home/user/pgsql_dump/pgsql_dump.log
BUCKET=Insert-Bucket-Name-Here
MAX_BACKUPS=12

printf '%s Starting Backup. Exporting dbname database\n' "$(date)" >> $PGDLOG
sudo -u postgres pg_dump dbname > $DIR/$SQLFILENAME 2>>$PGDLOG
7z a -mx=1 $DIR/$GZFILENAME $DIR/$SQLFILENAME
#tar -czf $GZFILENAME $SQLFILENAME 2>>$PGDLOG
rm $DIR/$SQLFILENAME 2>>$PGDLOG

#Upload file to Backblaze
b2-linux upload-file $BUCKET $DIR/$GZFILENAME $GZFILENAME
#Count Filecount in bucket
COUNT=$(b2-linux ls $BUCKET | wc -l)
if [ $COUNT -gt $MAX_BACKUPS ] ; then
  UUID=$(b2-linux ls --long $BUCKET | sort -k3 -k4 | head -n1 | awk -F'[[:space:]][[:space:]]+' '{print $1}')
  FILENAME=$(b2-linux ls --long $BUCKET | sort -k3 -k4 | head -n1 | awk -F'[[:space:]][[:space:]]+' '{print $6}')
  printf '%s Removing oldest backup %s (%s) from a total of %s files\n' "$(date)" "$FILENAME" "$UUID" "$COUNT" >> $PGDLOG
  b2-linux delete-file-version $UUID 2>> $PGDLOG
else
  printf '%s Not enough backup copies to run cleanup. Total backups in bucket %s.\n' "$(date)" "$COUNT" >> $PGDLOG
fi

# Store Latest Backup file to log
UUID=$(b2-linux ls --long $BUCKET | sort -k3 -k4 | tail -n1 | awk -F'[[:space:]][[:space:]]+' '{print $1}')
FILENAME=$(b2-linux ls --long $BUCKET | sort -k3 -k4 | tail -n1 | awk -F'[[:space:]][[:space:]]+' '{print $6}')
printf '%s Total backups in bucket %s. Newest backup %s (%s)\n' "$(date)" "$COUNT" "$FILENAME" "$UUID" >> $PGDLOG

rm $DIR/$GZFILENAME 2>>$PGDLOG
printf '%s Backup Completed\n' "$(date)" >> $PGDLOG

The script has a few requirements that you need to meet for it to work:

• Install 7zip (For example: dnf install p7zip p7zip-plugins)
• You need b2 cli (Get the Command-Line Tool (backblaze.com))
• Run “b2 authorize-account” with a key and secret for the bucket
• Schedule it with cron, in example as following (remember to chmod u+x)
  • 5 */12 * * * /home/user/pgsql_dump/pgsql-dump_dbname.sh

This is a simple example on how to get this done. Modify as needed!