Implement Replication with Patroni, etcd and Haproxy

Introduction

• Database replication is the process of automatically copying and synchronizing data from one database server (called Master or Primary) to one or more other servers (called Slave, Standby or Replica).
• Thus, the way it works is when there are data modification operations on the Master, the changes will be executed equivalently on the Slave.

Advantages

If you only run a single Database node, you will face many risks that Replication can resolve as follows:

• High Availability (HA): If the Master node suffers a hardware failure or power outage, an active Slave node will immediately be elected as the new Master, which is the Failover mechanism helping the system continue running without disruption.
• Read Scalability: Data writing queries (INSERT, UPDATE, DELETE) must be sent to the Master. But reading queries (SELECT), which account for most of the system load demand, can be evenly distributed among the Slaves for processing.
• Disaster Recovery: You can place a Slave in a completely different geographical Datacenter. If the entire main Datacenter goes down, the data remains safe in the backup Datacenter.

How It Works

In PostgreSQL and most other Databases, when you perform a modification such as INSERT, UPDATE, DELETE, Postgres does not write directly into the HEAP file immediately, because this operation has to perform data checks and determine the location of the Page before writing, which makes it very slow. Instead:

• It writes this modification behavior into a log file called WAL file first (because it is a sequential write, the speed is extremely fast).
• After that, it gradually updates the actual data file on the hard drive.
• The Replication process operates based on this exact WAL file:
  • Writing at Master: The Master node receives the write command from the application, performs the changes and generates WAL records.
  • Transmission: A process on the Master, called WalSender, continuously reads these new WAL segments and sends them over the network to the Slave node.
  • Applying at Slave: On the Slave node, a process called WalReceiver catches these WAL segments and replays those operations on its hard drive. Thanks to this, the Slave's data is always identical to the Master's.

Classification

Based on how the Master confirms data writing and the method of transmission, replication is divided into the following types:

Synchronous and Asynchronous

This is the classic trade-off choice between Speed and Data Safety:

• Asynchronous
  • Execution: This is the default choice operating in such a way that when the Master finishes writing data into its WAL, it immediately reports success to the Application. Sending the WAL to the Slave is run in the background.
  • Advantages: Extremely fast, not affected by network latency between Master and Slave.
  • Disadvantages: If the Master suddenly loses power right when the WAL has not yet been transmitted to the Slave, that part of the data will be lost.
• Synchronous
  • Execution: The Master finishes writing data, sends the WAL to the Slave. It must wait for the Slave to receive and finish processing the WAL file before responding with success.
  • Advantages: Never lose data. Data at the Master and Slave are always 100% matching at any given time.
  • Disadvantages: Transactions are slowed down because they must wait for the network response between servers. If the Slave goes down, the Master will also be suspended (no longer allowing writes) to wait for the Slave.

Physical and Logical

Based on the content of the data being transmitted:

• Physical: operates under the Primary - Standby (or Master - Slave) model
  • The Primary node treats the Standby nodes as exact copies of itself and does not care what tables or databases are inside the Standby. It simply opens a data transmission line to transmit raw bytes from the WAL file over the network.
  • At the Standby Node, it will replay those raw bytes to write data to the disk.
  • Characteristics:
    • Hard-locked: The Standby must be 100% identical to the Primary from system configuration, database list down to every single byte on the hard disk.
    • No independence: The Standby node has no autonomy, it only operates in a Read-Only state because any direct write behavior into the Standby such as editing or creating additional separate tables will skew the binary structure and corrupt the replication chain.
• Logical: operates under the Publisher - Subscriber model
  • The Publisher node will gather data changes, translate them into data modification logic (DML) and then perform publication to the corresponding Subscribers.
  • At the Subscriber node, it will actively perform Subscription to receive packets from the Publisher.
    • Because the transmitted data is logical information (SQL-like) rather than raw bytes, it can operate with higher flexibility compared to using Physical Replication.
    • After receiving the information, the Subscriber node will analyze and decide its own processing method for that data.
  • Advantages
    • You can customize to only replicate a few arbitrary tables to specific Subscriber Nodes.
    • You can still create new tables on the Subscriber or perform data operations without causing any conflict or affecting the Publisher node.
    • The Subscriber node is an independent entity, having its own file system, capable of running a completely different operating system version or Postgres version compared to the Publisher, supporting well in zero-downtime upgrade.

Tools

Patroni

It is the tool used to manage Postgres nodes. It monitors the status, replication configuration (Streaming Replication) and automatically performs Failover (electing a Slave node up to be Master when the old Master node is inactive).

etcd (etc distributed)

• It is a mini, ultra-fast and extremely reliable distributed database (using the Raft consensus algorithm), used as a Distributed Key-Value Store.
• It is a ledger storing configuration information and state of the entire system in an extremely secure manner. By running in a cluster, etcd ensures that even if a few servers containing etcd suffer hardware failure, the configuration data in this ledger remains intact and absolutely consistent across the remaining nodes.
• Within the Patroni cluster, etcd performs the following duties:
  • Distributed Consensus Store: saves accurate information of the Nodes including IP addresses, which node is Master or Replica.
  • Leader Election: When the cluster boots up, whichever node registers a lock on etcd the fastest becomes the Master. This lock has a Time To Live (TTL). The Master node must continuously send keep-alive signals to etcd to renew this lock.
  • Triggering Failover to automatically failover when an incident occurs:
    • If the Master node goes down, it can no longer renew the lock on etcd.
    • The Master lock on etcd will expire and disappear.
    • Immediately, etcd will inform the remaining Replica nodes. The Replica nodes will organize a new "election" through etcd to choose a new Master.
• Thus, etcd and Patroni will operate in coordination with each other, where Patroni performs the action and etcd responds with the result. Without etcd, Patroni would not be able to know which node is Master or Replica and could not automatically rescue when the Master dies.

HAProxy (High Availability Proxy)

• It acts as an intermediary and provides a Single Point of Entry for connections from Apps.
• Your App only needs to connect to the unique IP of HAProxy with 2 fixed ports:
  • Port 5000 (Write Only/Master): HAProxy receives the request, asks etcd/Patroni who the Master is and then forwards the connection straight to that node.
  • Port 5001 (Read Only/Replica): HAProxy receives the request, then distributes them evenly (Round Robin) to the Replica nodes to reduce the load on the Master.
• Health Check: HAProxy will continuously send requests to port 8008 of Patroni on each node to check if the Master/Replica is still active.
  • If a node is no longer active, HAProxy will remove that node from the routing list, ensuring end users do not encounter connection errors.
  • When an old Master node is no longer active or is no longer the Master, HAProxy will actively cut all old connections currently connecting to that node, forcing the App to reconnect to the new Master.

etcd and HAProxy

We need to use both etcd and HAProxy because

• etcd only supports HTTP/gRPC protocols, used for reading/writing Key-Value pairs, it does not know how to redirect database connections and cannot receive an SQL command then forward it to another machine.
• Therefore, there must be HAProxy operating with the TCP protocol, to directly transmit the SQL query data of Postgres.
  • Used for Port Forwarding to receive SQL connections at port 5000 and forward it to the correct Master machine.
  • Serves directly for your App/Code to connect and work.

Prerequisites

• Similar to when applying Sharding, Replication is also a solution used in a Distributed System.
• If your demand is to improve performance for querying on large datasets, you should consider Query Tuning, using appropriate Index types or Table Partitioning as options to apply first.
• If using only 1 database node becomes a SPOF (Single Point of Failure) when data access demand is too huge and you do not want the system to have downtime when the database has issues, then you need to think about Replication.

Detail

First of all, let us create the patroni/patroni.yml file used to configure Patroni as follows

scope: postgres-cluster
namespace: /service

etcd3:
  hosts:
    - etcd:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        shared_buffers: 128MB
        max_connections: 100
        hot_standby: "on"
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby_feedback: "on"
  
  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5

restapi:
  listen: 0.0.0.0:8008
  connect_address: localhost:8008

postgresql:
  listen: 0.0.0.0:5432
  connect_address: localhost:5432
  data_dir: /var/lib/postgresql/data
  pgpass: /var/lib/postgresql/.pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: superuser_password

Next is the configuration information for haproxy/haproxy.cfg

global
    maxconn 4096

defaults
    log     global
    mode    tcp
    timeout connect 4s
    timeout client  30m
    timeout server  30m

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 5s

frontend postgres_master_front
    bind *:5000
    default_backend postgres_master_back

frontend postgres_replica_front
    bind *:5001
    default_backend postgres_replica_back

backend postgres_master_back
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    
    server pg-node1 pg-node1:5432 maxconn 100 check port 8008
    server pg-node2 pg-node2:5432 maxconn 100 check port 8008
    server pg-node3 pg-node3:5432 maxconn 100 check port 8008

backend postgres_replica_back
    mode tcp
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2
    
    server pg-node1 pg-node1:5432 maxconn 100 check port 8008
    server pg-node2 pg-node2:5432 maxconn 100 check port 8008
    server pg-node3 pg-node3:5432 maxconn 100 check port 8008

Here, please pay attention to the information

• stats is the Dashboard of HAProxy at port 7000
• postgres_master_front is the connection information to Master Only at port 5000
• postgres_replica_front to connect to Replica Only, port 5001

Create a docker-compose.yml file containing the necessary services

services:
  etcd:
    image: gcr.io/etcd-development/etcd:v3.5.0
    container_name: etcd
    command:
      - /usr/local/bin/etcd
      - --advertise-client-urls=http://etcd:2379
      - --listen-client-urls=http://0.0.0.0:2379
    ports:
      - "2379:2379"
    networks:
      - pg-net

  pg-node1:
    image: patroni-postgres-custom:latest
    build:
      context: .
      dockerfile_inline: |
        FROM postgres:alpine
        RUN apk update && apk add --no-cache python3 py3-pip bash shadow
        RUN python3 -m venv /opt/patroni-env
        ENV PATH="/opt/patroni-env/bin:$PATH"
        RUN pip install --no-cache-dir --upgrade pip && \
            pip install --no-cache-dir psycopg2-binary patroni[etcd3]
        RUN chown -R postgres:postgres /opt/patroni-env /var/lib/postgresql
        USER postgres
        ENTRYPOINT ["patroni", "/opt/patroni/patroni.yml"]
    container_name: pg-node1
    hostname: pg-node1
    environment:
      PATRONI_NAME: pg-node1
      PATRONI_RESTAPI_CONNECT_ADDRESS: pg-node1:8008
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: pg-node1:5432
    volumes:
      - ./patroni/patroni.yml:/opt/patroni/patroni.yml
      - pg_node1_data:/var/lib/postgresql/data
    depends_on:
      - etcd
    networks:
      - pg-net

  pg-node2:
    image: patroni-postgres-custom:latest
    container_name: pg-node2
    hostname: pg-node2
    environment:
      PATRONI_NAME: pg-node2
      PATRONI_RESTAPI_CONNECT_ADDRESS: pg-node2:8008
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: pg-node2:5432
    volumes:
      - ./patroni/patroni.yml:/opt/patroni/patroni.yml
      - pg_node2_data:/var/lib/postgresql/data
    depends_on:
      - etcd
      - pg-node1
    networks:
      - pg-net

  pg-node3:
    image: patroni-postgres-custom:latest
    container_name: pg-node3
    hostname: pg-node3
    environment:
      PATRONI_NAME: pg-node3
      PATRONI_RESTAPI_CONNECT_ADDRESS: pg-node3:8008
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: pg-node3:5432
    volumes:
      - ./patroni/patroni.yml:/opt/patroni/patroni.yml
      - pg_node3_data:/var/lib/postgresql/data
    depends_on:
      - etcd
      - pg-node1
    networks:
      - pg-net

  haproxy:
    image: haproxy:latest
    container_name: haproxy
    ports:
      - "5000:5000"
      - "5001:5001"
      - "7000:7000"
    volumes:
      - ./haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    depends_on:
      - pg-node1
      - pg-node2
      - pg-node3
    networks:
      - pg-net

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    ports:
      - "8080:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}
    networks:
      - pg-net
    depends_on:
      - haproxy

networks:
  pg-net:
    driver: bridge

volumes:
  pg_node1_data:
  pg_node2_data:
  pg_node3_data:

• You can see that we will start etcd, haproxy, pgadmin.
• As for Patroni, it will start 3 services corresponding to 3 nodes which are pg-node1, pg-node2, pg-node3.

Start the services up as follows

docker compose up -d

After that, check the Patroni cluster, you will see specific information for each Node including the role like Leader or Replica and State as follows

$ docker exec -it pg-node1 patronictl -c /opt/patroni/patroni.yml list

+ Cluster: postgres-cluster (7663356298177036318) -------------+-----+------------+-----+
| Member   | Host     | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+----------+---------+-----------+----+-------------+-----+------------+-----+
| pg-node1 | pg-node1 | Leader  | running   |  1 |             |     |            |     |
| pg-node2 | pg-node2 | Replica | streaming |  1 |   0/4000060 |   0 |  0/4000060 |   0 |
| pg-node3 | pg-node3 | Replica | streaming |  1 |   0/4000060 |   0 |  0/4000060 |   0 |
+----------+----------+---------+-----------+----+-------------+-----+------------+-----+

Or you can view it directly on the Dashboard of HAProxy.

To check if HAProxy and etcd can operate automatically, when the Master is turned off, it will automatically find another Slave to replace the Master, please turn off the pg-node1 service

docker compose stop pg-node1

Checking again, you will see that the Master is now another Node.

After that, start pg-node1 back up

docker compose start pg-node1

Then at this time, that Node will become a Replica.

Next, connect to the Database through HAProxy as follows, using port 5000 to the Master Node. You should use this method because no matter which Node is currently the Master, HAProxy will automatically route you to that node without any additional operations.

After that, create a table and seed data as follows

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INT NOT NULL DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO products (product_name, price, stock_quantity, status)
SELECT 
    (ARRAY['Product Alpha', 'Gadget Pro', 'Smart Widget', 'Eco Item', 'Super Device', 'Ultra Gear'])[floor(random() * 6) + 1] || ' ' || i AS product_name,
    ROUND((random() * 1000 + 5)::numeric, 2) AS price,
    floor(random() * 501)::int AS stock_quantity,
    CASE 
        WHEN rand < 0.80 THEN 'active'
        WHEN rand < 0.95 THEN 'inactive'
        ELSE 'archived'
    END AS status
FROM generate_series(1, 1000) AS i,
     LATERAL (SELECT random() AS rand) r;

You can connect to the Slave Node via port 5001 of HAProxy to check that the products table has also been synchronized here and you can query data normally.

But when performing INSERT data, it will be blocked due to the Slave Node operating in Read-only mode, so it cannot perform INSERT.

Happy coding!

See more articles here.