How to Set Up Point‑in‑Time Recovery Backups for PostgreSQL 16 on an Ubuntu 24.04 VPS with WAL‑G and S3‑Compatible Storage (2025 Tutorial)

Database disasters strike without warning. Whether it’s accidental data deletion, corruption, or system failures, having reliable point-in-time recovery capabilities can mean the difference between a minor inconvenience and a business-critical outage. In this comprehensive tutorial, we’ll walk you through setting up continuous backup and point-in-time recovery for PostgreSQL 16 using WAL-G and S3-compatible storage on Ubuntu 24.04.

You’ll learn how to configure automated base backups, continuous WAL archiving, and perform granular point-in-time recovery operations—essential skills for any production PostgreSQL deployment.

Prerequisites

Before starting this tutorial, ensure you have:

• Ubuntu 24.04 LTS VPS with at least 2 GB RAM and 20 GB storage
• PostgreSQL 16 installed and running
• Root or sudo access to the server
• S3-compatible storage (AWS S3, MinIO, Backblaze B2, or Cloudflare R2)
• Basic knowledge of PostgreSQL administration and Linux command line

System Requirements:

• CPU: 2+ vCPUs recommended
• RAM: 2 GB minimum (4 GB+ for production)
• Storage: 20 GB+ available space
• Network: Reliable internet connection for S3 uploads

Step 1: Install WAL-G

WAL-G is a high-performance backup tool specifically designed for PostgreSQL. It handles both base backups and continuous WAL archiving with excellent compression and encryption support.

Download and install the latest WAL-G binary:

sudo wget -O /usr/local/bin/wal-g https://github.com/wal-g/wal-g/releases/download/v2.0.1/wal-g-pg-ubuntu-20.04-amd64
sudo chmod +x /usr/local/bin/wal-g

Verify the installation:

wal-g --version

Step 2: Configure S3 Storage Credentials

Create a dedicated configuration directory and set up your S3 credentials:

sudo mkdir -p /etc/wal-g
sudo touch /etc/wal-g/env
sudo chown postgres:postgres /etc/wal-g/env
sudo chmod 600 /etc/wal-g/env

Edit the environment file with your S3 credentials:

sudo -u postgres nano /etc/wal-g/env

Add the following configuration (adjust for your storage provider):

# AWS S3 Configuration
export AWS_ACCESS_KEY_ID="your-access-key"
export AWS_SECRET_ACCESS_KEY="your-secret-key"
export AWS_REGION="us-east-1"
export WALG_S3_PREFIX="s3://your-bucket/postgresql-backups"

# For other S3-compatible providers, add:
# export AWS_ENDPOINT="https://your-endpoint.com"

# WAL-G Configuration
export WALG_COMPRESSION_METHOD="lz4"
export WALG_DELTA_MAX_STEPS="6"
export POSTGRES_PASSWORD="your-postgres-password"

Step 3: Configure PostgreSQL for WAL Archiving

Enable WAL archiving in PostgreSQL by modifying the configuration file:

sudo -u postgres nano /etc/postgresql/16/main/postgresql.conf

Update these key parameters:

# WAL Configuration
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/wal-g wal-push %p'
archive_timeout = 60

# Replication settings for point-in-time recovery
max_wal_senders = 3
wal_keep_size = 1GB

# Checkpoint settings for better backup performance
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 1GB

Security Warning: Always test your archive_command in a non-production environment first to avoid potential data loss.

Create a script to source the WAL-G environment:

sudo tee /usr/local/bin/wal-g-env << 'EOF'
#!/bin/bash
source /etc/wal-g/env
exec "$@"
EOF

sudo chmod +x /usr/local/bin/wal-g-env

Update the archive command to use the environment wrapper:

archive_command = '/usr/local/bin/wal-g-env /usr/local/bin/wal-g wal-push %p'

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Step 4: Create Initial Base Backup

Create your first base backup using WAL-G:

sudo -u postgres /usr/local/bin/wal-g-env /usr/local/bin/wal-g backup-push /var/lib/postgresql/16/main

Monitor the backup progress and verify completion:

sudo -u postgres /usr/local/bin/wal-g-env /usr/local/bin/wal-g backup-list

Step 5: Automate Regular Backups

Set up automated backups using cron. Create a backup script:

sudo tee /usr/local/bin/postgres-backup.sh << 'EOF'
#!/bin/bash
source /etc/wal-g/env

# Perform base backup
/usr/local/bin/wal-g backup-push /var/lib/postgresql/16/main

# Clean up old backups (keep last 7 days)
/usr/local/bin/wal-g delete before FIND_FULL 7

# Log backup status
echo "$(date): Backup completed" >> /var/log/postgresql/wal-g.log
EOF

sudo chmod +x /usr/local/bin/postgres-backup.sh
sudo chown postgres:postgres /usr/local/bin/postgres-backup.sh

Add a cron job for daily backups:

sudo -u postgres crontab -e

Add this line for daily backups at 2 AM:

0 2 * * * /usr/local/bin/postgres-backup.sh

Step 6: Perform Point-in-Time Recovery

To demonstrate point-in-time recovery, let’s simulate a recovery scenario. First, note the current time for recovery:

date
# Record this timestamp for recovery

Stop PostgreSQL and backup the current data directory:

sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.backup

Restore from the latest backup:

sudo -u postgres mkdir -p /var/lib/postgresql/16/main
sudo -u postgres /usr/local/bin/wal-g-env /usr/local/bin/wal-g backup-fetch /var/lib/postgresql/16/main LATEST

Create a recovery configuration file:

sudo -u postgres tee /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = '/usr/local/bin/wal-g-env /usr/local/bin/wal-g wal-fetch %f %p'
recovery_target_time = '2025-01-15 10:30:00 UTC'
recovery_target_action = 'promote'
EOF

Create the recovery signal file:

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

Start PostgreSQL to begin recovery:

sudo systemctl start postgresql

Monitor the recovery process in the PostgreSQL logs:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Best Practices

Security Considerations:

• Enable encryption at rest using WAL-G’s built-in GPG encryption
• Use IAM roles instead of access keys when possible
• Implement network-level security with VPC endpoints for S3 access
• Regular backup restoration tests to verify data integrity

Performance Optimization:

• Adjust WALG_COMPRESSION_METHOD based on your CPU/bandwidth trade-offs
• Use delta backups to reduce storage costs and backup time
• Monitor WAL generation rates and adjust archive_timeout accordingly
• Consider using parallel backup uploads with WALG_UPLOAD_CONCURRENCY

Monitoring and Alerting:

• Set up alerts for backup failures and archive command errors
• Monitor WAL archive lag to detect potential issues
• Track storage usage and implement retention policies
• Regular recovery drills in non-production environments

Conclusion

You’ve successfully implemented a robust point-in-time recovery solution for PostgreSQL 16 using WAL-G and S3-compatible storage. This setup provides continuous protection against data loss with the ability to recover to any point in time within your retention window.

The combination of automated base backups, continuous WAL archiving, and granular recovery options makes this solution suitable for production environments where data integrity and availability are critical.

For organizations looking to deploy similar database infrastructure, consider exploring highly available PostgreSQL clusters or our comprehensive guide on choosing the right database for your VPS deployment. With properly configured backup and recovery strategies, your PostgreSQL databases will be well-protected against both planned and unplanned outages.