PostgreSQL: The World's Most Advanced Open Source Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Primary Server                              Standby Server
┌─────────────────────────────────────┐     ┌─────────────────────────────────────┐
│  Backend Process (handling queries) │     │  Startup Process (applies WAL)      │
│           │                         │     │           ▲                         │
│           ▼                         │     │           │                         │
│  ┌─────────────────┐               │     │  ┌─────────────────┐               │
│  │   WAL Buffer    │               │     │  │  WAL Receiver   │◄──────┐       │
│  │   (in memory)   │               │     │  │  Process        │       │       │
│  └────────┬────────┘               │     │  └─────────────────┘       │       │
│           │                         │     │           │                │       │
│           ▼                         │     │           ▼                │       │
│  ┌─────────────────┐               │     │  ┌─────────────────┐       │       │
│  │   WAL Files     │               │     │  │   WAL Files     │       │       │
│  │  (pg_wal/)      │               │     │  │  (pg_wal/)      │       │       │
│  └────────┬────────┘               │     │  └─────────────────┘       │       │
│           │                         │     │                            │       │
│           ▼                         │     │    Hot Standby Queries     │       │
│  ┌─────────────────┐               │     │  ┌─────────────────┐       │       │
│  │   WAL Sender    │────TCP/IP────────────►│  │   Client Conns  │       │       │
│  │   Process       │               │     │  └─────────────────┘       │       │
│  └─────────────────┘               │     │                            │       │
│                                     │     │                            │       │
└─────────────────────────────────────┘     └────────────────────────────┘
                │
                │  Feedback: flush position, apply position
                ◄───────────────────────────────────────────────────────

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# === On Primary Server ===
 
# 1. Configure postgresql.conf
wal_level = replica                    # Enable replication info in WAL
max_wal_senders = 10                   # Max number of standbys
wal_keep_size = 1GB                    # Retain WAL for slow standbys
hot_standby = on                        # Allow queries on standby
 
# 2. Create replication user
psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';"
 
# 3. Configure pg_hba.conf (allow replication connections)
# host    replication     replicator      standby_ip/32      scram-sha-256
 
# 4. Restart PostgreSQL
pg_ctl restart -D /var/lib/postgresql/data
 
 
# === On Standby Server ===
 
# 1. Stop PostgreSQL if running
pg_ctl stop -D /var/lib/postgresql/data
 
# 2. Clear data directory (IMPORTANT: this deletes existing data)
rm -rf /var/lib/postgresql/data/*
 
# 3. Take base backup from primary
pg_basebackup -h primary_ip -D /var/lib/postgresql/data -U replicator -P -R
 
# The -R flag creates standby.signal and configures recovery settings
 
# 4. Start standby
pg_ctl start -D /var/lib/postgresql/data
 
# Standby is now recovering and accepting read-only queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- On Primary: Check connected standbys
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,
    sync_state  -- 'async', 'sync', 'potential', 'quorum'
FROM pg_stat_replication;
 
-- On Primary: Replication lag in time (approximate)
SELECT 
    client_addr,
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN '0 seconds'
        ELSE NOW() - pg_last_xact_replay_timestamp()
    END AS replication_lag
FROM pg_stat_replication;
 
-- On Standby: Check recovery status
SELECT 
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn() AS last_received,
    pg_last_wal_replay_lsn() AS last_applied,
    pg_last_xact_replay_timestamp() AS last_tx_time;
 
-- Check for WAL files waiting to be applied
SELECT COUNT(*) as pending_wal_files FROM pg_ls_waldir();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# postgresql.conf on Primary
 
# Define synchronous standby policy
# FIRST: wait for first N standbys in priority order
# ANY: wait for any N from the list
 
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
# - Wait for standby1 (or standby2 if standby1 unavailable)
# - standby2 becomes sync if standby1 disconnects
 
# Alternative: require any 2 of 3 standbys
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'
 
# Combined with commit level:
synchronous_commit = on  # Wait for sync standbys to flush
 
# Quorum-based (requires at least 2 standbys):
synchronous_standby_names = 'FIRST 2 (standby1, standby2, standby3)'

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Override for specific transactions (less critical data)
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO audit_log (event, timestamp) VALUES ('user_clicked_button', NOW());
COMMIT;  -- Returns immediately, doesn't wait for standby
 
-- Override for critical transactions (even if default is async)
BEGIN;
SET LOCAL synchronous_commit = remote_apply;
UPDATE accounts SET balance = balance - 1000 WHERE id = 123;
COMMIT;  -- Waits until standby has applied and can serve reads
 
-- Check current setting
SHOW synchronous_commit;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- === On Publisher (source database) ===
 
-- Ensure wal_level supports logical decoding
-- postgresql.conf: wal_level = logical
 
-- Create publication for specific tables
CREATE PUBLICATION sales_pub FOR TABLE orders, customers, products;
 
-- Or publish all tables
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;
 
-- Add/remove tables from publication
ALTER PUBLICATION sales_pub ADD TABLE order_items;
ALTER PUBLICATION sales_pub DROP TABLE products;
 
-- View publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete FROM pg_publication;
 
 
-- === On Subscriber (target database) ===
 
-- Tables must exist with compatible schema (create manually or pg_dump -s)
CREATE TABLE orders (...);
CREATE TABLE customers (...);
 
-- Create subscription (starts initial sync automatically)
CREATE SUBSCRIPTION sales_sub
    CONNECTION 'host=publisher_ip dbname=source user=replicator password=...'
    PUBLICATION sales_pub;
 
-- View subscription status
SELECT subname, subenabled, subslotname FROM pg_subscription;
 
-- View replication state per table
SELECT * FROM pg_subscription_rel;
 
-- Check replication lag
SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

                            ┌─────────────────┐
                            │     Primary     │
                            │   (US-East)     │
                            └────────┬────────┘
                                     │ WAL Stream
                    ┌────────────────┴────────────────┐
                    │                                 │
           ┌────────▼────────┐               ┌────────▼────────┐
           │   Standby #1    │               │   Standby #2    │
           │  (US-East DC2)  │               │   (EU-West)     │
           └────────┬────────┘               └────────┬────────┘
                    │                                 │
                    │ Cascade                         │ Cascade
                    │                                 │
           ┌────────▼────────┐               ┌────────▼────────┐
           │   Standby #3    │               │   Standby #4    │
           │    (US-West)    │               │   (Asia-Pac)    │
           └─────────────────┘               └─────────────────┘
 
Benefits:
- Standby #1/#2 handle cascade load, primary focuses on writes
- Regional standbys serve local reads with low latency
- Reduces cross-ocean bandwidth from primary

1
2
3
4
5
6
7
8

# On Cascading Source Standby (Standby #1)
# postgresql.conf additions:
hot_standby = on
max_wal_senders = 5  # Allow this standby to send WAL
 
# On Downstream Standby (Standby #3)
# pg_basebackup from Standby #1, not Primary:
pg_basebackup -h standby1_ip -D /var/lib/postgresql/data -U replicator -P -R

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- On Delayed Standby's postgresql.conf
-- (or postgresql.auto.conf via ALTER SYSTEM)
recovery_min_apply_delay = '1 hour'
 
-- This standby will always be 1 hour behind the primary
-- If disaster occurs, you have 1 hour to:
-- 1. Stop replication
-- 2. Promote the delayed standby 
-- 3. Extract the data before the destructive operation
 
-- Common delay values:
-- 15 minutes: Quick recovery for operational errors
-- 1 hour: Buffer for detection and response
-- 24 hours: Overnight protection for major changes
 
-- Check delay on standby
SELECT 
    pg_last_wal_replay_lsn(),
    pg_last_xact_replay_timestamp(),
    NOW() - pg_last_xact_replay_timestamp() AS actual_delay;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

# patroni.yml - Patroni configuration for high availability
 
scope: postgres-cluster
namespace: /service/
name: postgresql-node1
 
restapi:
  listen: 0.0.0.0:8008
  connect_address: node1:8008
 
etcd3:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379
 
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB - don't failover to far-behind replica
    postgresql:
      use_pg_rewind: true  # Enable fast rejoining after failover
      parameters:
        max_connections: 200
        shared_buffers: 2GB
        wal_level: replica
        hot_standby: on
        max_wal_senders: 10
        synchronous_commit: on
        synchronous_standby_names: '*'  # Require sync replica
 
  initdb:
    - encoding: UTF8
    - data-checksums
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    replication:
      username: replicator
      password: secret
    superuser:
      username: postgres
      password: secret

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

                    ┌──────────────────────────────────────────┐
                    │            Load Balancer (HAProxy)       │
                    │  Reads port 5001 → all nodes             │
                    │  Writes port 5000 → primary only         │
                    └──────────────────┬───────────────────────┘
                                       │
         ┌─────────────────────────────┼─────────────────────────────┐
         │                             │                             │
         ▼                             ▼                             ▼
┌─────────────────┐         ┌─────────────────┐         ┌─────────────────┐
│    Node 1       │         │    Node 2       │         │    Node 3       │
│   (Primary)     │         │   (Replica)     │         │   (Replica)     │
│                 │         │                 │         │                 │
│ ┌─────────────┐ │         │ ┌─────────────┐ │         │ ┌─────────────┐ │
│ │ PostgreSQL  │ │◄───WAL──┤ │ PostgreSQL  │ │◄───WAL──┤ │ PostgreSQL  │ │
│ └─────────────┘ │         │ └─────────────┘ │         │ └─────────────┘ │
│ ┌─────────────┐ │         │ ┌─────────────┐ │         │ ┌─────────────┐ │
│ │   Patroni   │◄├─────────┤►│   Patroni   │◄├─────────┤►│   Patroni   │ │
│ └──────┬──────┘ │         │ └──────┬──────┘ │         │ └──────┬──────┘ │
└────────┼────────┘         └────────┼────────┘         └────────┼────────┘
         │                           │                           │
         └───────────────────────────┼───────────────────────────┘
                                     │
                    ┌────────────────▼────────────────┐
                    │      etcd Cluster (Consensus)   │
                    │   Stores leader lock, config    │
                    └─────────────────────────────────┘
 
Failover Process:
1. Primary becomes unreachable
2. Patroni on replicas detect via etcd leader lock expiry
3. Most up-to-date replica acquires leader lock
4. Patroni promotes that replica to primary
5. Other replicas reconfigure to follow new primary
6. HAProxy health checks detect change, route writes to new primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- Comprehensive replication health check
SELECT 
    client_addr AS standby_ip,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS flush_lag,
    pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) AS replay_lag,
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() 
        THEN interval '0 seconds'
        ELSE now() - pg_last_xact_replay_timestamp() 
    END AS time_lag
FROM pg_stat_replication;
 
-- Check replication slot health
SELECT 
    slot_name,
    slot_type,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
    CASE 
        WHEN active THEN 'Healthy'
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824 
        THEN 'CRITICAL: >1GB pending'
        ELSE 'Inactive'
    END AS status
FROM pg_replication_slots;
 
-- WAL generation rate (for capacity planning)
SELECT 
    pg_size_pretty(sum(size)) AS wal_24h,
    pg_size_pretty(sum(size) / 24) AS wal_per_hour
FROM pg_ls_waldir()
WHERE modification > now() - interval '24 hours';