PostgreSQL Streaming Physical Replication with Docker

This project demonstrates how to set up PostgreSQL Streaming Physical Replication using Docker containers. Streaming replication is one of several replication methods available in PostgreSQL, offering real-time data synchronization between a primary and one or more replica servers.

Table of Contents

• Overview
• What is Streaming Physical Replication?
• Architecture
• Prerequisites
• Project Structure
• Setup and Configuration
• How It Works
• Testing the Replication
• Monitoring
• Other PostgreSQL Replication Methods
• Troubleshooting
• Cleanup

Overview

This setup creates a PostgreSQL primary-replica configuration where:

• The primary server handles all write operations
• The replica server receives real-time updates and serves read-only queries
• Data is replicated at the physical level (WAL records) for maximum consistency

What is Streaming Physical Replication?

Streaming Physical Replication is a method where:

1. Physical Level: Replicates at the disk block level (not SQL statements)
2. Streaming: WAL (Write-Ahead Log) records are streamed in real-time
3. Asynchronous: By default, the primary doesn't wait for replica confirmation
4. Hot Standby: Replicas can serve read-only queries while receiving updates

Key Benefits:

• ✅ Near real-time replication (typically milliseconds lag)
• ✅ Crash consistency - exact byte-for-byte copy
• ✅ High performance - minimal overhead on primary
• ✅ Automatic failover support with additional tools
• ✅ Read scaling - distribute read queries across replicas

Limitations:

• ❌ Entire database replication - can't replicate specific tables
• ❌ Same PostgreSQL version required on all nodes
• ❌ Read-only replicas - no writes allowed on standby
• ❌ Network dependent - requires stable network connection

Architecture

┌─────────────────┐    WAL Stream    ┌─────────────────┐
│   Primary       │ ──────────────► │   Replica       │
│   (Port 5433)   │                 │   (Port 5434)   │
│                 │                 │                 │
│ ┌─────────────┐ │                 │ ┌─────────────┐ │
│ │   Writes    │ │                 │ │ Read-Only   │ │
│ │   Reads     │ │                 │ │   Queries   │ │
│ └─────────────┘ │                 │ └─────────────┘ │
└─────────────────┘                 └─────────────────┘

Prerequisites

• Docker and Docker Compose installed
• At least 2GB RAM available
• Network connectivity between containers

Project Structure

pg-replication/
├── docker-compose.yml           # Main orchestration file
├── primary/
│   ├── data/                   # Primary database files (auto-generated)
│   └── init-primary.sh         # Primary initialization script
├── replica/
│   ├── data/                   # Replica database files (auto-generated)
│   └── init-replica.sh         # Replica initialization script
└── README.md                   # This file

Setup and Configuration

1. Clone and Navigate

cd "Streaming Physical Replication/pg-replication"

2. Start the Replication Setup

docker-compose up --build

3. What Happens During Startup

1. Primary Server:

   • Initializes PostgreSQL database
   • Configures WAL level to 'replica'
   • Sets up replication slots
   • Configures pg_hba.conf for replication connections

2. Replica Server:

   • Waits for primary to be ready
   • Creates a replication slot on primary
   • Performs base backup from primary using pg_basebackup
   • Starts in standby mode
   • Begins streaming WAL from primary

How It Works

1. Write-Ahead Logging (WAL)

PostgreSQL uses WAL to ensure durability:

Application Write → WAL → Data Files → Replica Stream

2. Replication Process

The replication follows this sequence:

1. Application writes data to Primary
2. Primary writes WAL record
3. Primary applies changes to data files
4. WAL record is streamed to Replica
5. Replica applies WAL record
6. Applications can read from Replica

3. Key Configuration Parameters

Primary Server (postgresql.conf):

wal_level = replica              # Enable WAL for replication
max_wal_senders = 3             # Maximum concurrent connections
max_replication_slots = 3        # Maximum replication slots
wal_keep_size = 64MB            # WAL retention size

Authentication (pg_hba.conf):

host replication postgres 0.0.0.0/0 md5

Replica Connection:

primary_conninfo = 'host=primary port=5432 user=postgres password=password'
primary_slot_name = 'replica1_slot'

Testing the Replication

1. Create Test Data on Primary

# Connect to primary and create test table
docker exec -it pg_primary psql -U postgres -c "
CREATE TABLE replication_test (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
"

# Insert test data
docker exec -it pg_primary psql -U postgres -c "
INSERT INTO replication_test (data) VALUES 
('Hello from Primary Server'),
('This is a test record'),
('Real-time replication demo');
"

2. Verify Data on Replica

# Check if data appears on replica (should be immediate)
docker exec -it pg_replica psql -U postgres -c "
SELECT * FROM replication_test ORDER BY id;
"

3. Test Read-Only Nature of Replica

# This should fail with "cannot execute INSERT in a read-only transaction"
docker exec -it pg_replica psql -U postgres -c "
INSERT INTO replication_test (data) VALUES ('This will fail');
"

Monitoring

1. Check Replication Status on Primary

docker exec -it pg_primary psql -U postgres -c "
SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn
FROM pg_stat_replication;
"

2. Check Replication Status on Replica

docker exec -it pg_replica psql -U postgres -c "
SELECT 
    pg_is_in_recovery() as is_replica,
    pg_last_wal_receive_lsn() as receive_lsn,
    pg_last_wal_replay_lsn() as replay_lsn,
    pg_last_xact_replay_timestamp() as last_replay;
"

3. Monitor Replication Lag

docker exec -it pg_replica psql -U postgres -c "
SELECT 
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() 
        THEN 0 
        ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) 
    END AS lag_seconds;
"

Other PostgreSQL Replication Methods

1. Logical Replication

┌─────────────┐  SQL Commands  ┌─────────────┐
│   Primary   │ ──────────────► │   Replica   │
│             │                 │             │
│ All Tables  │                 │ Select      │
│             │                 │ Tables      │
└─────────────┘                 └─────────────┘

Characteristics:

• Replicates logical changes (SQL statements)
• Can replicate specific tables/databases
• Allows different PostgreSQL versions
• Supports bi-directional replication
• Higher overhead than physical replication

Use Cases:

• Selective table replication
• Cross-version replication
• Data transformation during replication

2. Synchronous Replication

┌─────────────┐    WAL + ACK    ┌─────────────┐
│   Primary   │ ◄──────────────► │   Replica   │
│             │   (Confirmed)   │             │
└─────────────┘                 └─────────────┘

Configuration:

synchronous_standby_names = 'replica1'
synchronous_commit = on

Characteristics:

• Primary waits for replica confirmation
• Zero data loss guarantee
• Higher latency for write operations
• Requires very stable network

3. Cascading Replication

┌─────────┐ WAL ┌─────────┐ WAL ┌─────────┐
│ Primary │────►│Replica1 │────►│Replica2 │
└─────────┘     └─────────┘     └─────────┘

Use Cases:

• Reducing network load on primary
• Geographic distribution
• Backup hierarchies

4. Point-in-Time Recovery (PITR)

┌─────────┐ WAL Archive ┌─────────────┐
│ Primary │────────────►│ Archive     │
└─────────┘             │ Storage     │
                        └─────────────┘

Characteristics:

• Continuous archiving of WAL files
• Restore to any point in time
• Primarily for backup/recovery
• Not real-time like streaming

Comparison Table

Method	Real-time	Granularity	Cross-version	Data Loss Risk	Complexity
Streaming Physical	✅ Yes	Database	❌ No	Low (async)	Low
Synchronous Physical	✅ Yes	Database	❌ No	None	Medium
Logical	✅ Yes	Table	✅ Yes	Low	Medium
Cascading	✅ Yes	Database	❌ No	Low	High
PITR	❌ No	Database	❌ No	Variable	Medium

Troubleshooting

Common Issues and Solutions

1. Connection Refused

Error: could not translate host name "primary" to address

Solution: Ensure containers are on the same Docker network

2. Authentication Failed

FATAL: password authentication failed for user "postgres"

Solution: Check pg_hba.conf configuration and password settings

3. No pg_hba.conf Entry

FATAL: no pg_hba.conf entry for replication connection

Solution: Add replication entry to pg_hba.conf:

docker exec pg_primary bash -c "echo 'host replication postgres 0.0.0.0/0 md5' >> /var/lib/postgresql/data/pg_hba.conf"
docker exec pg_primary psql -U postgres -c "SELECT pg_reload_conf();"

4. Replica Not Starting

"root" execution of the PostgreSQL server is not permitted

Solution: Ensure PostgreSQL runs as postgres user, not root

5. WAL Segments Not Found

FATAL: could not receive data from WAL stream

Solution: Increase wal_keep_size or use replication slots

Useful Diagnostic Commands

# Check container status
docker ps -a | grep pg_

# View logs
docker logs pg_primary
docker logs pg_replica

# Check PostgreSQL processes
docker exec pg_primary ps aux | grep postgres

# Test connectivity
docker exec pg_replica pg_isready -h primary -p 5432 -U postgres

Performance Tuning

1. Network Optimization

# postgresql.conf
wal_sender_timeout = 60s
wal_receiver_timeout = 60s
max_wal_size = 1GB

2. Memory Settings

shared_buffers = 256MB          # 1/4 of available RAM
effective_cache_size = 1GB      # Available system cache
maintenance_work_mem = 64MB     # Memory for maintenance operations

3. Checkpoint Tuning

checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 2GB

Security Considerations

1. Network Security

• Use SSL for replication connections
• Restrict access in pg_hba.conf
• Use VPN for cross-datacenter replication

2. Authentication

# Use SCRAM-SHA-256 instead of MD5
host replication postgres 0.0.0.0/0 scram-sha-256

3. SSL Configuration

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

Advanced Scenarios

1. Failover Process

# On replica, promote to primary
docker exec pg_replica pg_ctl promote -D /var/lib/postgresql/data

# Update application connection strings
# Restart old primary as new replica

2. Adding More Replicas

# In docker-compose.yml
replica2:
  image: postgres:15
  container_name: pg_replica2
  # ... similar configuration

3. Backup from Replica

# Backup from replica to reduce primary load
docker exec pg_replica pg_dump -U postgres postgres > backup.sql

Cleanup

# Stop and remove containers
docker-compose down -v

# Remove data directories
sudo rm -rf primary/data/* replica/data/*

# Remove Docker images (optional)
docker rmi postgres:15

Conclusion

Streaming Physical Replication is an excellent choice for:

• ✅ High availability setups
• ✅ Read scaling requirements
• ✅ Disaster recovery scenarios
• ✅ Real-time reporting needs

This method provides a robust, performant, and relatively simple solution for PostgreSQL replication. While it has some limitations (like requiring identical PostgreSQL versions), its benefits make it the most popular replication method for production PostgreSQL deployments.

The setup demonstrated here provides a solid foundation that can be extended with additional features like monitoring, automated failover, and multiple replicas as your requirements grow.

---

References

• PostgreSQL Official Documentation - High Availability
• PostgreSQL Streaming Replication
• pg_basebackup Documentation
• Replication Slots