Learn Bacula - Database & Application Backup

#Introduction

Backing up static files is easy: copy, done. But databases and applications have one big enemy: inconsistency. Copying .ibd or postgresql.conf files while the database is writing is the fastest way to make a backup that can't be restored. In episode 10 we learn how to back up live workloads correctly.

The approach varies by edition: integrated application plugins in Enterprise, or script-based pre/post patterns in Community. And behind both lies the same principle — make the data consistent first, then copy.

#The Problem: Why Database Backup Is Complicated

A database never writes one big file; it writes to many files with internal buffers and WAL (write-ahead log). Copying files while transactions are in progress produces an inconsistent snapshot: half a transaction saved, half not. When the database is opened from this copy, it fails.

There are two ways out:

1. Make the database stop writing (drain + snapshot) then copy.
2. Dump — ask the database to export a consistent logical representation.

Bacula Enterprise provides both via plugins; Community relies on scripts that call the database's native tools.

#The Enterprise Approach: Plugins

Bacula Enterprise provides plugins that communicate directly with applications to ensure consistency:

• VMware / Hyper-V plugin — takes a VM snapshot, backs up the disks consistently, then releases the snapshot. Suited for fully virtualized environments.
• MySQL / PostgreSQL plugin — triggers a logical dump or uses the built-in consistency mechanism before the data is backed up.
• MSSQL plugin — uses VSS (Volume Shadow Copy) on Windows to ensure SQL Server database consistency.
• NDMP (Network Data Management Protocol) — integrates NAS/filers (NetApp, etc.) so the backup is done by the storage machines themselves without burdening the client network.

The plugin advantage: consistency is guaranteed, no manual scripts needed, and restore can be application-aware. The price: a commercial license and additional configuration on the application side.

#The Community Approach: Script-Based

In Community, the standard strategy is pre/post hooks triggered by Bacula jobs. Bacula runs commands through the ClientRunBeforeJob and ClientRunAfterJob directives in the Job resource:

#The pg_dump Pattern

For PostgreSQL, the most reliable pattern: dump to a file first, then back up the dump file.

#!/bin/bash
set -euo pipefail
BACKUP_DIR=/var/backup/pg_dump
mkdir -p "$BACKUP_DIR"
PGPASSWORD=rahasia pg_dump -h 127.0.0.1 -U bacula myapp > "$BACKUP_DIR/myapp.sql"

Job {
  Name = "Backup PostgreSQL"
  Type = Backup
  Client = client-fd
  FileSet = "Set PG Dump"
  ClientRunBeforeJob = "/etc/bacula/scripts/pg_dump.sh"
  ClientRunAfterJob = "/etc/bacula/scripts/pg_cleanup.sh"
  Storage = FileStorage
  Pool = FilePool
}

The flow: Bacula runs pg_dump.sh before it starts reading files → the dump result is backed up along with the FileSet → pg_cleanup.sh removes old dumps afterwards.

#The mysqldump Pattern

MySQL uses mysqldump the same way:

mysqldump --single-transaction -u backup -p'rahasia' myapp > /var/backup/mysql_dump/myapp.sql

--single-transaction is important: it takes a consistent snapshot without locking all tables.

#Cleanup Hooks

Don't let dumps pile up on the client — dump files are large duplicates. ClientRunAfterJob cleans them up:

#!/bin/bash
find /var/backup -name "*.sql" -mtime +1 -delete

#Consistency and Snapshot Modes

#Pre-Backup Hooks vs Snapshots

A pre-backup hook forces the application to "pause briefly" (making a dump). The snapshot approach differs: the application keeps running, but the filesystem photographs a consistent state (for example an LVM snapshot or VSS on Windows) that is then backed up.

lvcreate -L 10G -s -n snapsata /dev/vg0/dbdata

The snapshot that gets backed up is a static image; the application is never blocked. This is why Enterprise and VM vendors use snapshots: zero downtime, still consistent.

#Choosing Which One

• Pre-backup hooks (dumps) — simplest, suited for small-to-medium databases and narrow backup windows.
• Snapshots — for large workloads with strict uptime SLAs; needs extra space (copy-on-write) and filesystem understanding.

For Community without plugins, the ideal combination: pg_dump for databases + a regular FileSet for static files. For VMs, consider an Enterprise plugin or backup through an in-VM agent with dumps.

#Practice: One Job, Two Purposes

The complete pattern for a small application server:

FileSet {
  Name = "Set Aplikasi"
  Include {
    Options { signature = SHA1 }
    File = /opt/myapp
    File = /var/backup/pg_dump
  }
}

The dump lives in /var/backup/pg_dump and gets backed up along with everything else. After the job finishes, cleanup removes the dump — but the dump's contents are already safe on the Bacula volumes.

#Closing

Key takeaways:

• Database backup without consistency is a time bomb.
• Enterprise provides plugins: VMware, Hyper-V, MSSQL, MySQL/PostgreSQL agents, NDMP.
• Community uses the ClientRunBeforeJob/ClientRunAfterJob hooks + pg_dump/mysqldump.
• Use set -e in hook scripts so failures cancel the job instead of producing a defective backup.
• Snapshots (LVM/VSS) are a zero-downtime alternative for large workloads.
• Always test restoring a dump, not just making the backup.

In the next episode, episode 11, we'll manage tape and storage — tape device configuration (LTO), autoloaders/changers, slot labeling and tape rotation, comparing them with disk storage, and dedup strategies (Enterprise plugin) versus file-based. These are the media that make Bacula excel in long-term enterprise environments.