How to Backup PostgreSQL on Ubuntu Server 20.04 with Bacula
The installation and initial configuration of Bacula is described here: How to Backup Ubuntu Server 20.04 with Bacula. In this tutorial, we will add settings for creating a PostgreSQL database backup.
First, you need to back up the database using the built-in DBMS tool, and then copy the result-ing file. Bacula allows you to run scripts before and after the backup process.
Step 1 — Preparing and creating scripts
First, let’s create a pre-psql-backup.sh script in the /etc/bacula/ folder that will make the dump.
sudo nano /etc/bacula/pre-psql-backup.sh
Write the following commands to it.
# Clearing, creating, and configuring a folder for the dump
rm -rf /opt/psql-backup/
mkdir -p /opt/psql-backup/
chown -R postgres:postgres /opt/psql-backup/
# Backup all PostgreSQL databases in SQL format
sudo -u postgres pg_dumpall --clean --inserts --verbose --file=/opt/psql-backup/$(date +%Y-%m-%d_%H:%M).sql 2>/var/log/bacula-pg_dump.log
Let’s create a script for cleaning the temporary folder.
sudo nano /etc/bacula/post-psql-backup.sh
Copy the following lines there.
rm -f /opt/psql-backup/*
Now let’s make the scripts executable.
chmod 750 /etc/bacula/pre-psql-backup.sh
chmod 750 /etc/bacula/post-psql-backup.sh
Step 2 – Configuring Bacula
The next section will describe our Job. Paste it into the /etc/bacula/bacula-dir.conf file.
Name = "PSQL-backup"
JobDefs = "DefaultJob"
Enabled = yes
Level = Full
FileSet = "PSQL"
Schedule = "LocalDaily"
Storage = LocalSD
Write Bootstrap = "/var/lib/bacula/PSQLBackup.bsr"
# Executing scripts
ClientRunBeforeJob = "/etc/bacula/pre-psql-backup.sh"
ClientRunAfterJob = "/etc/bacula/post-psql-backup.sh"
Now the section describing the target folder for backup.
Name = "PSQL"
signature = MD5
File = /opt/psql-backup/
Save and close the file, then restart Bacula to apply the changes.
systemctl reload bacula-dir
Now the script will make dumps, and Baсula will write them to the ‘LocalSD’ storage according to the ‘LocalDaily’ schedule.