How to Create a Backup Script for PostgreSQL and MariaDB Containers on a Server

When running databases like PostgreSQL and MariaDB on a server, ensuring regular backups is crucial for protecting your data from unexpected events such as crashes, human error, or system failure. While there are several ways to create backups, scripting a backup solution gives you complete control and automation.

In this article, we'll show you how to create a simple yet effective backup script for PostgreSQL and MariaDB running in Docker containers on a server. We'll automate the process to ensure that your databases are regularly backed up without you needing to manually intervene.

Why You Need Backups?

Before diving into the script, let’s take a moment to highlight why having regular backups is essential:

1. Protection Against Data Loss: Regular backups prevent loss of valuable data due to failures or accidents.
2. Disaster Recovery: Having up-to-date backups makes it easier to restore services quickly after a failure.
3. Compliance and Audits: Some industries require maintaining backup copies of data for regulatory compliance.
4. Peace of Mind: Knowing your data is safe provides confidence and good sleep.

Prerequisites

Before creating the backup script, make sure you have:

• A VPS running a Linux-based operating system.
• Docker installed and running.
• PostgreSQL and MariaDB running inside Docker containers.
• Basic knowledge of using the terminal and running Docker commands.

For the purpose of this tutorial, let’s assume your PostgreSQL and MariaDB containers are named postgres_container and mariadb_container.

Create a Backup Directory

Start by creating a directory on your server where the backups will be stored. This will help keep everything organized.

bash
Copy code
mkdir -p /home/youruser/backup

Replace /home/youruser/backup with the location where you'd like to store your backups.

Create the Backup Script

Now let’s create a bash script that will run daily backups for both PostgreSQL and MariaDB databases. Open your favorite text editor and create a file named backup_databases.sh.

bash
Copy code
#!/bin/bash
set -euo pipefail

# Backup a PostgreSQL database into a daily file.

BACKUP_DIR="/data/backups"
LOG_FILE="/var/log/db_backup.log"
DAYS_TO_KEEP=30
POSTGRESDATABASES=("db1" "db2") # PostgreSQL DBs to backup
MARIADBDATABASES=("db1") # MariaDB DBs to backup
POSTGRESCONTAINER="postgres_container"
MARIADBCONTAINER="mariadb_container"

# Create necessary directories
mkdir -p "${BACKUP_DIR}"
mkdir -p "$(dirname "${LOG_FILE}")"

# Create backup directory if it doesn't exist
mkdir -p "${BACKUP_DIR}"

# Function to log messages
log() {
    local level="$1"
    local message="$2"
    local timestamp
    timestamp=$(date +"%Y-%m-%d %H:%M:%S")

    # Log to both stdout and a log file
    echo -e "${timestamp} [${level}] ${message}" | tee -a "${LOG_FILE}"
}

for DATABASE in "${POSTGRESDATABASES[@]}"; do
    TIMESTAMP=$(date +"%Y%m%d%H%M")
    FILE="${TIMESTAMP}_${DATABASE}.sql.gz"
    OUTPUT_FILE="${BACKUP_DIR}/${FILE}"

    log "INFO" "Starting backup for database: ${DATABASE}"

    # Perform the backup and compress the output
    if docker exec -i "${POSTGRESCONTAINER}" /usr/bin/pg_dump -U "${USER}" "${DATABASE}" | gzip -9 > "${OUTPUT_FILE}"; then
        log "SUCCESS" "Backup created: ${OUTPUT_FILE}"
        ls -l "${OUTPUT_FILE}" | tee -a "${LOG_FILE}"
    else
        log "ERROR" "Backup failed for database ${DATABASE}" >&2
        continue
    fi

    # Prune old backups
    find "${BACKUP_DIR}" -maxdepth 1 -mtime +"${DAYS_TO_KEEP}" -name "*${DATABASE}.sql.gz" -exec rm -f {} \; \
        && log "INFO" "Old backups deleted for database ${DATABASE}" \
        || log "ERROR" "Failed to delete old backups for ${DATABASE}" >&2
done

## MariaDB Backup
for DATABASE in ${MARIADBDATABASES[@]}; do
    TIMESTAMP=$(date +"%Y%m%d%H%M")
    FILE="${TIMESTAMP}_${DATABASE}.sql.gz"
    OUTPUT_FILE="${BACKUP_DIR}/${FILE}"

    # Perform the database backup (dump)
    if docker exec ${MARIADBCONTAINER} /usr/bin/mariadb-dump -u  root --password=yourpassword ${DATABASE} | gzip -9 >  ${OUTPUT_FILE}; then
        log "SUCCESS" "Backup created: ${OUTPUT_FILE}"
        ls -l "${OUTPUT_FILE}" | tee -a "${LOG_FILE}"
    else
        log "ERROR" "Backup failed for database ${DATABASE}" >&2
        continue
    fi

    # Prune old backups
    find "${BACKUP_DIR}" -maxdepth 1 -mtime +"${DAYS_TO_KEEP}" -name "*${DATABASE}.sql.gz" -exec rm -f {} \; \
        && log "INFO" "Old backups deleted for database ${DATABASE}" \
        || log "ERROR" "Failed to delete old backups for ${DATABASE}" >&2
done

log "INFO" "Finished database backups!"

Explanation:

• Variables: The script starts by defining a backup directory and more parameters like days to keep backups and individual databases to backup.
• PostgreSQL Backup: It uses docker exec to run the pg_dump command inside the PostgreSQL container to dump previously defined databases.
• MariaDB Backup: Similarly, it uses docker exec to run the mysqldump inside the MariaDB container and backup previously defined databases.
• Compression: All backups are compressed using gzip to save space.
• Backup Cleanup: Finally, the script deletes backups older than DAYS_TO_KEEP days to prevent disk space issues.

Customizing the Script:

• Change BACKUP_DIR to the directory where you want to store your backups.
• Replace DAYS_TO_KEEP with the days how long you want to keep backups.
• Replace POSTGRESDATABASES with the PostgreSQL databases to backup.
• Replace MARIADBDATABASES with the MariaDB databases to backup.
• Replace POSTGRESCONTAINER and MARIADBCONTAINER with the name of your PostgreSQL and MariaDB containers.
• Replace yourpassword with the password for the root user in MariaDB..

Make the Script Executable

After saving the script, make it executable:

bash
Copy code
chmod +x /path/to/backup_databases.sh

Automate the Backup with Cron

To schedule automatic backups, set up a cron job.

• Open the crontab editor:

bash
Copy code
crontab -e

• Add the following line to run the backup script daily at 2:00 AM:

bash
Copy code
0 2 * * * /path/to/backup_databases.sh

This will execute the backup script every day at 2:00 AM.

Make sure to adjust the path /path/to/backup_databases.sh to the correct location of your script.

Verify the Backup

It’s always a good idea to manually run the backup script once to ensure everything is working correctly.

bash
Copy code
/path/to/backup_databases.sh

Check the backup directory to ensure that the backup files have been created and compressed.

Restore from Backup (Bonus)

In case you need to restore a backup, you can use the following commands to load the backups back into your PostgreSQL and MariaDB containers.

PostgreSQL Restore:

bash
Copy code
docker exec -i postgres_container psql -U postgres -d database < /home/youruser/backup/DATE_DATABASE.sql

MariaDB Restore:

bash
Copy code
docker exec -i mariadb_container mariadb u root --password=yourpassword database < /home/youruser/backup/DATE_DATABASE.sql

Replace DATE and DATABASE with the appropriate backup file’s date and database name

By following these steps, you've created a simple and automated backup solution for your PostgreSQL and MariaDB databases running inside Docker containers. Regular backups are essential for protecting your data, and this script ensures that your backups run smoothly without manual intervention. You can also use this script to backup databases e.g. in Unraid with the User Scriptplugin.

You can further enhance this backup strategy by sending notifications, backing up to remote storage (e.g., AWS S3 or Google Cloud), or setting up encryption for additional security.

With your databases securely backed up, you can rest easy knowing your data is safe and easily recoverable in case of an emergency.