PostgreSQL is one of the most reliable open-source databases. It provides strong consistency, good performance, and flexible replication features that support high availability (HA) systems.

In this article, we will explore how PostgreSQL replication works, from simple master–replica setups to multi-master clusters and replication lag monitoring.

Why Replication and High Availability Matter

In any production system, downtime means loss of service, users, and money. Replication helps keep your database running even when one server fails.

High Availability (HA) means your system can continue to work even when part of it is broken. PostgreSQL supports HA through replication, where one server (the primary) copies its data to another server (the replica). When the primary fails, the replica can take over, this process is called failover.

Write-Ahead Log (WAL): The Foundation of Replication

Before understanding replication, you need to know about WAL. PostgreSQL writes every data change into a special log called the Write-Ahead Log (WAL) before saving it to disk. This guarantees that no data is lost, even after a crash. Replication in PostgreSQL, both physical and logical, is built on top of WAL.

Types of Replication in PostgreSQL

PostgreSQL supports two main types of replication:

Physical Replication

This is the most common type. It copies WAL files as binary data from the primary to replicas.

It’s very fast and efficient for HA setups.

How it works:

1. Each transaction on the primary is recorded in WAL.
2. The replica reads WAL blocks through a process called streaming replication.
3. The replica replays those logs to stay in sync.

Configuration example:

# On primary
wal_level = replica
max_wal_senders = 10
hot_standby = on

# On replica
primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=secret'

Use case: HA standby, disaster recovery
Limitation: Cannot replicate selected tables or transform data

Logical Replication

Logical replication also uses WAL but in a decoded form. It doesn’t copy binary blocks — instead, it reads WAL, decodes the changes, and sends logical messages like:

{ "action": "INSERT", "table": "users", "data": {"id":1, "name":"Alice"} }

This makes it more flexible:

• You can replicate specific tables
• You can connect different PostgreSQL versions
• You can even replicate to other systems

Setup example:

-- On primary
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- On replica
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=10.0.0.1 dbname=app user=replicator password=secret'
PUBLICATION my_pub;

Use case: Data migration, integration, multi-master
Limitation: Doesn’t automatically replicate DDL (like ALTER TABLE)

Physical vs Logical Summary

Feature	Physical Replication	Logical Replication
WAL Usage	Sends WAL as binary blocks	Decodes WAL into SQL changes
Granularity	Whole database	Specific tables
Version compatibility	Same version	Can differ
Performance	Faster	Slightly slower
Flexibility	Limited	Very flexible

Analogy:
Physical replication copies the entire video file, logical replication watches the video and sends only the important scenes.