PostgreSQL Replication and High Availability

PostgreSQL provides robust data replication and high availability (HA) capabilities, allowing organizations to build resilient architectures that guarantee data durability and minimal downtime. Replication in PostgreSQL is categorized into physical replication (WAL-based byte-level streaming) and logical replication (table-level change publishing). These mechanisms can be deployed individually or combined to meet diverse business requirements. This article explores the configuration of streaming replication, the implementation of logical replication, and production-grade automated failover strategies.

• Physical Replication

  • Operates at the block level by copying Write-Ahead Log (WAL) segments.
  • Supports both synchronous and asynchronous operational modes.
  • Replicates an entire database cluster, making it ideal for disaster recovery and read-scale architectures.

• Logical Replication

  • Employs a publish-and-subscribe model targeting specific tables or schemas.
  • Offers granular control, enabling data synchronization across different major versions or distinct architectures.
  • Frequently utilized for data migrations, data sharding, and real-time analytics feeds.

Streaming Replication Configuration

The following guide demonstrates setting up synchronous streaming replication between two nodes, designated as master and standby, using PostgreSQL 15.

1. Prerequisites

# Install identical PostgreSQL versions on both hosts
apt-get install postgresql-15

# Ensure time synchronization between nodes via NTP
timedatectl set-ntp true

2. Master Node Configuration

Modify postgresql.conf (typically located at /etc/postgresql/15/main/postgresql.conf):

# Listen on all network interfaces (restrict to internal subnets in production)
listen_addresses = '*'

# Enable WAL configuration for replication
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024   # Measured in MB to prevent premature WAL recycling
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

Update pg_hba.conf to grant connection permissions to the standby node:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.10.0/24         md5

Create a dedicated replication role in the database:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongPass123';

Restart the master service to apply changes:

systemctl restart postgresql

3. Base Backup Execution

Execute pg_basebackup on the standby host to clone the master's data directory:

pg_basebackup -h 192.168.10.1 -D /var/lib/postgresql/15/main -U replicator -P --wal-method=stream

Upon completion, a standby.signal file is automatically generated in the data directory, designating the instance as a warm or hot standby.

4. Standby Node Configuration

Configure postgresql.conf on the standby instance:

primary_conninfo = 'host=192.168.10.1 port=5432 user=replicator password=StrongPass123 application_name=standby1'
primary_slot_name = 'standby1_slot'   # Optional, utilizes a replication slot to avoid WAL loss

If utilizing a replication slot, create it on the master node first:

SELECT * FROM pg_create_physical_replication_slot('standby1_slot');

Start the standby service:

systemctl start postgresql

Running SELECT pg_is_in_recovery(); on the standby should now return true, confirming it is operating in recovery mode.

5. Verifying Replication Status

Execute the following query on the master node:

SELECT client_addr, state, sync_state FROM pg_stat_replication;

• A state of streaming indicates active log shipping.
• A sync_state of sync confirms synchronous operation (when synchronous_standby_names is configured).

Implementing Synchronous Replication

PostgreSQL defaults to asynchronous replication, where transaction commits return immediately without waiting for standby acknowledgment. To enforce synchronous replication, specify the standby identifier in postgresql.conf:

synchronous_standby_names = 'standby1'

While synchronous replication introduces minor transaction latency overhead, it guarantees zero data loss upon master failure.

Logical Replication Example

To stream a single table, such as public.sales, to a reporting replica:

1. Master Publication Setup

-- Set WAL level to logical
ALTER SYSTEM SET wal_level = logical;
SELECT pg_reload_conf();

-- Create a publication for the target table
CREATE PUBLICATION sales_pub FOR TABLE public.sales;

2. Standby Subscription Configuration

Establish the subscription on the target database instance:

CREATE SUBSCRIPTION sales_sub
CONNECTION 'host=192.168.10.1 port=5432 dbname=shop user=replicator password=StrongPass123'
PUBLICATION sales_pub;

The database engine automatically copies existing data and continuously replicates subsequent INSERT, UPDATE, and DELETE operations.

High Availability Architecture Overview

Patroni Workflow Mechanics

1. Health Probing: Each PostgreSQL instance runs a Patroni agent that periodically evaluates local node health and replication lag.
2. Distributed Locking: Using Etcd, Consul, or ZooKeeper, Patroni maintains a distributed leader key to ensure only one primary accepts writes at any time.
3. Failure Detection: If the primary drops its lock renewal or fails health checks, standby agents contend to acquire the leadership lease.
4. Promotion: The newly elected node promotes itself via pg_ctl promote, updates the metadata store, and directs proxy layers like HAProxy to reroute write traffic.

Minimal Deployment Example (Docker Compose)

version: '3.7'
services:
  etcd:
    image: bitnami/etcd:latest
    environment:
      - ALLOW_NONE_AUTHENTICATION=yes
    ports: ["2379:2379"]

  patroni0:
    image: patroni:latest
    environment:
      - PATRONI_NAME=node0
      - PATRONI_SCOPE=pgcluster
      - PATRONI_ETCD_HOSTS=etcd:2379
      - PATRONI_REPLICATION_USERNAME=replicator
      - PATRONI_REPLICATION_PASSWORD=StrongPass123
    depends_on: [etcd]

  patroni1:
    image: patroni:latest
    environment:
      - PATRONI_NAME=node1
      - PATRONI_SCOPE=pgcluster
      - PATRONI_ETCD_HOSTS=etcd:2379
      - PATRONI_REPLICATION_USERNAME=replicator
      - PATRONI_REPLICATION_PASSWORD=StrongPass123
    depends_on: [etcd]

Troubleshooting and Tuning Best Practices

• Exhaustion of Disk Space Caused by WAL Growth

  • Tune wal_keep_size and max_wal_size, and activate archive_command to offload segments to durable external storage.
  • Leverage analysis utilities like pgBadger to inspect workload patterns.

• Replication Lag

  • Monitor network bandwidth and storage subsystem throughput.
  • Enable hot_standby_feedback = on on standby servers to prevent long-running read queries from causing vacuum conflicts on the primary.

• Logical Replication Conflicts

  • Address concurrent conflicting mutations by configuring conflict resolution rules or applying row filters on publications.

• False Positive Failover Triggers

  • Adjust lease ttl and retry_timeout parameters in distributed configuration stores to prevent transient network partitions from forcing unnecessary failovers.

Summary

PostgreSQL offers a mature and production-ready framework for replication and high availability. Successful implementations depend on selecting the correct replication paradigm, defining clear consistency models, integrating automated failover orchestration, and maintaining proactive telemetry. Mastering these concepts ensures reliable, scalable database services capable of sustaining mission-critical workloads.