PostgreSQL Master-Replica Replication Setup

A production-ready PostgreSQL 16 replication environment with monitoring, management tools, and automatic failover capabilities.

✅ Current Status

• PostgreSQL Version: 16-alpine
• Replication Type: Asynchronous streaming replication
• Health: Both primary and replica operational
• Replication Lag: < 3ms (near real-time)
• Data Persistence: Docker volumes for data safety

🚀 Quick Start

# 1. Clone or download this repository
git clone <your-repo-url>
cd self-hosted-pg

# 2. Configure environment (IMPORTANT: Change passwords!)
cp .env.postgres.example .env.postgres
nano .env.postgres

# 3. Start the cluster
./manage-postgres.sh start

# 4. Verify replication is working
./manage-postgres.sh test

📁 Project Structure

self-hosted-pg/
├── docker-compose-postgres.yml  # Main orchestration file
├── manage-postgres.sh           # Management CLI tool
├── .env.postgres               # Environment variables (create from .example)
├── postgres/
│   ├── primary/
│   │   ├── init/              # Initialization scripts (01-init-primary.sh)
│   │   └── config/            # postgresql.conf, pg_hba.conf
│   ├── replica/
│   │   └── config/            # Replica-specific configuration
│   ├── scripts/
│   │   ├── setup-replica.sh   # Replica initialization script
│   │   └── monitor-replication.sh
│   └── pgadmin/
│       └── servers.json       # PgAdmin server configurations
└── backups/                   # Database backups directory

🔧 Services & Ports

Service	Container Name	Port	Purpose
PostgreSQL Primary	postgres_primary	5432	Read/Write operations
PostgreSQL Replica	postgres_replica	5433	Read-only operations
PgAdmin 4	pgadmin	5050	Web-based management UI

📊 Management Commands

Service Control

./manage-postgres.sh start      # Start all services
./manage-postgres.sh stop       # Stop all services
./manage-postgres.sh restart    # Restart all services
./manage-postgres.sh status     # Show cluster status

Monitoring & Testing

./manage-postgres.sh monitor    # Detailed replication metrics
./manage-postgres.sh test       # Test replication is working
./manage-postgres.sh logs all   # View all service logs
./manage-postgres.sh logs primary   # Primary logs only
./manage-postgres.sh logs replica   # Replica logs only

Database Operations

./manage-postgres.sh psql primary   # Connect to primary (read/write)
./manage-postgres.sh psql replica   # Connect to replica (read-only)
./manage-postgres.sh backup         # Create full backup
./manage-postgres.sh restore <file> # Restore from backup

Advanced Operations

./manage-postgres.sh promote    # Promote replica to primary (failover)
./manage-postgres.sh clean      # Remove all data (WARNING: Data loss!)

🔍 Monitoring Replication

Quick Status Check

# Check replication status
docker exec postgres_primary psql -U postgres -c "SELECT * FROM pg_stat_replication;"

# Check replica lag
docker exec postgres_primary psql -U postgres -c "
SELECT application_name, state, sync_state, replay_lag 
FROM pg_stat_replication;"

Verify Replica is in Recovery Mode

docker exec postgres_replica psql -U postgres -c "SELECT pg_is_in_recovery();"
# Should return 't' (true)

🔐 Security Configuration

1. Update Passwords (Required!)

Edit .env.postgres:

POSTGRES_PASSWORD=<strong-password>
REPLICATION_PASSWORD=<strong-password>
PGADMIN_PASSWORD=<strong-password>

2. Network Security

The setup uses Docker networks for isolation. For production:

• Configure firewall rules
• Use SSL/TLS certificates
• Implement network segmentation

3. Access Control

Current pg_hba.conf uses MD5 authentication for network connections.

🎯 Application Integration

Connection Strings

# Primary (Read/Write)
postgresql://postgres:password@localhost:5432/myapp

# Replica (Read-Only)
postgresql://postgres:password@localhost:5433/myapp

Docker Compose Integration

services:
  your-app:
    environment:
      - DB_HOST=postgres-primary
      - DB_PORT=5432
      - DB_NAME=myapp
      - DB_USER=postgres
      - DB_PASSWORD=${POSTGRES_PASSWORD}
    networks:
      - self-hosted-pg_postgres_network

networks:
  self-hosted-pg_postgres_network:
    external: true

Node.js Example

const { Pool } = require('pg');

// Write operations
const writePool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  user: 'postgres',
  password: process.env.POSTGRES_PASSWORD
});

// Read operations
const readPool = new Pool({
  host: 'localhost',
  port: 5433,
  database: 'myapp',
  user: 'postgres',
  password: process.env.POSTGRES_PASSWORD
});

📈 Performance Tuning

Current Settings (postgres/*/config/postgresql.conf)

• shared_buffers: 256MB
• effective_cache_size: 1GB
• max_connections: 200
• wal_keep_size: 1GB

Recommended Adjustments

For production, adjust based on available RAM:

shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = RAM / max_connections / 4

🐛 Troubleshooting

Replica Not Connecting

# Check replica logs
docker logs postgres_replica

# Verify replication user exists
docker exec postgres_primary psql -U postgres -c "\du replicator"

# Test connectivity
docker exec postgres_replica ping postgres-primary

Replication Lag Issues

# Check current lag
./manage-postgres.sh monitor

# Force checkpoint
docker exec postgres_primary psql -U postgres -c "CHECKPOINT;"

Reset Replica

# Stop services
./manage-postgres.sh stop

# Remove replica volume
docker volume rm self-hosted-pg_postgres_replica_data

# Restart (replica will resync)
./manage-postgres.sh start

🔄 Backup & Recovery

Automated Backups

# Add to crontab for daily backups at 2 AM
0 2 * * * cd /path/to/self-hosted-pg && ./manage-postgres.sh backup

Manual Backup/Restore

# Create backup
./manage-postgres.sh backup

# List backups
ls -la backups/

# Restore specific backup
./manage-postgres.sh restore backups/postgres_backup_20250912_120613.sql

📋 Prerequisites

• Docker Engine 20.10+
• Docker Compose 1.29+
• 2GB+ RAM recommended
• 10GB+ disk space

🚦 Health Checks

Both services include health checks:

• Primary: Every 10s, checks if PostgreSQL is accepting connections
• Replica: Every 10s, verifies PostgreSQL is running

📝 Important Notes

• Replication is asynchronous by default (can be made synchronous)
• Replica is read-only (cannot accept writes)
• WAL files accumulate - monitor disk space
• Default database name is myapp
• PgAdmin requires email/password from .env.postgres

🆘 Quick Fixes

Permission Issues

# If you see "root execution not permitted"
chmod +x fix-monitor.sh
./fix-monitor.sh

Clean Start

# Complete reset (WARNING: Deletes all data!)
./manage-postgres.sh clean
./manage-postgres.sh setup
./manage-postgres.sh start

📚 Resources

• PostgreSQL 16 Documentation
• Streaming Replication
• PgAdmin Documentation

🎉 Success Indicators

When everything is working correctly:

• ./manage-postgres.sh status shows both containers healthy
• ./manage-postgres.sh test confirms replication works
• ./manage-postgres.sh monitor shows streaming state with minimal lag
• PgAdmin accessible at http://localhost:5050

---

Version: PostgreSQL 16 with Streaming Replication
Last Updated: September 2025
Status: Production-ready for development/testing environments