Deploy and Host PostgreSQL + Barman (PITR) on Railway

> ⚠️ EXPERIMENTAL: This template is experimental and not recommended for production use. Use at your own risk.

PostgreSQL deployment with Point-in-Time Recovery (PITR) using Barman for backup management and disaster recovery scenarios. Includes automated backups and cloud storage integration.

About Hosting PostgreSQL + Barman (PITR)

This template deploys PostgreSQL with Barman backup management for learning and testing PITR capabilities. It includes a primary database, standby replica, and automated backup system with cloud storage support (Cloudflare R2/AWS S3). The setup enables point-in-time recovery to restore your database to any moment in history, useful for recovering from data corruption or accidental deletions. All services communicate via SSH keys and include monitoring tools for backup validation.

Common Use Cases

• Learning PostgreSQL PITR and backup management concepts
• Testing disaster recovery procedures in development environments
• Prototyping applications that need backup and recovery capabilities
• Development workflows requiring database state restoration
• Educational purposes for understanding PostgreSQL replication and backup strategies

Dependencies for PostgreSQL + Barman (PITR) Hosting

• Cloud Storage: Cloudflare R2, AWS S3, or S3-compatible storage for backup archiving
• SSH Key Pair: Ed25519 keys for secure service-to-service communication
• Environment Variables: Database passwords, storage credentials, and service configuration

Deployment Dependencies

• Cloudflare R2 or AWS S3 for backup storage
• PostgreSQL Documentation for database administration
• Barman Documentation for backup management reference

Implementation Details

Services Included:

• Primary PostgreSQL: Main database server with backup hooks
• Standby PostgreSQL: Replica for replication testing
• Barman: Backup manager with WAL archival and cloud sync

Key Features:

• Automated backups with cloud storage
• Point-in-time recovery capabilities
• Streaming replication setup
• SSL/TLS encryption for connections
• Backup monitoring and validation tools

Environment Setup:

# Generate SSH keys for service communication
ssh-keygen -t ed25519 -a 100 -N "" -f id_ed25519
SSH_PRIVATE_KEY=$(openssl base64 -A < id_ed25519)
SSH_PUBLIC_KEY=$(openssl base64 -A < id_ed25519.pub)

Complete Point-in-Time Recovery Guide

Prerequisites:

1. Install Railway CLI:

   curl -fsSL https://railway.app/install.sh | sh
   

2. Link to project: railway link and select your deployed project
3. Ensure cloud storage is configured with backups available

Recovery Process:

Step 1: Create Recovery Service

• In Railway dashboard, duplicate your standby service
• Rename to "recovery-pg" or similar
• Set environment variables:
  • MODE=idle
  • Remove PRIMARY_HOST (critical)
  • Keep SSH_PRIVATE_KEY, SSH_PUBLIC_KEY, POSTGRES_PASSWORD
• Redeploy service (starts in idle mode)

Step 2: Access Barman Service

railway ssh barman

Step 3: Check Available Backups

# List all backups to choose restore point
su - barman -c "barman list-backup pg-primary-db"

# Show details of latest backup
su - barman -c "barman show-backup pg-primary-db latest"

Step 4: Perform Restore

# Clear recovery service data directory
su - barman -c "ssh -i /var/lib/barman/.ssh/id_ed25519 \
  [email protected] 'rm -rf /var/lib/postgresql/data/pgdata/*'"

# Restore to specific timestamp (adjust time as needed)
su - barman -c "barman restore \
  --remote-ssh-command 'ssh -i /var/lib/barman/.ssh/id_ed25519 \
  [email protected]' \
  --target-time '2025-08-22 17:50:00+00:00' \
  pg-primary-db latest /var/lib/postgresql/data/pgdata"

Step 5: Activate Recovered Database

• In Railway dashboard for recovery-pg service:
  • Set MODE=active
  • Ensure PRIMARY_HOST is NOT set
  • Redeploy service
• Database will start and pause at recovery point

Step 6: Complete Promotion

• In Railway dashboard, go to recovery-pg service → Settings → Public Networking
• Enable public networking to get connection details (hostname, port)
• Connect using psql or your preferred client:

psql -h  -p  -U postgres -d 
SELECT pg_wal_replay_resume();

Step 7: Verify Recovery

# Test database is writable (confirms primary status)
psql -h  -p  -U postgres -d  -c "SELECT pg_is_in_recovery();" # Should return 'f'

psql -h  -p  -U postgres -d  -c "CREATE TABLE recovery_test (id int);" # Should succeed

Optional: Replace Original Primary

If you want to make recovery-pg your new primary:

1. Stop old primary service in Railway dashboard
2. Create replication slots on recovery-pg (connect via public networking):

   SELECT pg_create_physical_replication_slot('barman_slot');
   SELECT pg_create_physical_replication_slot('standby_slot');
   

3. Update standby service:
   • Wipe standby volume: Service → Volume → Settings → "Wipe volume"
   • Set PRIMARY_HOST=recovery-pg.railway.internal
   • Redeploy (performs fresh pg_basebackup)
4. Update barman service:
   • Set POSTGRES_HOST=recovery-pg.railway.internal
   • Redeploy (clears stale WAL state automatically)

Troubleshooting:

• Timeline conflicts: Wipe standby volume before switching primaries
• SSH failures: Verify SSH keys match between services
• Target time errors: Use +00:00 timezone and ensure time is after backup completion
• Connection issues: Enable public networking in Railway dashboard for database access

For complete step-by-step instructions with troubleshooting, see the included RECOVERY.md guide.

Why Deploy PostgreSQL + Barman (PITR) on Railway?

This template provides a complete PostgreSQL disaster recovery solution that's typically complex to set up and maintain. Railway's platform simplifies the deployment by handling service discovery, networking, and environment management automatically.

Key advantages of this Railway deployment:

• Simplified Service Communication: Railway's internal networking eliminates complex firewall and DNS configuration between PostgreSQL, Barman, and standby services
• Automatic SSH Key Distribution: Environment variables seamlessly distribute SSH keys across all services without manual key management
• Integrated Cloud Storage: Direct integration with Cloudflare R2 and AWS S3 for backup archival without additional infrastructure
• One-Click Scaling: Vertically scale database resources during recovery operations when needed
• Built-in Monitoring: Railway's logging and metrics help track backup jobs and replication status across all services

Perfect for applications requiring disaster recovery without the operational overhead of managing multiple servers, networking, and backup infrastructure.