Red Orbita
Home Linux & Systems Cybersecurity Cloud & DevOps Networks & Infrastructure SIEM & Monitoring DFIR & Threat Intel Development & Other All categories Projects About Tools

PostgreSQL Installation Manual in High Availability with Patroni and etcd

Leer en espanol
February 1, 2025 Networks & Infrastructure Linux & Systems rokitoh
PostgreSQL Installation Manual in High Availability with Patroni and etcd

Table of contents

This manual is designed to guide you through the process of installing and configuring a highly available PostgreSQL database using Patroni and etcd as a service. ===

1. Introduction

This manual is designed to guide you through the process of installing and configuring a highly available PostgreSQL database using Patroni and etcd as a coordination service. Patroni makes PostgreSQL database cluster management easy and ensures automatic failover, while etcd provides configuration synchronization between cluster nodes.

This type of configuration is ideal for production environments where high availability and resilience to node failures needs to be guaranteed.

2. Prerequisites

Before you begin the installation, make sure you meet the following prerequisites:

  • Operating System: GNU/Linux (In this case we will use Debian).
  • root privileges: Access to an account with superuser permissions to perform system installations and configurations.
  • Grid: Three servers (or more) on the same network with static IP addresses.
  • SSH connectivity: Make sure all nodes can communicate with each other using SSH.
  • Dependencies: You will need tools like curl, wget, and other packages required to download and install components.

2.1 Choice of the Number of Nodes in High Availability

When you configure a PostgreSQL cluster for high availability with Patroni and etcd, one of the key decisions is the number of nodes that will be part of the cluster. The number of nodes has a direct impact on fault tolerance, failover ability, and overall system availability. Below we explain how to calculate the appropriate number of nodes and why it is recommended to use an odd number of nodes in some cases.

How Many Nodes Do I Need?

The minimum recommended number to configure a high availability cluster is three nodes. This number of nodes ensures that the system is robust enough to tolerate failures of at least one node without loss of availability or consensus in the cluster.

To calculate the number of nodes needed in a cluster with etcd, a formula related to the quorum. Quorum is the minimum number of nodes that must be available for the system to reliably make decisions. This formula is the following:

quorum=nodes2+1quorum = \frac{nodes}{2} + 1

For example:

  • With 3 nodes- The quorum is 2 nodes, allowing the system to continue functioning if one of the nodes fails.
  • With 5 nodes- The quorum is 3 nodes, allowing the system to remain operational even if two nodes fail.
  • With 7 nodes- Quorum is 4 nodes, allowing for greater fault tolerance.

Why is it important to have an odd number of nodes?

The use of a number odd of nodes is essential when using consensus technologies, such as etcd either raft, which are responsible for maintaining data consistency in the cluster. This is because consensus depends on a voting process between nodes to make decisions. If there are an even number of nodes, there is a risk that a consensus cannot be reached in the event of a failure, since it could not be determined which node has the majority.

For example:

  • If the cluster has 2 nodes and one of them fails, the other node would be left without a quorum, since it cannot decide for itself whether the fallen node should be promoted or not.
  • With 3 nodes, if one fails, the other two can continue working and decide what to do with the downed node.

Therefore, whenever services of coordination and consensus (like etcd or Consul), it is advisable to have a number odd of nodes in the cluster to ensure that quorum can always be reached and avoid split-brain issues.

When could peer nodes be used?

In some cases, the even nodes may be suitable, but only for certain types of services that do not depend on a consensus process, such as in load distribution or in clusters of Kubernetes where the nodes worker They do not participate in critical consensus decisions. Some examples of these cases are:

  • Load balancers: In services such as HAProxy either NGINX, balancing nodes can be peers, since their objective is to distribute traffic without the need for consensus.
  • Application servers: If the service only needs high availability but a consensus process between nodes is not required, the application nodes can be peers.

Summary

  • For high availability clusters that use Patroni and etcd, 3 nodes is the recommended minimum, and a number should be used odd to ensure consensus and availability in case of failures.
  • 5 nodes It is ideal for larger environments or where higher load is expected, as it provides more fault tolerance.
  • The number of nodes can be pair in services that do not require consensus, such as load balancers or application servers.

3. Installation of the High Availability Database

This step will guide you through installing PostgreSQL and Patroni on three nodes (servers) that will be part of the high availability cluster.

3.0 Installing PostgreSQL, Patroni and etcd

To get started, update your system packages and install the necessary tools:

BASH
sudo apt update && sudo apt install -y postgresql postgresql-contrib patroni etcd-server etcd-client curl wget pgbackrest jq

We will download and install the certificate management tools, necessary to ensure communications between the nodes.

BASH
sudo wget https://pkg.cfssl.org/R1.2/cfssl_linux-amd64 -O /bin/cfssl 
sudo wget https://pkg.cfssl.org/R1.2/cfssljson_linux-amd64 -O /bin/cfssljson 
sudo wget https://pkg.cfssl.org/R1.2/cfssl-certinfo_linux-amd64 -O /bin/cfssl-certinfo
sudo chmod +x /bin/cfssl*

Then modify the file /etc/hosts of each node so that the other servers in the cluster are recognized.

BASH
sudo bash -c 'cat <<EOF >> /etc/hosts
192.168.1.214 srvlropsql01
192.168.1.215 srvlropsql02
192.168.1.216 srvlropsql03
EOF'

3.1 Create a common group to share access to certificates and keys

To allow services like postgres and etcd share access to the necessary certificate and key files, you can create a common group and add the corresponding users to that group.

Create the common group

You can create a group called, for example, db-etcd, which will have access to the certificates and keys necessary for both services.

BASH
sudo groupadd db-etcd

Add users to the group

Add users postgres and etcd to the group db-etcd. This will allow both services to access the key files on a shared basis.

BASH
sudo usermod -aG db-etcd postgres
sudo usermod -aG db-etcd etcd

3.2 etcd-Server configuration

Certificate Generation (Manual)

To secure communications between cluster nodes, it is necessary to generate SSL certificates. Certificates are generated by cfssl (CloudFlare's PKI toolkit).

  • Create the necessary directories and enter the directory where you will store the certificates.

BASH
mkdir /etc/etcd  &&  mkdir ~/etcd-ca && cd ~/etcd-ca
  • Create the Certificate Authority (CA) using cfssl.
BASH
echo '{"CN":"CA","key":{"algo":"rsa","size":2048}}' | cfssl gencert -initca - | cfssljson -bare ca -
  • Create the configuration for certificate signing.
BASH
echo '{"signing":{"default":{"expiry":"43800h","usages":["signing","key encipherment","server auth","client auth"]}}}' > ca-config.json
  • Repeat the process for each node in the cluster, using the following script, replacing the values ​​of NAME and ADDRESS for each server:
BASH
export NAME=srvlropsql01
export ADDRESS=192.168.1.214,$NAME
echo '{"CN":"'$NAME'","hosts":[""],"key":{"algo":"rsa","size":2048}}' | cfssl gencert -config=ca-config.json -ca=ca.pem -ca-key=ca-key.pem -hostname="$ADDRESS" - | cfssljson -bare $NAME

Next, transfer the certificates to the appropriate directory and make sure you set the correct permissions:

BASH
scp ca.pem $NAME:/etc/etcd/etcd-ca.crt
scp $NAME.pem $NAME:/etc/etcd/server.crt
scp $NAME-key.pem $NAME:/etc/etcd/server.key
scp ca* $NAME:/etc/etcd/
ssh $NAME chmod 600 /etc/etcd/server.key
ssh $NAME  sudo chown etcd:db-etcd /etc/etcd/*

Repeat this process for each node in the cluster, changing the values ​​of NAME and ADDRESS as needed.

3.3 etcd configuration (Automated)

If you prefer to automate the process of creating and distributing certificates, you can use the following script. This script creates the certificates and distributes them to all nodes automatically.

BASH
#!/bin/bash

# Crear la CA
echo "Generando la CA"
echo '{"CN":"CA","key":{"algo":"rsa","size":2048}}' | cfssl gencert -initca - | cfssljson -bare ca -
echo '{"signing":{"default":{"expiry":"43800h","usages":["signing","key encipherment","server auth","client auth"]}}}' > ca-config.json

# Lista de servidores y sus direcciones IP
declare -A SERVERS
SERVERS=(
  ["srvlropsql01"]="192.168.1.214"
  ["srvlropsql02"]="192.168.1.215"
  ["srvlropsql03"]="192.168.1.216"
)

# Ruta del archivo CA
CA_PATH="/etc/etcd/"

# Certificados generados
CA_PEM="ca.pem"
CA_KEY="ca-key.pem"

# Iterar sobre cada servidor
for NAME in "${!SERVERS[@]}"; do
  ADDRESS="${SERVERS[$NAME]},$NAME"
   
  # Generar certificado
  echo "Generando certificado de $NAME"
  echo '{"CN":"'$NAME'","hosts":[""],"key":{"algo":"rsa","size":2048}}' | cfssl gencert -config=ca-config.json -ca=$CA_PEM -ca-key=$CA_KEY -hostname="$ADDRESS" - | cfssljson -bare $NAME

  if [ "$NAME" == "srvlropsql01" ]; then
    # Copiar archivos al servidor local
    sudo cp $CA_PEM $CA_PATH/etcd-ca.crt
    sudo cp $NAME.pem $CA_PATH/server.crt
    sudo cp $NAME-key.pem $CA_PATH/server.key
    sudo cp ca* $CA_PATH/
    sudo chmod 600 $CA_PATH/server.key
    sudo chown etcd:db-etcd $CA_PATH/*
  else
    # Copiar archivos a servidores remotos
    scp $CA_PEM $NAME:$CA_PATH/etcd-ca.crt
    scp $NAME.pem $NAME:$CA_PATH/server.crt
    scp $NAME-key.pem $NAME:$CA_PATH/server.key
    scp ca* $NAME:$CA_PATH/
    ssh $NAME "sudo chmod 600 $CA_PATH/server.key"
    ssh $NAME "sudo chown etcd:db-etcd $CA_PATH/*"
  fi
done

This script automates the entire certificate creation and distribution process, ensuring that each node has the appropriate certificates and that permissions are correctly configured.

3.4 Verification of Certificates

Once you have configured the certificates and permissions, verify the validity of the certificates generated on each node.

BASH
openssl x509 -in /etc/etcd/server.crt -text -noout | grep -E "CN=|DNS:|IP Address:"

This command displays the certificate details to make sure everything is configured correctly.

3.5 Etcd Configuration

Configuring Etcd files on each node


On each of the nodes that are part of the cluster, you must configure the file /etc/default/etcd with the following parameters:

for the node srvlropsql01:

CODE
ETCD_NAME="srvlropsql01"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="https://192.168.1.214:2380"
ETCD_LISTEN_CLIENT_URLS="https://192.168.1.214:2379"
ETCD_INITIAL_CLUSTER="srvlropsql01=https://srvlropsql01:2380,srvlropsql02=https://srvlropsql02:2380,srvlropsql03=https://srvlropsql03:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.1.214:2380"
ETCD_ADVERTISE_CLIENT_URLS="https://192.168.1.214:2379"
ETCD_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_CERT_FILE="/etc/etcd/server.crt"
ETCD_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CLIENT_CERT_AUTH=true
ETCD_PEER_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_PEER_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CERT_FILE="/etc/etcd/server.crt"
ETCD_LOG_LEVEL="info"
ETCD_ENABLE_V2="true"

for the node srvlropsql02:

CODE
ETCD_NAME="srvlropsql02"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="https://192.168.1.215:2380"
ETCD_LISTEN_CLIENT_URLS="https://192.168.1.215:2379"
ETCD_INITIAL_CLUSTER="srvlropsql01=https://srvlropsql01:2380,srvlropsql02=https://srvlropsql02:2380,srvlropsql03=https://srvlropsql03:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.1.215:2380"
ETCD_ADVERTISE_CLIENT_URLS="https://192.168.1.215:2379"
ETCD_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_CERT_FILE="/etc/etcd/server.crt"
ETCD_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CLIENT_CERT_AUTH=true
ETCD_PEER_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_PEER_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CERT_FILE="/etc/etcd/server.crt"
ETCD_LOG_LEVEL="info"
ETCD_ENABLE_V2="true"

for the node srvlropsql03:

CODE
ETCD_NAME="srvlropsql03"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="https://192.168.1.216:2380"
ETCD_LISTEN_CLIENT_URLS="https://192.168.1.216:2379"
ETCD_INITIAL_CLUSTER="srvlropsql01=https://srvlropsql01:2380,srvlropsql02=https://srvlropsql02:2380,srvlropsql03=https://srvlropsql03:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.1.216:2380"
ETCD_ADVERTISE_CLIENT_URLS="https://192.168.1.216:2379"
ETCD_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_CERT_FILE="/etc/etcd/server.crt"
ETCD_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CLIENT_CERT_AUTH=true
ETCD_PEER_TRUSTED_CA_FILE="/etc/etcd/etcd-ca.crt"
ETCD_PEER_KEY_FILE="/etc/etcd/server.key"
ETCD_PEER_CERT_FILE="/etc/etcd/server.crt"
ETCD_LOG_LEVEL="info"
ETCD_ENABLE_V2="true"

Reboot and enable Etcd on each node

On each node, run the following commands to restart and enable the Etcd service to start automatically at boot:

BASH
sudo systemctl restart etcd
sudo systemctl enable etcd

Etcd Status Check

Once Etcd is configured and running, you can check the health of the nodes with the following command:

BASH
sudo etcdctl --endpoints=https://srvlropsql01:2379,https://srvlropsql02:2379,https://srvlropsql03:2379 --cacert=/etc/etcd/ca.pem --cert=/etc/etcd/server.crt --key=/etc/etcd/server.key endpoint health

Output

CODE
https://srvlropsql01:2379 is healthy: successfully committed proposal: took = 27.537946ms
https://srvlropsql03:2379 is healthy: successfully committed proposal: took = 38.517279ms
https://srvlropsql02:2379 is healthy: successfully committed proposal: took = 35.582262ms

Checking the status of Etcd endpoints

You can check the status of Etcd endpoints using the following command:

BASH
sudo etcdctl --endpoints=https://srvlropsql01:2379,https://srvlropsql02:2379,https://srvlropsql03:2379 --cacert=/etc/etcd/ca.pem --cert=/etc/etcd/server.crt --key=/etc/etcd/server.key endpoint status --write-out=table

Output

JAVA
+---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT          |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| https://srvlropsql01:2379 | 1d73939a04e3e9e0 |  3.4.23 |   20 kB |      true |      false |         6 |          9 |                  9 |        |
| https://srvlropsql02:2379 | bce2231f6867959d |  3.4.23 |   25 kB |     false |      false |         6 |          9 |                  9 |        |
| https://srvlropsql03:2379 | 2ce6d020bc93183f |  3.4.23 |   20 kB |     false |      false |         6 |          9 |                  9 |        |
+---------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

Review of Etcd logs

To check for errors in the Etcd service, you can check the logs by running:

BASH
sudo journalctl -u etcd --no-pager | tail -n 20

If you have problems with the certificate, change the configuration to the following and perform tests

CODE
ETCD_PEER_CLIENT_CERT_AUTH=false
CLIENT-CERT-AUTH=false
ETCD_AUTO_TLS=false

Patroni Settings

Stop the PostgreSQL service

Before starting Patroni configuration, you need to stop the PostgreSQL service to avoid conflicts.

BASH
sudo systemctl stop postgresql.service

PostgreSQL user access

The user is changed postgres to execute commands within PostgreSQL.

BASH
sudo su - postgres
psql

Configuring roles and users in PostgreSQL

Roles required for replication and backup are created and configured with pgBackRest.

Creating the replicator user

A role with replication permissions is created.

SQL
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'Temporal01.';
ALTER ROLE replicator WITH REPLICATION;

Administrator user settings

The user's password is changed postgres to ensure secure authentication.

SQL
ALTER USER postgres WITH PASSWORD 'Temporal01.';

User creation for pgBackRest

A user is created with superuser permissions and granted connection and access privileges to streams and tables. 

SQL
CREATE ROLE pgbackrest WITH LOGIN PASSWORD 'Temporal01.' SUPERUSER;
GRANT CONNECT ON DATABASE postgres TO pgbackrest;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pgbackrest;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgbackrest;
GRANT CONNECT ON DATABASE postgres TO pgbackrest;

Creating and setting permissions

BASH
touch /var/lib/postgresql/.pgpass
chmod 600 /var/lib/postgresql/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass

File editing .pgpass

Credentials for replication are added to each node in the cluster.

CODE
vi  /var/lib/postgresql/.pgpass
CODE
Contenido del archivo:
CODE
192.168.1.214:5432:*:replicator:Temporal01.
192.168.1.215:5432:*:replicator:Temporal01.
192.168.1.216:5432:*:replicator:Temporal01.

192.168.1.214:5432:*:postgres:Temporal01.
192.168.1.215:5432:*:postgres:Temporal01.
192.168.1.216:5432:*:postgres:Temporal01.

 Setting environment variable PGPASSFILE

You need to export the environment variable that points to the file .pgpass so that PostgreSQL uses it automatically.

CODE
vi ~/.bashrc

Add the following line:

BASH
export PGPASSFILE=/var/lib/postgresql/.pgpass

Apply the changes:

CODE
source ~/.bashrc

Delete previous data and restart the service

Before starting Patroni, it is advisable to delete previous PostgreSQL data and stop the service again.

BASH
 sudo systemctl stop postgresql.service
sudo rm -rf /var/lib/postgresql/15/main

 Configuring Patroni files on each node

Each node has its own configuration file config.yml, which specifies configuration details, such as the address of the restapi, the configuration of etcd for cluster coordination, and PostgreSQL parameters. This is an example configuration for three nodes in a Patroni cluster:

 for the node srvlropsql01:

CODE

  scope: keycloak-cluster
namespace: /service/
name: srvlropsql01

restapi:
  listen: 192.168.1.214:8008
  connect_address: 192.168.1.214:8008
  authentication:
    username: admin
    password: admin_password

etcd:
  hosts: 192.168.1.214:2379,192.168.1.215:2379,192.168.1.216:2379
  protocol: https
  cacert: /etc/etcd/etcd-ca.crt
  cert: /etc/etcd/server.crt
  key: /etc/etcd/server.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
  initdb:
    - encoding: UTF8
    - data-checksums
  users:
    replication:
      password: replicator_password
      options:
        - replication
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
  post_init: 
    - createuser --superuser admin

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.214:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    archive_mode: "on"
    archive-command: 'pgbackrest --stanza=postgres archive-push %f'
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    wal_keep_size: 256MB
  pg_hba:
    - local all postgres trust
    - hostssl replication replicator 127.0.0.1/32 trust
    - hostssl replication replicator 192.168.1.215/32 trust
    - hostssl replication replicator 192.168.1.216/32 trust
    - hostssl replication replicator 192.168.1.214/32 trust
    - host replication replicator 127.0.0.1/32 trust
    - host replication replicator 192.168.1.215/32 trust
    - host replication replicator 192.168.1.216/32 trust
    - host replication replicator 192.168.1.214/32 trust
    - hostssl all all 192.168.1.0/24 trust
    - host all all 192.168.1.0/24 trust
    - hostssl all pgbackrest 192.168.1.0/24 trust
    - host all pgbackrest 192.168.1.0/24 trust
    - hostssl all all 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

 for the node srvlropsql02:

CODE


scope: keycloak-cluster
namespace: /service/
name: srvlropsql02

restapi:
  listen: 192.168.1.215:8008
  connect_address: 192.168.1.215:8008
  authentication:
    username: admin
    password: admin_password

etcd:
  hosts: 192.168.1.214:2379,192.168.1.215:2379,192.168.1.216:2379
  protocol: https
  cacert: /etc/etcd/etcd-ca.crt
  cert: /etc/etcd/server.crt
  key: /etc/etcd/server.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
  initdb:
    - encoding: UTF8
    - data-checksums
  users:
    replication:
      password: replicator_password
      options:
        - replication
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
  post_init: 
    - createuser --superuser admin

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.215:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    archive_mode: "on"
    archive-command: 'pgbackrest --stanza=postgres archive-push %f'
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    wal_keep_size: 256MB
  pg_hba:
    - local all postgres trust
    - hostssl replication replicator 127.0.0.1/32 trust
    - hostssl replication replicator 192.168.1.215/32 trust
    - hostssl replication replicator 192.168.1.216/32 trust
    - hostssl replication replicator 192.168.1.214/32 trust
    - host replication replicator 127.0.0.1/32 trust
    - host replication replicator 192.168.1.215/32 trust
    - host replication replicator 192.168.1.216/32 trust
    - host replication replicator 192.168.1.214/32 trust
    - hostssl all all 192.168.1.0/24 trust
    - host all all 192.168.1.0/24 trust
    - hostssl all pgbackrest 192.168.1.0/24 trust
    - host all pgbackrest 192.168.1.0/24 trust
    - hostssl all all 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

 for the node srvlropsql03:

CODE
scope: keycloak-cluster
namespace: /service/
name: srvlropsql03

restapi:
  listen: 192.168.1.216:8008
  connect_address: 192.168.1.216:8008
  authentication:
    username: admin
    password: admin_password

etcd:
  hosts: 192.168.1.214:2379,192.168.1.215:2379,192.168.1.216:2379
  protocol: https
  cacert: /etc/etcd/etcd-ca.crt
  cert: /etc/etcd/server.crt
  key: /etc/etcd/server.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
  initdb:
    - encoding: UTF8
    - data-checksums
  users:
    replication:
      password: replicator_password
      options:
        - replication
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
  post_init: 
    - createuser --superuser admin

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.216:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    archive_mode: "on"
    archive-command: 'pgbackrest --stanza=postgres archive-push %f'
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    wal_keep_size: 256MB
  pg_hba:
    - local all postgres trust
    - hostssl replication replicator 127.0.0.1/32 trust
    - hostssl replication replicator 192.168.1.215/32 trust
    - hostssl replication replicator 192.168.1.216/32 trust
    - hostssl replication replicator 192.168.1.214/32 trust
    - host replication replicator 127.0.0.1/32 trust
    - host replication replicator 192.168.1.215/32 trust
    - host replication replicator 192.168.1.216/32 trust
    - host replication replicator 192.168.1.214/32 trust
    - hostssl all all 192.168.1.0/24 trust
    - host all all 192.168.1.0/24 trust
    - hostssl all pgbackrest 192.168.1.0/24 trust
    - host all pgbackrest 192.168.1.0/24 trust
    - hostssl all all 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

pgBackRest Configuration

After configuring Patroni, you need to configure pgBackRest, a tool used to manage PostgreSQL backups. This is the configuration file pgbackrest.conf which should be used:

CODE
[global]
repo1-path=/var/lib/pgbackrest  # Directorio donde se guardarán los respaldos
log-level-console=info
log-level-file=detail
compress=y
start-fast=y
log-path=/var/log/pgbackrest  # Ruta para logs detallados

# Nodo 1 (Líder)
[nodo1]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
pg1-host=localhost  # El nodo líder se encuentra en el localhost
stanza=postgres
archive-mode=on
archive-command='pgbackrest --stanza=postgres archive-push %p'

# Nodo 2 (Réplika)
[nodo2]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
pg1-host=192.168.1.215  # Dirección IP del nodo 2
stanza=postgres
archive-mode=on
archive-command='pgbackrest --stanza=postgres archive-push %p'

# Nodo 3 (Réplika)
[nodo3]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
pg1-host=192.168.1.216  # Dirección IP del nodo 3
stanza=postgres
archive-mode=on
archive-command='pgbackrest --stanza=postgres archive-push %p'
  • The directory where the backups will be stored is configured (repo1-path).
  • Fast backups are enabled (start-fast=y) and compression (compress=y).
  • Each node has its own configuration pg1-path and pg1-host, and the parameters of archive-command are set to perform backups with pgBackRest.

Starting the Services

Once the Patroni and pgBackRest configuration is ready, the corresponding services must be started:

BASH
sudo systemctl start patroni.service
sudo systemctl enable patroni.service

This will start the Patroni service on all three nodes and enable it to start automatically on every reboot.

Checking Patron Status

You can check Patroni status using a tool like curl to make an HTTP request to the Patroni REST API on the leader node:

 for the node srvlropsql01:

BASH
curl http://192.168.1.214:8008/patroni  | jq .

Output

JSON
{
  "state": "running",
  "postmaster_start_time": "2025-02-27 10:38:12.906725+00:00",
  "role": "master",
  "server_version": 150010,
  "xlog": {
    "location": 285213000
  },
  "timeline": 14,
  "replication": [
    {
      "usename": "replicator",
      "application_name": "srvlropsql02",
      "client_addr": "192.168.1.215",
      "state": "streaming",
      "sync_state": "async",
      "sync_priority": 0
    },
    {
      "usename": "replicator",
      "application_name": "srvlropsql03",
      "client_addr": "192.168.1.216",
      "state": "streaming",
      "sync_state": "async",
      "sync_priority": 0
    }
  ],
  "dcs_last_seen": 1740653075,
  "database_system_identifier": "7476020745627849320",
  "patroni": {
    "version": "3.0.2",
    "scope": "keycloak-cluster"
  }
}

 for the node srvlropsql01:

BASH
curl http://192.168.1.215:8008/patroni | jq .

Output

JSON
{
  "state": "running",
  "postmaster_start_time": "2025-02-27 10:38:20.602283+00:00",
  "role": "replica",
  "server_version": 150010,
  "xlog": {
    "received_location": 285213000,
    "replayed_location": 285213000,
    "replayed_timestamp": null,
    "paused": false
  },
  "timeline": 14,
  "dcs_last_seen": 1740653115,
  "database_system_identifier": "7476020745627849320",
  "patroni": {
    "version": "3.0.2",
    "scope": "keycloak-cluster"
  }
}

 for the node srvlropsql03:

BASH

curl http://192.168.1.216:8008/patroni | jq .

Output

JSON
{
  "state": "running",
  "postmaster_start_time": "2025-02-27 10:38:36.525932+00:00",
  "role": "replica",
  "server_version": 150010,
  "xlog": {
    "received_location": 285213000,
    "replayed_location": 285213000,
    "replayed_timestamp": null,
    "paused": false
  },
  "timeline": 14,
  "dcs_last_seen": 1740653125,
  "database_system_identifier": "7476020745627849320",
  "patroni": {
    "version": "3.0.2",
    "scope": "keycloak-cluster"
  }
}

Checking recovery status in PostgreSQL

  • Change the user postgres:

BASH
sudo su - postgres

Check if the server is in recovery mode using the command pg_is_in_recovery:

SQL
psql -U postgres -c "SELECT pg_is_in_recovery();"

Output

CODE
 pg_is_in_recovery 
-------------------
 f
(1 row)
  • f: The server is in normal operating mode (not recovery).
  • t: The server is in recovery mode (probably on a replica or recovering from a failure).

Check Replication Status

  • Check the replication status on the server using the following command:
SQL
psql -U postgres -c "SELECT * FROM pg_stat_replication;"

Output

CODE
pid  | usesysid |  usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time           
------+----------+------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
1602 |    16384 | replicator | srvlropsql02     | 192.168.1.215 |                 |       38202 | 2025-02-27 10:38:20.756912+00 |              | streaming | 0/11000060 | 0/11000060 | 0/11000060 | 0/11000060 |           |           |            |             0 | async      | 2025-02-27 10:41:53.23572+00
1609 |    16384 | replicator | srvlropsql03     | 192.168.1.216 |                 |       53922 | 2025-02-27 10:38:46.672521+00 |              | streaming | 0/11000060 | 0/11000060 | 0/11000060 | 0/11000060 |           |           |            |             0 | async      | 2025-02-27 10:41:56.916286+00
(2 rows)

This shows details of the replication processes, including application names (srvlropsql02, srvlropsql03), client IP addresses, replication states (streaming), and the positions of the replica logs.

Check replication slots

Query the replication slots with the following command:

SQL
psql -U postgres -c "SELECT * FROM pg_replication_slots;"

Output

CODE
slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase 
--------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
srvlropsql02 |        | physical  |        |          | f         | t      |       1602 |      |              | 0/11000060  |                     | reserved   |               | f
srvlropsql03 |        | physical  |        |          | f         | t      |       1609 |      |              | 0/11000060  |                     | reserved   |               | f
(2 rows)

Replication slots display the slot name (srvlropsql02, srvlropsql03), the type of slot (physical), and if they are active. The slots are reserved, indicating that replication is underway.

View Patroni's logs

  • To review Patroni service logs, you can use journalctl:
BASH
sudo journalctl -u patroni --no-pager | tail -n 20

Recommendations and Best Practices

  1. Monitoring: Implement tools like Prometheus and Grafana to monitor cluster health and configure failure alerts.

  2. Backups: Make regular backups with tools like pgBackRest and be sure to store them safely.

  3. Scalability: Add reading nodes and adjust settings to scale out based on load.

  4. Security: Use TLS to encrypt communication between nodes and clients, and enforces strict access policies in PostgreSQL.

  5. etcd: Hold etcd replicated and monitored to ensure consistency between nodes.

  6. Failover: Perform periodic failover tests to ensure availability in case of failures.

  7. Maintenance: Regularly updates system components, verifying compatibility between versions.

  8. Redundancy: Ensures redundant networks and hardware to avoid interruptions.

  9. Optimization: Adjusts the parameters of PostgreSQL depending on the load and available resources.

Conclusion

Implement a system of PostgreSQL with Patroni, etcd and TLS offers a robust solution for high availability and scalability. Patroni provides automated failover and node promotion management, ensuring continuous database availability. etcd ensures efficient coordination between nodes, allowing consensus and distributed configuration to be maintained. Besides, TLS secures communications between nodes, protecting data in transit with high-level encryption. This architecture ensures a highly available, secure and easily scalable database, ideal for critical production environments.

:wq!

 

 

 

Comments