Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

8.6 KB
Raw Blame

Database backups

These scripts create database-consistent backups for the three production hosts. They intentionally do not archive live Docker volumes.

Host map

Host PostgreSQL ClickHouse
FrogStats Native pg_dump to B2 Native BACKUP directly to B2
Rybbit Native pg_dump to B2 Not backed up here; production uses the standalone host
ClickHouse None Native BACKUP staged locally and copied to B2

PostgreSQL dumps include a custom-format database archive, cluster globals, and SHA-256 checksums. ClickHouse backs up only the analytics database, not the large and regenerable system log tables.

The ClickHouse client receive timeout is raised to six hours because native backups can produce no client-visible data for longer than the client's five-minute default while the server is still making progress.

Prerequisites

All hosts need Bash, Docker, flock, curl, and rclone. The clickhouse container must provide clickhouse-client; the PostgreSQL container already provides pg_dump, pg_dumpall, and pg_restore.

Create a private B2 bucket, enable server-side encryption, and optionally enable a 7-14 day Object Lock. Give each host a separate application key restricted to that host's prefix. Configure lifecycle rules in B2 rather than giving the server credentials permission to delete old remote backups.

For PostgreSQL and locally staged ClickHouse backups, configure rclone as root:

sudo rclone config
sudo chmod 600 /root/.config/rclone/rclone.conf

The examples use a Backblaze remote named backblaze. Data is protected in transit by TLS and at rest by B2 server-side encryption. The direct FrogStats ClickHouse backup is written through ClickHouse's S3-compatible client. Its B2 credentials live in a ClickHouse named collection rather than the backup SQL.

Install on each host

From a checkout of this repository:

sudo install -d -m 0755 /opt/rybbit-backups/bin
sudo install -m 0755 ops/backups/bin/backup-postgres.sh /opt/rybbit-backups/bin/
sudo install -m 0755 ops/backups/bin/backup-clickhouse.sh /opt/rybbit-backups/bin/
sudo install -m 0644 ops/backups/bin/common.sh /opt/rybbit-backups/bin/

Install the appropriate configuration without committing secrets:

# Choose frogstats.env.example, rybbit.env.example, or clickhouse.env.example.
sudo install -m 0600 ops/backups/config/HOST.env.example /etc/rybbit-backup.env
sudoedit /etc/rybbit-backup.env

The configuration file is sourced by Bash. Quote values containing $, spaces, single quotes, or other shell metacharacters.

Validate configuration without touching either database or remote storage:

sudo env BACKUP_CONFIG=/etc/rybbit-backup.env DRY_RUN=true \
  /opt/rybbit-backups/bin/backup-postgres.sh
sudo env BACKUP_CONFIG=/etc/rybbit-backup.env DRY_RUN=true \
  /opt/rybbit-backups/bin/backup-clickhouse.sh

Only run the script relevant to the host. The Rybbit application host does not have ClickHouse backup settings.

FrogStats ClickHouse named collection

The direct S3 backup requires a named collection containing its B2 endpoint and restricted application key. Install the template outside the repository:

sudo install -d -m 0750 /etc/rybbit-backup
sudo install -m 0640 -o root -g 101 \
  ops/backups/clickhouse/s3-named-collection.xml.example \
  /etc/rybbit-backup/clickhouse-s3.xml
sudoedit /etc/rybbit-backup/clickhouse-s3.xml

Create a dedicated backup-user password, store its hash in the user XML, and store only the plaintext password in the root-readable backup environment:

backup_password="$(openssl rand -hex 32)"
backup_password_hash="$(printf '%s' "$backup_password" | sha256sum | cut -d' ' -f1)"

sudo install -m 0640 -o root -g 101 \
  ops/backups/clickhouse/backup-user.xml \
  /etc/rybbit-backup/clickhouse-backup-user.xml
sudo sed -i \
  "s/REPLACE_WITH_SHA256_PASSWORD/${backup_password_hash}/" \
  /etc/rybbit-backup/clickhouse-backup-user.xml
sudo sed -i \
  "s/^CLICKHOUSE_BACKUP_PASSWORD=.*/CLICKHOUSE_BACKUP_PASSWORD=${backup_password}/" \
  /etc/rybbit-backup.env
unset backup_password backup_password_hash

Then mount it into the FrogStats ClickHouse container:

services:
  clickhouse:
    volumes:
      - /etc/rybbit-backup/clickhouse-s3.xml:/etc/clickhouse-server/config.d/backup-s3.xml:ro
      - /etc/rybbit-backup/clickhouse-backup-user.xml:/etc/clickhouse-server/users.d/backup-user.xml:ro

UID/GID 101 is the default ClickHouse user in the official image; confirm it before installation if the image changes. Recreate ClickHouse and verify the dedicated user's grants without printing the named collection contents:

docker exec clickhouse clickhouse-client \
  --user backup --query "SHOW GRANTS FOR backup"

The backup user is restricted to connections originating inside the ClickHouse container. It can back up analytics, use the B2 named collection, and access S3; it cannot read or mutate application data through ordinary SQL. The FrogStats environment example selects this user for backups.

Standalone ClickHouse staging disk

The standalone host creates one native backup and uploads the completed snapshot to B2. Install the disk configuration outside the repository and prepare the host staging directory:

sudo install -d -m 0750 /etc/rybbit-backup
sudo install -m 0640 -o root -g 101 \
  ops/backups/clickhouse/backup-disk.xml \
  /etc/rybbit-backup/clickhouse-backup-disk.xml
sudo install -d -m 0750 -o 101 -g 101 /var/backups/rybbit/clickhouse

Then add these mounts to its ClickHouse service:

services:
  clickhouse:
    volumes:
      - /var/backups/rybbit/clickhouse:/var/lib/clickhouse/backups
      - /etc/rybbit-backup/clickhouse-backup-disk.xml:/etc/clickhouse-server/config.d/backup-disk.xml:ro

UID/GID 101 is the default ClickHouse user in the official image; confirm it if the image changes. Restart ClickHouse after adding the disk configuration, then confirm it exists:

docker exec clickhouse clickhouse-client \
  --query "SELECT name, path FROM system.disks WHERE name = 'backups'"

FrogStats uses direct S3 mode and does not need this staging disk.

Test manually, then enable timers

Run the relevant service manually before enabling its timer:

sudo install -m 0644 ops/backups/systemd/*.service ops/backups/systemd/*.timer /etc/systemd/system/
sudo systemctl daemon-reload

sudo systemctl start rybbit-postgres-backup.service
sudo journalctl -u rybbit-postgres-backup.service -n 100 --no-pager

# FrogStats and the standalone ClickHouse host only:
sudo systemctl start rybbit-clickhouse-backup.service
sudo journalctl -u rybbit-clickhouse-backup.service -n 100 --no-pager

After verifying objects at every configured destination:

sudo systemctl enable --now rybbit-postgres-backup.timer
# FrogStats and the standalone ClickHouse host only:
sudo systemctl enable --now rybbit-clickhouse-backup.timer

The PostgreSQL timer runs every six hours. The ClickHouse timer runs daily with a randomized delay. Use systemd timer overrides if the two ClickHouse hosts should run in different windows.

B2 retention should be implemented with bucket lifecycle rules, especially when Object Lock is enabled. Never add an Object-Locked B2 remote to CLICKHOUSE_PRUNE_REMOTES.

Restore drills

Always restore into a disposable container or a differently named database first. Never test a restore against production.

For PostgreSQL, download one backup directory through the B2 remote, verify it, restore globals, and then restore the custom archive:

cd /var/tmp/postgres-restore
sha256sum --check SHA256SUMS
docker exec -i postgres sh -ec \
  'exec psql -U "$POSTGRES_USER" -d postgres' < globals.sql
docker exec postgres sh -ec \
  'exec createdb -U "$POSTGRES_USER" analytics_restore'
docker exec -i postgres sh -ec \
  'exec pg_restore -U "$POSTGRES_USER" --dbname analytics_restore --clean --if-exists' \
  < database.dump

Review the globals SQL before applying it when restoring into a shared PostgreSQL cluster.

For a direct S3 ClickHouse backup, use the installed named collection and a different destination database name:

SET log_queries = 0;
RESTORE DATABASE analytics AS analytics_restore
FROM S3(b2_backups, 'BACKUP_DIRECTORY');

For a locally staged ClickHouse backup, download the complete backup directory to the configured host staging root and restore it from the backup disk:

RESTORE DATABASE analytics AS analytics_restore
FROM Disk('backups', 'BACKUP_DIRECTORY');

Perform and record a restore drill at least monthly. A successful upload is not proof that the backup can be restored.