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
• Built-in extensions: pgvector (vector similarity search), VectorChord (advanced vector indexing, opt-in via ENABLE_VCHORD=1), ParadeDB pg_search (full-text search)
• Telegram notifications on backup failures
• Optional pgAdmin web interface

Project Structure

Path	Description
docker-compose.yml	Service definitions: postgres, backup, ssh-server (testing), pgadmin
Dockerfile.postgres-walg	PostgreSQL image with wal-g, pgvector, VectorChord, pg_search, PgBouncer
Dockerfile.backup	Alpine-based backup sidecar (SQL dump or wal-g cron)
backup.sh	SQL mode backup script (pg_dumpall → gzip → age → rclone)
entrypoint-backup.sh	Backup container entrypoint, sets up cron for SQL or WAL mode
postgresql.conf.template	PostgreSQL configuration template (WAL archiving settings)
pgbouncer.ini.template	PgBouncer configuration template
env_sample	Environment variable template — copy to .env to configure
run-tests	Top-level test runner wrapper with CLI flags
scripts/	Runtime scripts (entrypoint, wal-g helpers, PgBouncer setup, SSH setup)
test/	Integration and regression tests
docs/	Extended documentation (env vars reference, testing guides, etc.)
secrets/	Local SSH keys for testing (not committed to production)

Container Architecture

The stack runs two core service containers with clearly separated responsibilities:

postgres (built from Dockerfile.postgres-walg)

Runs PostgreSQL and owns all real-time data operations:

• Starts and manages the PostgreSQL server process
• In WAL mode: continuously archives WAL segments to remote storage via archive_command = 'wal-g wal-push %p' — this happens inside this container, not the backup container
• Hosts optional services that must be co-located with PostgreSQL: PgBouncer (connection pooler) and VectorChord / pgvector / pg_search extensions
• wal-g binary is installed here so the archive command can execute it in-process without an IPC round-trip

backup (built from Dockerfile.backup)

Alpine-based sidecar responsible for scheduled, operator-triggered backup and retention tasks:

• SQL mode: cron runs backup.sh on the configured schedule → pg_dumpall → gzip → Age encryption → Rclone upload → remote retention cleanup
• WAL mode: cron runs wal-g-runner.sh backup (base backup / backup-push) and wal-g-runner.sh clean (retention / delete retain FULL N) on independent schedules; also handles SSH key setup and .walg_env preparation via walg-env-prepare.sh
• Sends Telegram notifications on failure
• Mounts the pg_data volume read-write so backup-push can read PGDATA directly

Summary table:

Task	Container
PostgreSQL server	postgres
WAL segment archiving (wal-push, continuous)	postgres
SQL dump + encrypt + upload (scheduled)	backup
Base backup (backup-push, scheduled)	backup
Retention cleanup (delete retain FULL, scheduled)	backup
PgBouncer connection pooler	postgres
pgvector / VectorChord / pg_search extensions	postgres

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 starts, create the first base backup so PITR is available immediately (WAL archiving starts automatically, but recovery requires at least one base backup):

   sudo docker compose exec backup bash -c "/opt/walg/scripts/wal-g-runner.sh backup"
   
   # Verify
   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       # session | transaction | statement
PGBOUNCER_MAX_CLIENT_CONN=100
PGBOUNCER_DEFAULT_POOL_SIZE=20

Note: Only POSTGRES_USER is configured for PgBouncer by default. To add users, update /etc/pgbouncer/userlist.txt inside the container.

Database Access

Via Docker Network (Recommended)

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

Via PgBouncer (Host Access)

When ENABLE_PGBOUNCER=1, connect through PgBouncer on port 6432:

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

Note: PostgreSQL port 5432 is not exposed to the host by default. To access PostgreSQL directly from the host, add a port mapping in docker-compose.yml, or use docker exec:

docker exec -it postgres psql -U your_db_user -d your_db

pgAdmin Web Interface

Access pgAdmin at http://localhost:8080 with credentials from your .env file (default: admin@admin.com / admin).

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 ssh: handshake failed: ssh: unable to authenticate, ensure you’re using the latest version — the wal-g wrapper script now auto-loads SSH credentials. Verify with:

sudo docker exec postgres wal-g backup-list

Debugging SSH Connection

# Check SSH key and env
sudo docker exec postgres ls -la /var/lib/postgresql/.ssh/
sudo docker exec postgres cat /var/lib/postgresql/.walg_env

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

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