PostgreSQL with Automated Encrypted Backups

Production-ready PostgreSQL setup with automated, encrypted backups to remote storage. Choose between two backup strategies:

• **SQL Mode** (default): Daily full dumps using pg_dumpall + Age encryption + Rclone
• **WAL Mode**: Continuous incremental backups using wal-g + SSH storage + Point-in-Time Recovery

Features

• PostgreSQL Database (customizable version, default 17.6)
• Two backup modes: SQL (full dumps) or WAL-G (incremental with PITR)
• Automated backup scheduling and retention policies
• Optional PgBouncer connection pooling
• Telegram notifications on backup failures
• Optional pgAdmin web interface

Quick Start

1. Create environment file:

   cp env_sample .env
   # Edit .env to configure your backup mode and credentials
       

2. Choose your backup mode:
   • For SQL mode (default): BACKUP_MODE=sql
   • For WAL mode: BACKUP_MODE=wal
3. Create required Docker volume:

   sudo docker volume create postgres-data
       

4. Start the stack:

   sudo docker compose up --build -d
       

5. Monitor backup logs:

   sudo docker compose logs backup -f
       

Backup Mode Comparison

Feature	SQL Mode	WAL Mode
Backup Type	Full dump daily	Continuous WAL + periodic base
Storage Size	Larger (full dumps)	Smaller (incremental deltas)
Recovery	Daily snapshots only	Point-in-time recovery
Large DB Performance	Slower	Faster
Storage Backend	Rclone (any cloud)	SSH server
Setup Complexity	Simpler	More complex

SQL Mode Setup

Configure these variables in .env:

BACKUP_MODE=sql
POSTGRES_VERSION=17.6  # Or your desired PostgreSQL version

# Rclone configuration (base64 encoded)
# Run: cat ~/.config/rclone/rclone.conf | base64 -w0
RCLONE_CONFIG_BASE64=PASTE_YOUR_BASE64_ENCODED_RCLONE_CONFIG_HERE

# Age encryption public key
AGE_PUBLIC_KEY=PASTE_YOUR_AGE_PUBLIC_KEY_HERE

# Remote backup path
REMOTE_PATH=your_rclone_remote:path/to/backups

# Backup schedule (cron format)
BACKUP_CRON_SCHEDULE="0 2 * * *"  # Daily at 2:00 AM

# Retention
SQL_BACKUP_RETAIN_DAYS=30

WAL Mode Setup

1. Configure .env file:

   BACKUP_MODE=wal
   POSTGRES_VERSION=17.6  # Or your desired version
   
   # SSH storage configuration
   WALG_SSH_PREFIX=ssh://walg@backup-host/var/backups/pg/prod
   
   # SSH private key (base64 encoded)
   # Run: cat ~/.ssh/id_walg | base64 -w0
   WALG_SSH_PRIVATE_KEY=PASTE_YOUR_BASE64_ENCODED_SSH_PRIVATE_KEY_HERE
   
   # Backup retention
   WALG_RETENTION_FULL=7  # Keep 7 full backups
   
   # Backup schedules
   WALG_BASEBACKUP_CRON="30 1 * * *"  # Daily base backup at 1:30 AM
   WALG_CLEAN_CRON="15 3 * * *"       # Cleanup at 3:15 AM
       

2. Start the stack:

   sudo docker compose up --build -d
       

3. ⚠️ IMPORTANT: Create first base backup manually

   After the WAL mode container is running in production, you must manually execute the first base backup:

   sudo docker compose exec backup bash -c "/opt/walg/scripts/wal-g-runner.sh backup"
       

   Why is this required?

   • WAL archiving starts automatically when PostgreSQL starts
   • However, WAL-G requires at least one base backup before you can perform point-in-time recovery
   • The automated cron schedule runs at scheduled times (e.g., daily at 1:30 AM)
   • By manually creating the first base backup immediately after deployment, you ensure:
     • You have a recovery point from the start of production operations
     • WAL files archived after this base backup can be used for PITR
     • You don’t have to wait for the first scheduled backup to have disaster recovery capability

   Verify the backup succeeded:

   # Check the backup logs
   sudo docker compose logs backup
   
   # List available backups
   sudo docker compose exec postgres wal-g backup-list
       

Note: The POSTGRES_VERSION automatically creates a version-specific subdirectory in your backup storage. For example, if WALG_SSH_PREFIX=ssh://user@host/backups, WAL files will be stored at ssh://user@host/backups/17.6/.

PgBouncer Connection Pooling (Optional)

Enable PgBouncer for connection pooling:

# In .env file
ENABLE_PGBOUNCER=1
PGBOUNCER_PORT=6432
PGBOUNCER_POOL_MODE=session
PGBOUNCER_MAX_CLIENT_CONN=100
PGBOUNCER_DEFAULT_POOL_SIZE=20

Connect to PgBouncer on port 6432 instead of PostgreSQL’s port 5432:

psql -h localhost -p 6432 -U your_db_user -d your_db

Note: By default, only POSTGRES_USER is configured for PgBouncer. To add additional users, you’ll need to update /etc/pgbouncer/userlist.txt inside the container. See env_sample for detailed instructions.

Database Access

Direct PostgreSQL Connection

Connect using psql or any PostgreSQL client:

psql -h localhost -p 5432 -U your_db_user -d your_db

pgAdmin Web Interface

Access pgAdmin at http://localhost:8080 with credentials from your .env file (default: [email protected] / admin).

From Another Docker Container

If your application runs in a Docker container, use the shared network:

services:
  my_app:
    image: your_app_image
    environment:
      DATABASE_URL: "postgresql://pg_user:pg_password@postgres:5432/app_database"
    networks:
      - shared_net

networks:
  shared_net:
    external: true
    name: postgres-network

Monitoring and Troubleshooting

Check Backup Status

# View backup logs
sudo docker compose logs backup -f

# WAL mode: Check last base backup
sudo docker compose exec postgres cat /var/lib/postgresql/data/walg_basebackup.last

# WAL mode: List available backups
sudo docker compose exec postgres wal-g backup-list

Manual Operations (WAL Mode)

# Trigger base backup manually
sudo docker compose exec backup /opt/walg/scripts/wal-g-runner.sh backup

# Cleanup old backups
sudo docker compose exec backup /opt/walg/scripts/wal-g-runner.sh clean

# Check wal-g version
sudo docker compose exec postgres wal-g --version

Note: WAL-G commands can be run directly via docker exec postgres wal-g <command> without needing to switch users. The system includes a wrapper script that automatically loads the required environment variables (including SSH_PRIVATE_KEY_PATH) before executing WAL-G commands.

Common Issues (WAL Mode)

SSH Authentication Error

If you see an error like:

ERROR: ssh: handshake failed: ssh: unable to authenticate, attempted methods [none]

This was a known issue that has been fixed. The system now includes a wal-g wrapper script that automatically sources the environment file containing SSH credentials. Make sure you’re using the latest version with the wrapper script installed.

To verify the fix is working:

# Check that wal-g wrapper is installed
sudo docker exec postgres which wal-g
# Output should be: /usr/local/bin/wal-g

# Check that environment file exists
sudo docker exec postgres test -f /var/lib/postgresql/.walg_env && echo "Environment file exists"

# Test wal-g with environment loaded
sudo docker exec postgres wal-g backup-list

Debugging SSH Connection

If you continue to have SSH issues:

# Check if SSH key exists
sudo docker exec postgres ls -la /var/lib/postgresql/.ssh/

# Test SSH connection manually
sudo docker exec postgres su - postgres -c "ssh -v <user>@<host> -p <port>"

# Check WAL-G environment variables
sudo docker exec postgres cat /var/lib/postgresql/.walg_env

Restore Procedures

SQL Mode Restore

1. Download the .sql.gz.age backup file from your Rclone remote
2. Decrypt: age -d -i /path/to/private.key backup.sql.gz.age > backup.sql.gz
3. Decompress: gunzip backup.sql.gz
4. Restore: psql -h localhost -U your_db_user -d your_target_db < backup.sql

WAL Mode Restore (Point-in-Time Recovery)

1. Stop the PostgreSQL container:

   sudo docker compose stop postgres
       

2. Create a restore container:

   sudo docker run --rm -it \
     --env-file .env \
     -v postgres-data:/var/lib/postgresql/data \
     postgres-walg bash
       

3. Perform the restore:

   # Clear data directory
   rm -rf /var/lib/postgresql/data/*
   
   # Fetch base backup
   wal-g backup-fetch /var/lib/postgresql/data LATEST
   
   # Configure recovery (PostgreSQL 12+)
   # Create empty recovery.signal file to trigger recovery mode
   touch /var/lib/postgresql/data/recovery.signal
   
   # Add recovery settings to postgresql.conf
   cat >> /var/lib/postgresql/data/postgresql.conf << EOF
   restore_command = 'wal-g wal-fetch %f %p'
   recovery_target_time = '2025-01-15 14:30:00+00'
   recovery_target_action = 'promote'
   EOF
       

4. Restart services:

   sudo docker compose up -d
       

Additional Resources

• Complete environment variable reference: See env_sample
• Testing documentation: test/README.org and docs/WAL-G-TESTING.md
• Integration guide: docs/INTEGRATION.md
• PgBouncer testing: docs/PGBOUNCER_TESTING.md

Security Considerations

• Use strong passwords for POSTGRES_PASSWORD
• WAL mode: Restrict SSH key access to backup directory only
• SQL mode: Secure your Age private key and Rclone configuration
• Regularly test your restore procedures
• Keep your backup storage secure and properly encrypted