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.
In the Serverspace you can create a server with already installed app "PostgreSQL".
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.
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.
Copy the following lines there.
rm -f /opt/psql-backup/*
Now let's make the scripts executable.
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"
Include {
Options {
signature = MD5
}
File = /opt/psql-backup/
}
}
Save and close the file, then restart Bacula to apply the changes.
Now the script will make dumps, and Baсula will write them to the ‘LocalSD’ storage according to the ‘LocalDaily’ schedule.