Backup & Restore

Protect your analytics data with comprehensive backup strategies. This guide covers backing up PostgreSQL (metadata), ClickHouse (analytics), and configuration.

What to Backup

Critical Data

1. PostgreSQL - User accounts, sites, API keys, settings
2. ClickHouse - Analytics events (largest dataset)
3. Environment variables - Configuration and secrets

Optional

4. Redis - Cache only, can be rebuilt
5. Application logs - For debugging/audit

Backup Strategies

Strategy 1: Simple Daily Backups (Recommended)

Best for: Small to medium deployments (less than 10M events/day)

• Frequency: Daily at 2 AM
• Retention: 7 daily, 4 weekly, 12 monthly
• Storage: Local + offsite (S3/Backblaze)

Strategy 2: Continuous Backups (Enterprise)

Best for: Large deployments (>10M events/day)

• PostgreSQL: WAL archiving + PITR
• ClickHouse: Incremental backups
• Retention: Point-in-time recovery for 30 days

Docker Compose Backup

Automated Backup Script

Create /scripts/backup.sh:

#!/bin/bash
set -euo pipefail

# Configuration
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d-%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p "$BACKUP_DIR/$DATE"

echo "Starting backup: $DATE"

# 1. Backup PostgreSQL
echo "Backing up PostgreSQL..."
docker-compose exec -T postgres pg_dump \
  -U ovyxa \
  -Fc ovyxa \
  > "$BACKUP_DIR/$DATE/postgres.dump"

# 2. Backup ClickHouse
echo "Backing up ClickHouse..."
docker-compose exec -T clickhouse clickhouse-client \
  --query "BACKUP DATABASE ovyxa TO Disk('backups', '$DATE/')"

# Copy ClickHouse backup from container
docker cp ovyxa-clickhouse:/var/lib/clickhouse/backups/$DATE \
  "$BACKUP_DIR/$DATE/clickhouse"

# 3. Backup environment
echo "Backing up configuration..."
cp .env "$BACKUP_DIR/$DATE/env.backup"

# 4. Create archive
echo "Creating compressed archive..."
cd "$BACKUP_DIR"
tar -czf "ovyxa-$DATE.tar.gz" "$DATE"
rm -rf "$DATE"

# 5. Upload to S3 (optional)
if [ -n "${AWS_S3_BUCKET:-}" ]; then
  echo "Uploading to S3..."
  aws s3 cp "ovyxa-$DATE.tar.gz" \
    "s3://$AWS_S3_BUCKET/backups/"
fi

# 6. Clean old backups
echo "Cleaning old backups..."
find "$BACKUP_DIR" -name "ovyxa-*.tar.gz" \
  -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ovyxa-$DATE.tar.gz"

Make executable:

chmod +x /scripts/backup.sh

Schedule with Cron

# Edit crontab
crontab -e

# Add daily backup at 2 AM
0 2 * * * /path/to/scripts/backup.sh >> /var/log/ovyxa-backup.log 2>&1

Manual Backup

# Run backup script
./scripts/backup.sh

# Backups saved to /backup/ovyxa-YYYYMMDD-HHMMSS.tar.gz

Kubernetes Backup

Using Velero (Recommended)

Install Velero

# AWS example
velero install \
  --provider aws \
  --plugins velero/velero-plugin-for-aws:v1.8.0 \
  --bucket ovyxa-backups \
  --secret-file ./credentials-velero \
  --backup-location-config region=us-east-1 \
  --snapshot-location-config region=us-east-1

Schedule Automated Backups

# Daily full backup at 2 AM
velero schedule create ovyxa-daily \
  --schedule="0 2 * * *" \
  --include-namespaces ovyxa \
  --ttl 168h  # 7 days retention

# Weekly backup at Sunday 3 AM
velero schedule create ovyxa-weekly \
  --schedule="0 3 * * 0" \
  --include-namespaces ovyxa \
  --ttl 720h  # 30 days retention

Manual Backup

# Create backup now
velero backup create ovyxa-manual-$(date +%Y%m%d) \
  --include-namespaces ovyxa

# Check status
velero backup describe ovyxa-manual-20250113

Alternative: CronJob Backup

Create backup-cronjob.yaml:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: ovyxa-backup
  namespace: ovyxa
spec:
  schedule: "0 2 * * *"  # 2 AM daily
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: ovyxa/backup:latest
            env:
            - name: AWS_S3_BUCKET
              value: ovyxa-backups
            - name: AWS_ACCESS_KEY_ID
              valueFrom:
                secretKeyRef:
                  name: aws-credentials
                  key: access-key-id
            - name: AWS_SECRET_ACCESS_KEY
              valueFrom:
                secretKeyRef:
                  name: aws-credentials
                  key: secret-access-key
            volumeMounts:
            - name: backup-script
              mountPath: /scripts
          restartPolicy: OnFailure
          volumes:
          - name: backup-script
            configMap:
              name: backup-script

Restore Procedures

Docker Compose Restore

1. Extract Backup

cd /backup
tar -xzf ovyxa-20250113-020000.tar.gz
cd 20250113-020000

2. Stop Services

docker-compose down

3. Restore PostgreSQL

# Start only postgres
docker-compose up -d postgres

# Wait for postgres to be ready
sleep 10

# Drop existing database (if exists)
docker-compose exec postgres psql -U postgres -c "DROP DATABASE IF EXISTS ovyxa"

# Create fresh database
docker-compose exec postgres psql -U postgres -c "CREATE DATABASE ovyxa OWNER ovyxa"

# Restore from dump
docker-compose exec -T postgres pg_restore \
  -U ovyxa \
  -d ovyxa \
  < postgres.dump

4. Restore ClickHouse

# Start ClickHouse
docker-compose up -d clickhouse
sleep 15

# Copy backup into container
docker cp clickhouse ovyxa-clickhouse:/var/lib/clickhouse/backups/

# Restore database
docker-compose exec clickhouse clickhouse-client \
  --query "RESTORE DATABASE ovyxa FROM Disk('backups', '.')"

5. Restore Configuration

# Restore env file
cp env.backup .env

# Verify and update if needed
nano .env

6. Start All Services

docker-compose up -d

# Check logs
docker-compose logs -f

Kubernetes Restore

Using Velero

# List available backups
velero backup get

# Restore from backup
velero restore create \
  --from-backup ovyxa-daily-20250113

# Monitor restore
velero restore describe ovyxa-daily-20250113
velero restore logs ovyxa-daily-20250113

Restore to Different Namespace

velero restore create \
  --from-backup ovyxa-daily-20250113 \
  --namespace-mappings ovyxa:ovyxa-restored

Database-Specific Procedures

PostgreSQL Dump & Restore

Dump

# Full database
pg_dump -U ovyxa -Fc ovyxa > backup.dump

# Specific tables only
pg_dump -U ovyxa -Fc -t users -t sites ovyxa > metadata.dump

# SQL format (human-readable)
pg_dump -U ovyxa --clean --if-exists ovyxa > backup.sql

Restore

# From custom format
pg_restore -U ovyxa -d ovyxa backup.dump

# From SQL format
psql -U ovyxa -d ovyxa < backup.sql

# Parallel restore (faster)
pg_restore -U ovyxa -d ovyxa -j 4 backup.dump

ClickHouse Dump & Restore

Dump (SQL)

# Export all tables as SQL
clickhouse-client --query "SHOW TABLES FROM ovyxa" | while read table; do
  clickhouse-client --query "SELECT * FROM ovyxa.$table FORMAT TabSeparated" \
    > "$table.tsv"
done

Restore (SQL)

# Import from TSV
for file in *.tsv; do
  table=$(basename "$file" .tsv)
  clickhouse-client --query "INSERT INTO ovyxa.$table FORMAT TabSeparated" \
    < "$file"
done

Native Backup (Faster)

# Backup
clickhouse-client --query "BACKUP DATABASE ovyxa TO Disk('backups', 'full/')"

# Restore
clickhouse-client --query "RESTORE DATABASE ovyxa FROM Disk('backups', 'full/')"

Offsite Storage

AWS S3

# Configure AWS CLI
aws configure

# Upload backup
aws s3 cp ovyxa-backup.tar.gz s3://your-bucket/backups/

# Download backup
aws s3 cp s3://your-bucket/backups/ovyxa-backup.tar.gz .

# List backups
aws s3 ls s3://your-bucket/backups/

Backblaze B2

# Install B2 CLI
pip install b2

# Authorize
b2 authorize-account <key_id> <application_key>

# Upload
b2 upload-file <bucket_name> ovyxa-backup.tar.gz backups/ovyxa-backup.tar.gz

# Download
b2 download-file-by-name <bucket_name> backups/ovyxa-backup.tar.gz ./

rsync (Remote Server)

# Push to remote server
rsync -avz --progress /backup/ user@remote:/backup/ovyxa/

# Pull from remote server
rsync -avz --progress user@remote:/backup/ovyxa/ /backup/

Backup Verification

Test Restore Procedure

Regularly test restores (monthly recommended):

# 1. Create test environment
docker-compose -f docker-compose.test.yml up -d

# 2. Restore backup to test environment
./scripts/restore.sh /backup/latest.tar.gz test

# 3. Verify data integrity
docker-compose -f docker-compose.test.yml exec api npm run verify:data

# 4. Cleanup
docker-compose -f docker-compose.test.yml down -v

Checksum Verification

# Create checksums
sha256sum ovyxa-backup.tar.gz > backup.sha256

# Verify integrity later
sha256sum -c backup.sha256

Disaster Recovery Plan

RTO & RPO Targets

• Recovery Time Objective (RTO): 2 hours
• Recovery Point Objective (RPO): 24 hours (daily backups)

DR Checklist

1. Backups exist: Verify latest backup < 24h old
2. Offsite copy: Backup stored in different region/provider
3. Tested restore: Successfully restored within 30 days
4. Documentation: Restore procedure documented and accessible
5. Access: Credentials/keys available to DR team
6. Infrastructure: Can provision new infrastructure quickly

Emergency Restore

# 1. Provision new infrastructure
# 2. Download latest backup from offsite
aws s3 cp s3://bucket/backups/latest.tar.gz .

# 3. Extract and restore
tar -xzf latest.tar.gz
./scripts/restore.sh

# 4. Update DNS
# 5. Verify functionality
# 6. Monitor logs

Backup Best Practices

1. Test restores regularly (monthly minimum)
2. Store backups offsite (3-2-1 rule: 3 copies, 2 media types, 1 offsite)
3. Encrypt backups (GPG or cloud provider encryption)
4. Monitor backup jobs (alert on failure)
5. Document procedures (runbooks for DR team)
6. Automate everything (scripts, not manual steps)
7. Verify integrity (checksums, test restores)

Troubleshooting

Backup Failing

Check disk space:

df -h /backup

Check Docker volumes:

docker system df

Restore Errors

Check logs:

docker-compose logs postgres
docker-compose logs clickhouse

Verify backup integrity:

tar -tzf backup.tar.gz  # List contents

Performance Issues

Backup taking too long:

• Use parallel dump for PostgreSQL
• Use incremental backups for ClickHouse
• Compress with faster algorithms (lz4 vs gzip)

Next Steps

• Configuration - Backup-related settings
• Docker Compose - Complete setup
• Kubernetes - HA deployment

Regular backups are essential for production deployments. Test your restore procedure before you need it.