Backup and Restore¶

Automate PostgreSQL backups and restore BigBrotr from a snapshot.

Overview¶

BigBrotr stores all persistent data in PostgreSQL. Regular backups protect against data loss from hardware failure, accidental deletion, or failed upgrades. This guide covers automated backups with pg_dump and restore procedures.

1. Create a Backup Script¶

Create /opt/bigbrotr/backup.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR=/opt/bigbrotr/backups
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p "${BACKUP_DIR}"

docker compose exec -T postgres pg_dump -U admin -d bigbrotr \
    | gzip > "${BACKUP_DIR}/backup_${TIMESTAMP}.sql.gz"

# Remove backups older than 7 days
find "${BACKUP_DIR}" -name "backup_*.sql.gz" -mtime +7 -delete

echo "Backup completed: backup_${TIMESTAMP}.sql.gz"

Make it executable:

chmod +x /opt/bigbrotr/backup.sh

Manual deployment

If you are not using Docker, replace the docker compose exec line with a direct pg_dump call:

pg_dump -U admin -d bigbrotr | gzip > "${BACKUP_DIR}/backup_${TIMESTAMP}.sql.gz"

2. Schedule Automated Backups¶

Add a crontab entry to run the backup daily at 2:00 AM:

crontab -e

Add this line:

0 2 * * * cd /opt/bigbrotr/deployments/bigbrotr && /opt/bigbrotr/backup.sh >> /opt/bigbrotr/backups/backup.log 2>&1

Tip

Adjust the retention period in the backup script (-mtime +7) based on your storage capacity. For large databases, consider keeping weekly backups for 30 days.

3. Verify Backups¶

After a backup runs, verify the file is valid:

# Check the file exists and has a reasonable size
ls -lh /opt/bigbrotr/backups/

# Test decompression (without writing output)
gunzip -t /opt/bigbrotr/backups/backup_20260101_020000.sql.gz

# Inspect the first few lines
gunzip -c /opt/bigbrotr/backups/backup_20260101_020000.sql.gz | head -20

Warning

A zero-byte backup file indicates a connection failure. Check that PostgreSQL is running and the credentials are correct.

4. Restore from Backup¶

Stop application services¶

Stop all services that write to the database before restoring:

DockerSystemd

docker compose stop finder validator monitor synchronizer

sudo systemctl stop bigbrotr-finder bigbrotr-validator bigbrotr-monitor bigbrotr-synchronizer

Restore the database¶

DockerManual

gunzip -c backup.sql.gz | docker compose exec -T postgres psql -U admin -d bigbrotr

gunzip -c backup.sql.gz | psql -U admin -d bigbrotr

Refresh materialized views¶

After restoring, refresh all materialized views to ensure they reflect the restored data:

-- Connect to the database
-- Docker: docker compose exec postgres psql -U admin -d bigbrotr
-- Manual: psql -U admin -d bigbrotr

SELECT all_statistics_refresh();

This calls the stored function that refreshes all 11 materialized views concurrently.

Restart services¶