SLOzy

Appearance

Backup & Recovery ​

This document covers backup procedures for SLOzy's PostgreSQL database and migration management.

PostgreSQL Backup ​

Using pg_dump ​

Create a full backup:

bash
pg_dump -h localhost -U slozy_user -d slozy_db -F c -f slozy_backup_$(date +%Y%m%d).dump

For large databases, use parallel dump with multiple jobs:

bash
pg_dump -h localhost -U slozy_user -d slozy_db -j 4 -F d -f slozy_backup_dir/

Using pg_restore ​

Restore from a custom-format dump:

bash
pg_restore -h localhost -U slozy_user -d slozy_db --clean --if-exists slozy_backup_20260611.dump

Restore with parallel workers:

bash
pg_restore -h localhost -U slozy_user -d slozy_db -j 4 --clean --if-exists slozy_backup_20260611.dump

Automated Backup Script ​

bash
#!/bin/bash
BACKUP_DIR="/backups/slozy"
DB_NAME="slozy_db"
DB_USER="slozy_user"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR
pg_dump -F c -U $DB_USER -d $DB_NAME -f "$BACKUP_DIR/slozy_$TIMESTAMP.dump"

# Rotate backups older than 30 days
find $BACKUP_DIR -name "slozy_*.dump" -mtime +30 -delete

Migrations ​

SLOzy uses golang-migrate/migrate for database schema migrations (internal/db/migrations.go).

Running Migrations ​

go
func RunMigrations(databaseURL string) error {
    m, err := migrate.New("file://migrations", databaseURL)
    // ...
    return m.Up()
}

Migration Commands ​

Apply all pending migrations (handled automatically on application startup):

bash
# Via application startup:
./slozy-web
# Migrations run automatically via RunMigrations()

Manual migration commands:

bash
# Apply up migrations
migrate -path migrations/ -database "postgres://slozy_user:password@localhost:5432/slozy_db?sslmode=disable" up

# Rollback one migration
migrate -path migrations/ -database "postgres://slozy_user:password@localhost:5432/slozy_db?sslmode=disable" down 1

Migration Files ​

All 18 migration pairs are located in migrations/:

000001_organizations.up.sql
000001_organizations.down.sql
...
000018_query_cache.up.sql
000018_query_cache.down.sql

Schema Migrations Table ​

The schema_migrations table tracks applied migration versions. If a migration fails:

  1. Check the dirty version in schema_migrations
  2. Manually fix the failing migration SQL
  3. Mark the migration as applied or force-clean the dirty flag:
    sql
    UPDATE schema_migrations SET dirty = false WHERE version = <version>;

Kubernetes CronJob Backup ​

From docs/deployment.md — automated backup using a K8s CronJob:

yaml
apiVersion: batch/v1
kind: CronJob
metadata:
  name: slozy-db-backup
  namespace: slozy
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: pg-dump
            image: postgres:16-alpine
            command:
            - /bin/sh
            - -c
            - |
              pg_dump -h postgres-service -U slozy_user \
                -d slozy_db -F c \
                -f /backups/slozy_$(date +%Y%m%d).dump
            env:
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: slozy-secrets
                  key: database-password
            volumeMounts:
            - name: backup-storage
              mountPath: /backups
          restartPolicy: OnFailure
          volumes:
          - name: backup-storage
            persistentVolumeClaim:
              claimName: backup-pvc

Recovery Checklist ​

  1. Stop the application to prevent writes during restore
  2. Drop and recreate the target database
  3. Run pg_restore with the latest valid backup
  4. Verify the schema_migrations table matches expected state
  5. Start the application and run health checks
  6. Verify SLO data integrity via the dashboard