Simple and cost-effective PostgreSQL backup strategy — Combining Docker, rclone, S3, and Telegram notifications

In the ever-evolving landscape of web development, maintaining a reliable backup strategy for your database is crucial. This article explores a comprehensive, cost-effective approach to backing up PostgreSQL databases using a combination of Docker, rclone, S3 storage, and Telegram notifications.

This solution is particularly tailored for Django projects using the Cookiecutter template, but the principles can be applied to various setups. Throughout this article, I will explore a real-world implementation of this backup strategy in Similarix.com, a production system that has successfully adopted these practices.

My goal is to showcase a cost-effective and relatively simple system that can be adapted for projects of various scales.

Overview of the backup system

My backup system consists of several components working in tandem:

1. Docker-based PostgreSQL backups
2. rclone for syncing backups to encrypted S3 storage
3. Automated cleanup of old backups
4. System health checks
5. Telegram notifications for backup status and system health

Let’s examine each component and how they work together to create a robust backup solution.

Docker-based PostgreSQL backups

The first step in my backup process involves creating PostgreSQL dumps using Docker. This approach ensures consistency across different environments and simplifies the backup process.

Backup script

Here’s the core of my backup script:

#!/usr/bin/env bash

set -o errexit
set -o pipefail
set -o nounset

working_dir="$(dirname 

#!/usr/bin/env bash
set -o errexit
set -o pipefail
set -o nounset
working_dir="$(dirname ${0})"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"
message_welcome "Backing up the '${POSTGRES_DB}' database..."
export PGHOST="${POSTGRES_HOST}"
export PGPORT="${POSTGRES_PORT}"
export PGUSER="${POSTGRES_USER}"
export PGPASSWORD="${POSTGRES_PASSWORD}"
export PGDATABASE="${POSTGRES_DB}"
backup_filename="${BACKUP_FILE_PREFIX}_$(date +'%Y_%m_%dT%H_%M_%S').sql.gz"
pg_dump | gzip > "${BACKUP_DIR_PATH}/${backup_filename}"
message_success "'${POSTGRES_DB}' database backup '${backup_filename}' has been created and placed in '${BACKUP_DIR_PATH}'."
)"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"

message_welcome "Backing up the '${POSTGRES_DB}' database..."

export PGHOST="${POSTGRES_HOST}"
export PGPORT="${POSTGRES_PORT}"
export PGUSER="${POSTGRES_USER}"
export PGPASSWORD="${POSTGRES_PASSWORD}"
export PGDATABASE="${POSTGRES_DB}"

backup_filename="${BACKUP_FILE_PREFIX}_$(date +'%Y_%m_%dT%H_%M_%S').sql.gz"
pg_dump | gzip > "${BACKUP_DIR_PATH}/${backup_filename}"

message_success "'${POSTGRES_DB}' database backup '${backup_filename}' has been created and placed in '${BACKUP_DIR_PATH}'."

This script performs the following actions:

1. Sets bash options for error handling and variable usage
2. Sources common constants and message functions
3. Sets up PostgreSQL connection environment variables
4. Creates a timestamped backup filename
5. Executes pg_dump, compresses the output with gzip, and saves it to the backup directory

Scheduling backups

To automate the backup process, I use a cron job:

0 2 * * * cd /home/projects/similarix && docker compose -f production.yml exec -T postgres backup >> /home/projects/similarix/logs/postgres_backup.log 2>&1

This cron job runs daily at 2 AM, executing the backup script within the PostgreSQL Docker container and logging the output.

Syncing backups to encrypted S3 with rclone

After creating local backups, I use rclone to sync them to an encrypted S3-compatible storage service. This ensures off-site storage and adds an extra layer of data protection.

rclone configuration

First, I set up an rclone remote for our S3 storage:

rclone config
Name: backups_similarix_ovh
Type: s3
... credentials and additional setup ...

When configuring the S3 remote, I ensure that server-side encryption is enabled to protect the data at rest.

Sync script

Here’s my sync_backups.sh script:

#!/bin/bash

PROJECT_ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
BACKUP_DIR="${PROJECT_ROOT}/backups"
RCLONE_REMOTE="backups_similarix_ovh"
REMOTE_PATH="similarix-backups"

source "${PROJECT_ROOT}/.env"

# Delete backups older than 7 days
deleted_count=$(find "$BACKUP_DIR" -type f -mtime +7 -name "backup_*.sql.gz" -delete -print | wc -l)

# Sync the backups to the remote
rclone_output=$(rclone sync "$BACKUP_DIR" "${RCLONE_REMOTE}:${REMOTE_PATH}" \
    --progress \
    --exclude ".*" \
    --exclude "*/.*" \
    --stats-one-line)

# System checks
disk_usage=$(df -h / | awk 'NR==2 {print $5}')
ram_usage=$(free -m | awk 'NR==2 {printf "%.2f%%", $3*100/\}')
load_average=$(uptime | awk -F'load average:' '{print $2}' | xargs)

# Prepare and send Telegram message
message="✅ Backup sync completed.
$deleted_count files deleted
Rclone output: $rclone_output

System status:
Disk usage: $disk_usage
RAM usage: $ram_usage
Load average: $load_average"

curl -s -X POST "https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage" \
    -d chat_id="${TELEGRAM_CHANNEL_ID}" \
    -d text="${message}"

echo "$message"

This script performs several important tasks:

1. Deletes backups older than 7 days to manage local storage
2. Syncs the backup directory to the encrypted S3 storage using rclone
3. Performs basic system health checks
4. Sends a status message to a Telegram channel

Scheduling the sync

I schedule the sync process with another cron job:

0 4 * * * cd /home/projects/similarix && bash scripts/sync_backups.sh >> /home/projects/similarix/logs/backup_sync.log 2>&1

This job runs daily at 4 AM, two hours after the backup creation, ensuring that the most recent backup is included in the sync.

Managing backups

In addition to creating and syncing backups, my system includes scripts for managing backups:

Listing backups

#!/usr/bin/env bash

working_dir="$(dirname 

#!/usr/bin/env bash
working_dir="$(dirname ${0})"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"
message_welcome "These are the backups you have got:"
ls -lht "${BACKUP_DIR_PATH}"
)"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"

message_welcome "These are the backups you have got:"

ls -lht "${BACKUP_DIR_PATH}"

This script provides a simple way to list all available backups.

Removing backups

#!/usr/bin/env bash

working_dir="$(dirname 

#!/usr/bin/env bash
working_dir="$(dirname ${0})"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"
if [[ -z ${1+x} ]]; then
message_error "Backup filename is not specified yet it is a required parameter. Make sure you provide one and try again."
exit 1
fi
backup_filename="${BACKUP_DIR_PATH}/${1}"
if [[ ! -f "${backup_filename}" ]]; then
message_error "No backup with the specified filename found. Check out the 'backups' maintenance script output to see if there is one and try again."
exit 1
fi
message_welcome "Removing the '${backup_filename}' backup file..."
rm -r "${backup_filename}"
message_success "The '${backup_filename}' database backup has been removed."
)"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"

if [[ -z ${1+x} ]]; then
    message_error "Backup filename is not specified yet it is a required parameter. Make sure you provide one and try again."
    exit 1
fi
backup_filename="${BACKUP_DIR_PATH}/"
if [[ ! -f "${backup_filename}" ]]; then
    message_error "No backup with the specified filename found. Check out the 'backups' maintenance script output to see if there is one and try again."
    exit 1
fi

message_welcome "Removing the '${backup_filename}' backup file..."

rm -r "${backup_filename}"

message_success "The '${backup_filename}' database backup has been removed."

This script allows for the manual removal of specific backup files.

Restoring from backup

#!/usr/bin/env bash

working_dir="$(dirname 

#!/usr/bin/env bash
working_dir="$(dirname ${0})"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"
if [[ -z ${1+x} ]]; then
message_error "Backup filename is not specified yet it is a required parameter. Make sure you provide one and try again."
exit 1
fi
backup_filename="${BACKUP_DIR_PATH}/${1}"
if [[ ! -f "${backup_filename}" ]]; then
message_error "No backup with the specified filename found. Check out the 'backups' maintenance script output to see if there is one and try again."
exit 1
fi
message_welcome "Restoring the '${POSTGRES_DB}' database from the '${backup_filename}' backup..."
export PGHOST="${POSTGRES_HOST}"
export PGPORT="${POSTGRES_PORT}"
export PGUSER="${POSTGRES_USER}"
export PGPASSWORD="${POSTGRES_PASSWORD}"
export PGDATABASE="${POSTGRES_DB}"
message_info "Dropping the database..."
dropdb "${PGDATABASE}"
message_info "Creating a new database..."
createdb --owner="${POSTGRES_USER}"
message_info "Applying the backup to the new database..."
gunzip -c "${backup_filename}" | psql "${POSTGRES_DB}"
message_success "The '${POSTGRES_DB}' database has been restored from the '${backup_filename}' backup."
)"
source "${working_dir}/_sourced/constants.sh"
source "${working_dir}/_sourced/messages.sh"

if [[ -z ${1+x} ]]; then
    message_error "Backup filename is not specified yet it is a required parameter. Make sure you provide one and try again."
    exit 1
fi
backup_filename="${BACKUP_DIR_PATH}/"
if [[ ! -f "${backup_filename}" ]]; then
    message_error "No backup with the specified filename found. Check out the 'backups' maintenance script output to see if there is one and try again."
    exit 1
fi

message_welcome "Restoring the '${POSTGRES_DB}' database from the '${backup_filename}' backup..."

export PGHOST="${POSTGRES_HOST}"
export PGPORT="${POSTGRES_PORT}"
export PGUSER="${POSTGRES_USER}"
export PGPASSWORD="${POSTGRES_PASSWORD}"
export PGDATABASE="${POSTGRES_DB}"

message_info "Dropping the database..."
dropdb "${PGDATABASE}"

message_info "Creating a new database..."
createdb --owner="${POSTGRES_USER}"

message_info "Applying the backup to the new database..."
gunzip -c "${backup_filename}" | psql "${POSTGRES_DB}"

message_success "The '${POSTGRES_DB}' database has been restored from the '${backup_filename}' backup."

This script provides a method for restoring the database from a specific backup file.

System health checks and notifications

My backup system includes basic health checks to monitor the server’s status:

• Disk usage
• RAM usage
• Load average

These metrics are included in the Telegram notification sent after each backup sync, providing a quick overview of the system’s health alongside the backup status.

Conclusion

This comprehensive backup strategy combines several powerful tools and techniques to ensure data safety and system health:

1. Regular, automated PostgreSQL backups using Docker
2. Efficient syncing to encrypted S3 storage with rclone
3. Automated cleanup of old backups to manage storage
4. Basic system health monitoring
5. Real-time notifications via Telegram

By implementing this system, I’ve achieved a robust, cost-effective backup solution for my Django projects and PostgreSQL-based applications. The use of Docker ensures consistency across environments, rclone provides flexible and efficient off-site storage, and Telegram notifications keep me informed of backup status and system health.

I recommend regularly testing your backup and restore processes to ensure they function as expected. With this setup, you’ll have peace of mind knowing your data is securely backed up and easily recoverable in case of any unforeseen issues.