26.05.2023

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.

sudo nano /etc/bacula/pre-psql-backup.sh

Write the following commands to it.

#!/bin/bash
# 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.

#!/bin/bash
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.

Job {
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.

FileSet {
Name = "PSQL"
Include {
Options {
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.