Setting up isolated Btrfs subvolumes and snapshot policies on openSUSE Leap 15.5 for Secure DB Backups

Leveraging Btrfs Subvolumes for Atomic Database Backups

For enterprise environments demanding robust and atomic database backup strategies, Btrfs subvolumes offer a powerful, filesystem-level solution. This approach bypasses the complexities and potential race conditions inherent in file-level copying of live databases. By creating read-only snapshots of dedicated subvolumes containing database files, we achieve point-in-time consistency without impacting database operations. This guide details the setup on openSUSE Leap 15.5, focusing on isolating database data and implementing a basic snapshot rotation policy.

Prerequisites and Initial Btrfs Setup

This setup assumes you have a Btrfs filesystem already mounted. Typically, the root filesystem (`/`) on openSUSE Leap is Btrfs by default. We will create dedicated subvolumes for our database and its backups. Ensure you have sufficient disk space for both the database and its historical snapshots.

Creating Database and Backup Subvolumes

We’ll create two primary subvolumes: one for the actual database data and another for storing the snapshots. It’s crucial to mount these subvolumes at appropriate locations within your filesystem hierarchy. For this example, we’ll assume PostgreSQL is installed and its data directory will reside on a dedicated subvolume.

# Create the subvolume for database data
sudo btrfs subvolume create /srv/db/data

# Create the subvolume for storing snapshots
sudo btrfs subvolume create /srv/db/snapshots

# Verify creation
sudo btrfs subvolume list /srv/db

The output of btrfs subvolume list /srv/db should show entries for @/srv/db/data and @/srv/db/snapshots (or similar, depending on your mount point and naming conventions). It’s good practice to ensure these subvolumes are mounted automatically on boot. This is typically handled by /etc/fstab. If you created them directly on the root Btrfs volume, they are already part of the hierarchy. If you are mounting a separate Btrfs partition for /srv, you would add entries like:

UUID=your-btrfs-uuid /srv btrfs defaults,subvol=@srv,compress=zstd 0 0
UUID=your-btrfs-uuid /srv/db/data btrfs defaults,subvol=@srv/db/data,compress=zstd 0 0
UUID=your-btrfs-uuid /srv/db/snapshots btrfs defaults,subvolume=@srv/db/snapshots,compress=zstd 0 0

Replace your-btrfs-uuid with the actual UUID of your Btrfs partition. The subvol= option specifies which subvolume to mount at that path. The compress=zstd option is recommended for performance and space savings.

Configuring Database Data Directory

Now, we need to configure our database system (e.g., PostgreSQL) to use the newly created subvolume for its data. This involves stopping the database service, moving existing data (if any), and updating the database configuration.

PostgreSQL Example

First, stop the PostgreSQL service:

sudo systemctl stop postgresql

Next, move any existing data to the new subvolume. If this is a fresh installation, you can skip the move and just ensure the directory is empty.

# Assuming default data directory is /var/lib/pgsql/data
sudo mv /var/lib/pgsql/data/* /srv/db/data/
# Ensure correct ownership and permissions
sudo chown -R postgres:postgres /srv/db/data
sudo chmod -R 700 /srv/db/data

Update PostgreSQL’s configuration to point to the new data directory. This is typically done in postgresql.conf. Find the data_directory parameter and update it. On openSUSE, the main configuration file is often located at /var/lib/pgsql/data/postgresql.conf. However, since we moved the data, we need to tell PostgreSQL where to find it. The most robust way is to create a symlink or update the service unit file. A symlink is often simpler:

# Remove the old data directory link if it exists
sudo rm -rf /var/lib/pgsql/data

# Create a symbolic link to the new subvolume
sudo ln -s /srv/db/data /var/lib/pgsql/data

Alternatively, you can edit the PostgreSQL systemd service file (e.g., /usr/lib/systemd/system/postgresql.service or a drop-in file in /etc/systemd/system/postgresql.service.d/) to change the Environment=PGDATA= directive. For this example, the symlink is sufficient.

Finally, start the PostgreSQL service:

sudo systemctl start postgresql

Check the PostgreSQL logs (e.g., journalctl -u postgresql) to ensure it started correctly and is using the new data directory.

Implementing Snapshot Policies with Snapshots

With the database data isolated on its own subvolume, we can now create atomic, read-only snapshots. We’ll use a simple script to automate this process and implement a basic retention policy.

Snapshot Creation Script

Create a script, for example, /usr/local/bin/db_snapshot.sh, to handle snapshot creation. This script will create a new snapshot in the /srv/db/snapshots subvolume.

#!/bin/bash

# Configuration
DB_DATA_SUBVOLUME="/srv/db/data"
SNAPSHOT_DIR="/srv/db/snapshots"
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
SNAPSHOT_NAME="${TIMESTAMP}.snapshot"
FULL_SNAPSHOT_PATH="${SNAPSHOT_DIR}/${SNAPSHOT_NAME}"

# Ensure the snapshot directory is a subvolume (it should be)
if ! sudo btrfs subvolume show "${SNAPSHOT_DIR}" > /dev/null 2>&1; then
    echo "Error: ${SNAPSHOT_DIR} is not a Btrfs subvolume." >&2
    exit 1
fi

# Ensure the database data directory is a subvolume
if ! sudo btrfs subvolume show "${DB_DATA_SUBVOLUME}" > /dev/null 2>&1; then
    echo "Error: ${DB_DATA_SUBVOLUME} is not a Btrfs subvolume." >&2
    exit 1
fi

# Create the read-only snapshot
echo "Creating read-only snapshot: ${FULL_SNAPSHOT_PATH}"
if sudo btrfs subvolume snapshot -r "${DB_DATA_SUBVOLUME}" "${FULL_SNAPSHOT_PATH}"; then
    echo "Snapshot created successfully."
else
    echo "Error creating snapshot." >&2
    exit 1
fi

exit 0

Make the script executable:

sudo chmod +x /usr/local/bin/db_snapshot.sh

Snapshot Retention Policy

To prevent disk space exhaustion, we need to prune old snapshots. A common strategy is to keep a certain number of recent snapshots. We can extend the script or create a separate one for pruning.

#!/bin/bash

# Configuration
SNAPSHOT_DIR="/srv/db/snapshots"
KEEP_COUNT=7 # Keep the last 7 snapshots

# Ensure the snapshot directory is a subvolume
if ! sudo btrfs subvolume show "${SNAPSHOT_DIR}" > /dev/null 2>&1; then
    echo "Error: ${SNAPSHOT_DIR} is not a Btrfs subvolume." >&2
    exit 1
fi

# List snapshots, sort them, and delete older ones
# We use 'sort -r' to get the newest first, then skip the first 'KEEP_COUNT'
# and delete the rest.
SNAPSHOTS_TO_DELETE=$(sudo btrfs subvolume list -o "${SNAPSHOT_DIR}" | grep -oP '\d+ \K.*?(?=\s+id)' | sort | head -n -"${KEEP_COUNT}")

if [ -n "${SNAPSHOTS_TO_DELETE}" ]; then
    echo "Deleting old snapshots:"
    echo "${SNAPSHOTS_TO_DELETE}"
    echo "${SNAPSHOTS_TO_DELETE}" | while read -r snapshot; do
        FULL_PATH="${SNAPSHOT_DIR}/${snapshot}"
        echo "Deleting: ${FULL_PATH}"
        if sudo btrfs subvolume delete "${FULL_PATH}"; then
            echo "Deleted successfully."
        else
            echo "Error deleting ${FULL_PATH}." >&2
        fi
    done
else
    echo "No old snapshots to delete."
fi

exit 0

Save this as /usr/local/bin/db_prune_snapshots.sh and make it executable:

sudo chmod +x /usr/local/bin/db_prune_snapshots.sh

Automating with Cron

Schedule these scripts using cron. For example, to create a snapshot daily at 02:00 AM and prune snapshots weekly (keeping 7 days worth):

# Edit the root user's crontab
sudo crontab -e

# Daily snapshot at 2 AM
0 2 * * * /usr/local/bin/db_snapshot.sh >> /var/log/db_snapshot.log 2>&1

# Weekly prune (e.g., Sunday at 3 AM) - adjust KEEP_COUNT in script for desired retention
0 3 * * 0 /usr/local/bin/db_prune_snapshots.sh >> /var/log/db_prune_snapshots.log 2>&1

Ensure the log files are created and have appropriate permissions if needed.

Restoring from a Snapshot

Restoring a database from a Btrfs snapshot is a critical operation. The process involves stopping the database, mounting the snapshot, and then either copying data back or, more elegantly, promoting the snapshot to become the new live data directory.

Promoting a Snapshot

This method is generally preferred as it ensures consistency. It involves stopping the database, unmounting the current data directory (if it’s on a separate mount point, otherwise it’s part of the root filesystem), and then making the snapshot the new live data directory.

# 1. Stop the database service
sudo systemctl stop postgresql

# 2. Identify the snapshot to restore (e.g., the latest one)
LATEST_SNAPSHOT=$(sudo btrfs subvolume list -o /srv/db/snapshots | grep -oP '\d+ \K.*?(?=\s+id)' | sort -r | head -n 1)
if [ -z "${LATEST_SNAPSHOT}" ]; then
    echo "No snapshots found in ${SNAPSHOT_DIR}."
    exit 1
fi
SNAPSHOT_TO_RESTORE="/srv/db/snapshots/${LATEST_SNAPSHOT}"
echo "Restoring from snapshot: ${SNAPSHOT_TO_RESTORE}"

# 3. Ensure the current data directory is not in use.
#    If /var/lib/pgsql/data is a symlink to /srv/db/data, we can simply
#    remove the symlink and replace it with a new one pointing to the snapshot.
#    If /srv/db/data is directly mounted, this becomes more complex and might
#    require unmounting /srv/db/data if it's on a separate partition.
#    For simplicity, assuming the symlink approach:

# Remove the existing symlink
if [ -L /var/lib/pgsql/data ]; then
    echo "Removing existing symlink /var/lib/pgsql/data"
    sudo rm /var/lib/pgsql/data
else
    echo "Warning: /var/lib/pgsql/data is not a symlink. Manual intervention may be required."
    # If it's not a symlink, you might need to move the current data aside
    # sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.old_$(date +%s)
fi

# 4. Create a new read-write snapshot from the read-only snapshot
#    This is crucial: Btrfs snapshots are read-only by default. To use it
#    as a live data directory, we need a writable copy.
RESTORE_RW_SUBVOLUME="/srv/db/data.restored" # Temporary name
echo "Creating writable copy from snapshot..."
if sudo btrfs subvolume snapshot -r "${SNAPSHOT_TO_RESTORE}" "${RESTORE_RW_SUBVOLUME}"; then
    echo "Writable copy created: ${RESTORE_RW_SUBVOLUME}"
    # Now, make this the actual data directory
    echo "Making ${RESTORE_RW_SUBVOLUME} the new data directory."
    sudo mv "${RESTORE_RW_SUBVOLUME}" /srv/db/data
    sudo chown -R postgres:postgres /srv/db/data
    sudo chmod -R 700 /srv/db/data
else
    echo "Error creating writable copy from snapshot." >&2
    exit 1
fi

# 5. Recreate the symlink to the new data directory
echo "Recreating symlink /var/lib/pgsql/data"
sudo ln -s /srv/db/data /var/lib/pgsql/data

# 6. Start the database service
echo "Starting PostgreSQL service..."
sudo systemctl start postgresql

# 7. Verify
echo "Verifying PostgreSQL status..."
sudo systemctl status postgresql
echo "Check logs for errors: journalctl -u postgresql"

Important Considerations for Restoration:

• The btrfs subvolume snapshot -r ... command creates a read-only snapshot. To use it as a live data directory, it must be writable. The method above creates a new writable subvolume from the read-only snapshot.
• Ensure the ownership and permissions on the restored data directory are correct for the database user.
• Always test your restore procedure in a non-production environment.
• If your database data directory is on a separate Btrfs partition, you might need to unmount it before replacing it with the restored data.

Advanced Considerations and Next Steps

This setup provides a solid foundation. For production systems, consider:

• Offsite Backups: Replicate snapshots to an offsite location using tools like rsync or Btrfs’s own send/receive functionality.
• Monitoring: Implement robust monitoring for snapshot creation failures, disk space usage, and database health.
• Snapshot Granularity: Adjust the snapshot frequency and retention policy based on your Recovery Point Objective (RPO).
• Database-Specific Backups: While Btrfs snapshots are atomic for the filesystem, for certain databases, performing a logical backup (e.g., pg_dump) in addition to filesystem snapshots can provide an extra layer of safety and flexibility.
• Btrfs Scrubbing: Regularly run btrfs scrub start /path/to/btrfs/mountpoint to detect and correct silent data corruption.

By integrating Btrfs subvolumes and snapshots into your database backup strategy, you gain a powerful, efficient, and reliable method for ensuring data integrity and rapid recovery.