Database Replication

◆Databases◆Intermediate to Advanced🎯Free Tools

Introduction

Database replication copies data from one database (primary) to one or more replicas. It enables read scaling, high availability, disaster recovery, and geographic distribution. The key challenge is maintaining consistency across replicas while handling network partitions and failures.

Primary-Replica (Leader-Follower)

The most common pattern: one primary handles all writes, replicas serve read queries. The primary streams its write-ahead log (WAL) to replicas, which replay it to stay in sync.

primary-replica.sql

SQL

1	-- PostgreSQL primary-replica setup
2
3	-- On PRIMARY (postgresql.conf):
4	-- wal_level = replica
5	-- max_wal_senders = 10
6	-- wal_keep_size = '1GB'
7
8	-- On PRIMARY (pg_hba.conf):
9	-- host replication replicator 10.0.0.0/8 scram-sha-256
10
11	-- Create replication user on primary
12	CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';
13
14	-- On REPLICA:
15	-- pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P
16
17	-- Create standby.signal file on replica
18	-- postgresql.conf on replica:
19	-- primary_conninfo = 'host=primary-host user=replicator password=secure_password'
20	-- hot_standby = on
21
22	-- Monitor replication status on primary
23	SELECT
24	client_addr,
25	state,
26	sent_lsn,
27	write_lsn,
28	replay_lsn,
29	replay_lag
30	FROM pg_stat_replication;

ℹ

info

Use replay_lagto monitor replica health. A growing lag means the replica can't keep up with the primary's write throughput. Consider upgrading the replica or reducing read traffic.

Change Data Capture (CDC)

CDC captures row-level changes from the database and streams them to other systems (Kafka, Elasticsearch, analytics). Unlike replication, CDC is designed for downstream consumers, not database sync.

docker-compose.yml

Bash

1	# Debezium CDC with Kafka Connect
2	# docker-compose.yml
3	version: '3.8'
4	services:
5	zookeeper:
6	image: quay.io/debezium/zookeeper:2.4
7	kafka:
8	image: quay.io/debezium/kafka:2.4
9	environment:
10	ZOOKEEPER_CONNECT: zookeeper:2181
11	KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
12
13	postgres:
14	image: postgres:16
15	environment:
16	POSTGRES_DB: myapp
17	POSTGRES_PASSWORD: secret
18	command: >
19	postgres
20	-c wal_level=logical
21	-c max_replication_slots=4
22	-c max_wal_senders=4
23
24	debezium:
25	image: quay.io/debezium/connect:2.4
26	environment:
27	BOOTSTRAP_SERVERS: kafka:9092
28	GROUP_ID: 1
29	CONFIG_STORAGE_TOPIC: connect-configs
30	OFFSET_STORAGE_TOPIC: connect-offsets

logical-replication.sql

SQL

1	-- PostgreSQL logical replication (built-in CDC)
2	-- On primary:
3	CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
4
5	-- On replica/consumer:
6	CREATE SUBSCRIPTION orders_sub
7	CONNECTION 'host=primary-host dbname=myapp user=replicator password=secret'
8	PUBLICATION orders_pub;
9
10	-- Verify subscription
11	SELECT * FROM pg_stat_subscription;

✓

best practice

Use Debezium for complex CDC pipelines (Kafka, Elasticsearch). Use PostgreSQL's native logical replication for simpler database-to-database sync without external dependencies.

Conflict Resolution

conflicts.sql

SQL

1	-- Multi-primary (multi-writer) replication requires conflict resolution
2
3	-- Strategy 1: Last-Write-Wins (LWW)
4	-- Uses timestamps; last writer overwrites earlier changes
5	-- Simple but can lose data
6
7	-- Strategy 2: Conflict-free Replicated Data Types (CRDTs)
8	-- Data structures that merge automatically without conflicts
9	-- Used by: Redis, Riak, AntidoteDB
10
11	-- Strategy 3: Application-level resolution
12	-- Custom logic to merge conflicting changes
13	-- Most flexible but most complex
14
15	-- PostgreSQL: conflict detection in logical replication
16	ALTER SUBSCRIPTION orders_sub SET (log_min_messages = 'debug1');
17
18	-- Handle conflicts with origin tracking
19	SELECT * FROM pg_replication_origin_status;
20
21	-- Best practices for avoiding conflicts:
22	-- 1. Shard by primary (each row has one primary writer)
23	-- 2. Use UUIDs instead of serial IDs
24	-- 3. Design for append-only operations when possible
25	-- 4. Use optimistic locking (version columns)

Failover & High Availability

patroni.yml

Bash

1	# Automatic failover with Patroni (PostgreSQL)
2	# patroni.yml
3	scope: postgres-cluster
4	name: node1
5	restapi:
6	listen: 0.0.0.0:8008
7	connect_address: 10.0.0.1:8008
8	etcd3:
9	hosts: 10.0.0.10:2379,10.0.0.11:2379,10.0.0.12:2379
10	bootstrap:
11	dcs:
12	ttl: 30
13	loop_wait: 10
14	retry_timeout: 10
15	maximum_lag_on_failover: 1048576
16	initdb:
17	- encoding: UTF8
18	- data-checksums
19	postgresql:
20	listen: 0.0.0.0:5432
21	connect_address: 10.0.0.1:5432
22	authentication:
23	replication:
24	username: replicator
25	password: secret
26	superuser:
27	username: postgres
28	password: secret
29
30	# Patroni automatically:
31	# 1. Elects a leader (primary)
32	# 2. Configures replicas
33	# 3. Detects failures
34	# 4. Promotes a new primary
35	# 5. Reconfigures the old primary as a replica when it recovers

⚠

warning

Automatic failover requires careful configuration. A false positive (promoting a replica when the primary is just slow) can cause split-brain. Always set maximum_lag_on_failover and test failover scenarios regularly.

$Blueprint — Engineering Documentation·Section ID: DB-RP-01·Revision: 1.0

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor