PostgreSQL Docker Template with WAL Archiving & Auto Backups

This repo provides a production-ready PostgreSQL setup using Docker with:

✅ Persistent data storage on host
✅ WAL archiving for Point-in-Time Recovery (PITR)
✅ Automatic daily backups via cron
✅ Restore script to any point in time
✅ Easy portability across systems or cloud

📁 Directory Structure

pg-docker/
├── docker-compose.yml # PostgreSQL with WAL archiving
├── .env # DB credentials
├── pg_backup.sh # Daily backup script
├── pg_restore_pitr.sh # Recovery script (to specific time)
├── data/ # PostgreSQL data directory (host-mounted)
├── wal_archive/ # WAL logs for PITR
└── backups/ # Timestamped full backups

🚀 Getting Started

1 - Clone the Repo & Create Folders

git clone https://github.com/hardikmer/postgres-docker-template.git pg-docker
cd ~/pg-docker/
mkdir -p data wal_archive backups

2 - Configure .env

Create a file named .env in the root directory:

POSTGRES_USER=admin
POSTGRES_PASSWORD=supersecret
POSTGRES_DB=mydb

3 - Start PostgreSQL Server

docker-compose up -d

4 - Connect to DB

You can connect via psql or any GUI tool:

Host: localhost
Port: 5432
User: admin
Password: supersecret
Database: mydb

Daily Backup Setup

Manual Backup

chmod +x pg_backup.sh
./pg_backup.sh

This saves a base backup + WAL archive in:

backups/YYYY-MM-DD_HH-MM-SS/
├── base.tar.gz
└── wal_archive/

Setup Daily Auto Backup via Cron

crontab -e

Add:

0 2 * * * /home/YOUR_USER/pg-docker/pg_backup.sh >> /home/YOUR_USER/pg-docker/backup.log 2>&1

replace YOUR_USER accordingly.

Point-in-Time Recovery (PITR)

To restore to a specific moment (e.g., 2025-05-19 02:15:00):

Edit pg_restore_pitr.sh

BACKUP_FOLDER=~/pg-docker/backups/2025-05-19_02-00-00
RECOVERY_TIME='2025-05-19 02:15:00'

Run Restore

chmod +x pg_restore_pitr.sh
./pg_restore_pitr.sh

This will:

Stop the container

Wipe old data

Restore base + WAL logs

Replay up to the exact timestamp

Restart PostgreSQL

Restore to Latest (All WALs)

To replay all WAL logs until the end, skip the recovery_target_time block in pg_restore_pitr.sh.

Security Tips

Don't commit .env to GitHub

Use firewall rules to restrict DB access

Use tools like fail2ban and ufw for host security

Consider encrypting backups or syncing to S3

Cloud Migration

Just copy these folders to any cloud server:

data/

wal_archive/

backups/ (optional)

docker-compose up -d

Your DB is back online. No reconfiguration needed.

License

MIT © Hardik Mer