PostgreSQL Agent

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: myapp
    volumes:
      - pg_data:/var/lib/postgresql/data

  lox-pg-agent:
    image: loxbackup/postgresql-agent:latest
    environment:
      LOX_API_KEY: ${LOX_API_KEY}
      PGHOST: postgres
      PGPORT: 5432
      PGUSER: myapp
      PGPASSWORD: ${POSTGRES_PASSWORD}
      PGDATABASE: myapp
      BACKUP_SCHEDULE: "0 3 * * *"
      BACKUP_NAME: "myapp-postgres"
    depends_on:
      - postgres

volumes:
  pg_data:

# Download the agent
curl -L https://releases.backlox.com/postgresql-agent/latest/linux-amd64 -o lox-pg-agent
chmod +x lox-pg-agent

# Configure
export LOX_API_KEY="lox_xxxxxxxxxxxx"
export PGHOST="localhost"
export PGUSER="backup_user"
export PGPASSWORD="secure_password"
export PGDATABASE="myapp"

# Run a backup
./lox-pg-agent backup

# Or run as daemon with schedule
./lox-pg-agent daemon --schedule "0 3 * * *"

-- For logical backups (pg_dump)
CREATE ROLE lox_backup WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO lox_backup;
GRANT USAGE ON SCHEMA public TO lox_backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lox_backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO lox_backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO lox_backup;

-- For backing up all databases
ALTER ROLE lox_backup WITH SUPERUSER;  -- Or use pg_read_all_data role in PG14+

-- PostgreSQL 14+ alternative (more secure)
GRANT pg_read_all_data TO lox_backup;

-- For physical backups (pg_basebackup)
ALTER ROLE lox_backup WITH REPLICATION;

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    BACKUP_TYPE: physical
    BACKUP_SCHEDULE: "0 2 * * *"

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    BACKUP_TYPE: physical
    # Enable WAL archiving
    WAL_ARCHIVING: "true"
    WAL_ARCHIVE_SCHEDULE: "*/5 * * * *"  # Every 5 minutes
    # Full backup weekly
    BACKUP_SCHEDULE: "0 3 * * 0"

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    PGDATABASE: myapp
    BACKUP_FORMAT: directory
    PARALLEL_JOBS: 8  # Use 8 parallel workers
    BACKUP_NAME: "myapp-parallel"
    BACKUP_SCHEDULE: "0 */4 * * *"

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    PGDATABASE: analytics
    # Skip large audit/log tables
    EXCLUDE_TABLES: "public.audit_log,public.events,public.metrics_raw"
    BACKUP_NAME: "analytics-db"

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    PGDATABASE: myapp
    SCHEMA_ONLY: "true"
    BACKUP_NAME: "myapp-schema"
    BACKUP_SCHEDULE: "0 0 * * *"  # Daily at midnight

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: mydb.xxxx.us-east-1.rds.amazonaws.com
    PGPORT: 5432
    PGUSER: postgres
    PGPASSWORD: ${RDS_PASSWORD}
    PGDATABASE: myapp
    PGSSLMODE: require
    # RDS doesn't allow physical backups
    BACKUP_TYPE: logical
    BACKUP_FORMAT: custom
    BACKUP_NAME: "rds-postgres-prod"

lox-pg-agent:
  image: loxbackup/postgresql-agent:latest
  environment:
    LOX_API_KEY: ${LOX_API_KEY}
    PGHOST: postgres
    PGUSER: postgres
    PGPASSWORD: ${POSTGRES_PASSWORD}
    PGDATABASE: all  # Backup all databases
    BACKUP_NAME: "postgres-cluster"
    BACKUP_SCHEDULE: "0 3 * * *"

# Download the backup
lox backup download <backup-id> -o backup.dump

# Restore to PostgreSQL
pg_restore -h localhost -U postgres -d myapp backup.dump

# Restore with parallel jobs
pg_restore -h localhost -U postgres -d myapp -j 4 backup.dump

# Restore specific tables only
pg_restore -h localhost -U postgres -d myapp -t users -t orders backup.dump

# Download the backup
lox backup download <backup-id> -o backup.sql.gz

# Decompress and restore
gunzip backup.sql.gz
psql -h localhost -U postgres -d myapp < backup.sql

# 1. Stop PostgreSQL
sudo systemctl stop postgresql

# 2. Clear data directory
rm -rf /var/lib/postgresql/16/main/*

# 3. Download and extract base backup
lox backup download <base-backup-id> -o base.tar.gz
tar -xzf base.tar.gz -C /var/lib/postgresql/16/main/

# 4. Download WAL archives
lox backup download <wal-backup-id> -o wal.tar.gz
tar -xzf wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal/

# 5. Create recovery signal with target time
cat > /var/lib/postgresql/16/main/recovery.signal << EOF
EOF

# 6. Configure recovery target in postgresql.conf
echo "recovery_target_time = '2024-01-15 14:30:00'" >> postgresql.conf
echo "restore_command = 'cp /path/to/wal/%f %p'" >> postgresql.conf

# 7. Start PostgreSQL
sudo systemctl start postgresql

# Backup metrics
lox_pg_backup_total{status="success"}
lox_pg_backup_total{status="failed"}
lox_pg_backup_duration_seconds
lox_pg_backup_size_bytes
lox_pg_last_backup_timestamp

# WAL metrics
lox_pg_wal_archived_total
lox_pg_wal_archive_lag_bytes

# Database metrics
lox_pg_database_size_bytes{database="myapp"}
lox_pg_tables_count{database="myapp"}