PostgreSQL Replication

Replication keeps one or more copies of a database on other servers. The main server accepts changes. The copies, called replicas, follow along and apply the same changes.

  Primary server  ---- changes ---->  Replica 1
   (read + write)           |
                            +------>  Replica 2
                                      (read only)

Imagine a teacher who writes notes on the board while two assistants copy every line into their notebooks. If the teacher falls ill, an assistant can continue the class from the copied notes.

Why Use Replication

• High availability: a replica takes over when the primary fails.
• Read scaling: reports and searches run on replicas and keep the primary free.
• Safety: copies protect against hardware loss.
• Maintenance: upgrades and tests run on a copy without risk.

Replication does not replace backups. A mistaken DELETE on the primary copies itself to every replica within moments.

The Write-Ahead Log

PostgreSQL writes every change to a file called the write-ahead log, or WAL, before it changes the data files. The log works like a diary of everything that happened. Replicas read this diary and repeat each entry in the same order.

  Change --> WAL record --> sent to replica --> replica replays it

Two Main Types

PostgreSQL offers two replication styles. They differ in what they copy and how much freedom the replica has.

Physical Streaming Replication

The replica copies the whole cluster byte by byte through the WAL stream. It mirrors every database, table, and role of the primary. Replicas accept read-only queries.

Logical Replication

The replica receives row changes for chosen tables. It can hold different tables of its own, run a different PostgreSQL version, and accept writes on tables that are not being copied.

Feature	Physical	Logical
What copies	Whole cluster	Selected tables
Replica writes	Read only	Allowed on other tables
Version difference	Must match	Can differ
Best for	Failover and read scaling	Migrations and data sharing

Setting Up a Streaming Replica

A working replica needs a prepared primary and a copy of its data. The steps below show the full path on two Linux servers.

Step 1: Configure the Primary

Edit postgresql.conf.

wal_level = replica
max_wal_senders = 10

Create a role dedicated to replication.

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

Allow the replica to connect by adding a line to pg_hba.conf, then reload the server.

host replication replicator 192.168.1.20/32 scram-sha-256

Step 2: Copy the Data to the Replica

Run pg_basebackup on the replica machine with an empty data folder.

pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/data -R -P

The -R flag writes the connection settings and creates a standby.signal file. The replica starts in standby mode, connects to the primary, and begins to stream changes.

Operating Replication

A running replica needs monitoring, a chosen commit mode, and a plan for failure.

Checking Replication Health

Run this query on the primary.

SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

A row with state streaming means the replica is connected. A large gap between sent_lsn and replay_lsn means the replica lags behind. Run this query on a replica to confirm its role.

SELECT pg_is_in_recovery();

The answer true marks a replica.

Synchronous and Asynchronous Modes

An asynchronous setup lets the primary confirm a commit without waiting for the replica. Writes run fast, but a crash can lose the last few changes. A synchronous setup makes the primary wait until a replica confirms receipt. No committed change disappears, and each commit takes slightly longer.

  Async: commit --> done           (replica catches up later)
  Sync:  commit --> replica OK --> done

Logical Replication Example

Publish a table on the source server.

CREATE PUBLICATION shop_pub FOR TABLE orders, customers;

Subscribe from the target server. The tables must already exist there.

CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=192.168.1.10 dbname=shop user=replicator password=strong_password'
  PUBLICATION shop_pub;

Every INSERT, UPDATE, and DELETE on orders and customers now flows to the target.

Promoting a Replica

When the primary fails, promote a replica so it accepts writes.

SELECT pg_promote();

Point your applications to the new primary. Tools such as Patroni automate failover and prevent two servers from claiming the primary role at once.

Good Habits

• Monitor replication lag.
• Keep regular backups next to replicas.
• Restrict replication access by IP address and strong passwords.
• Test the failover process before an emergency.