Configuring a multi-master setup for PostgreSQL using Patroni can significantly enhance the high availability and fault tolerance of your database. This article will guide you through the steps required to implement such a setup. The process involves various components including etcd, Patroni, HAProxy, and PostgreSQL itself.
Setting up a multi-master PostgreSQL cluster is an advanced configuration that allows multiple nodes to handle read and write operations, ensuring continuous availability and high performance. Patroni is a template for PostgreSQL HA (high availability) which leverages etcd for key-value storage, and HAProxy for load balancing. Here, we will take you through the essential steps to install and configure these components.
Understanding the Components
Patroni is a high-availability solution for PostgreSQL that can manage a cluster of PostgreSQL nodes efficiently. etcd is a distributed key-value store used for shared configuration and service discovery. HAProxy offers load balancing and high availability for server applications. When combined, these tools provide a robust multi-master setup for your PostgreSQL database.
Installing and Configuring etcd
The first step in setting up your multi-master PostgreSQL cluster is to install and configure etcd. etcd will be responsible for storing configuration data and coordinating cluster management.
Installation
To begin, you need to install etcd on each server node. Use the following command:
sudo apt install etcd
After installation, configure etcd on each node. Open the configuration file /etc/etcd/etcd.conf and edit the following settings:
ETCD_LISTEN_PEER_URLS="http://<node1-ip>:2380,http://<node2-ip>:2380"
ETCD_LISTEN_CLIENT_URLS="http://<node1-ip>:2379,http://<node2-ip>:2379"
ETCD_INITIAL_CLUSTER="node1=http://<node1-ip>:2380,node2=http://<node2-ip>:2380"
ETCD_NAME="node1"
Replace <node1-ip> and <node2-ip> with the respective IP addresses of your nodes. Start the etcd service using:
sudo systemctl start etcd
sudo systemctl enable etcd
Verification
To verify that etcd is running correctly, you can use the etcdctl command-line utility:
etcdctl member list
You should see a list of members indicating that the etcd cluster is functional. By configuring etcd, you ensure that your cluster nodes can communicate and coordinate effectively.
Setting Up Patroni
With etcd configured, you can now set up Patroni on each node. Patroni will manage your PostgreSQL instances and ensure high availability.
Installation
Install Patroni using pip:
sudo apt install python3-pip
pip3 install patroni
Configuration
Next, create a configuration file for Patroni on each node. You can name it patroni.yml and place it in a suitable directory like /etc/patroni/. Below is an example configuration:
scope: postgres
namespace: /service/
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: <node1-ip>:8008
etcd:
host: <etcd-cluster-ip>:2379
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"
wal_log_hints: "on"
max_wal_senders: 10
max_replication_slots: 10
postgresql:
listen: 0.0.0.0:5432
connect_address: <node1-ip>:5432
data_dir: /var/lib/postgresql/12/main
config_dir: /etc/postgresql/12/main
bin_dir: /usr/lib/postgresql/12/bin
authentication:
replication:
username: replicator
password: replicator_password
superuser:
username: postgres
password: postgres_password
parameters:
max_connections: 100
shared_buffers: 128MB
wal_level: replica
watchdog:
mode: required
device: /dev/watchdog
safety_margin: 5
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
Ensure to replace <node1-ip>, <etcd-cluster-ip>, and other placeholders with actual values. This configuration enables Patroni to manage PostgreSQL and facilitate replication.
Starting Patroni
Start the Patroni service using the following command:
patroni /etc/patroni/patroni.yml
Ensure that Patroni is running correctly by checking its logs and verifying the cluster state.
Configuring PostgreSQL for Replication
With Patroni running, you need to configure PostgreSQL for replication. This involves setting up replication users and configuring the database settings.
Replication User
Create a replication user in PostgreSQL:
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator_password';
Ensure this user has the necessary permissions for replication.
PostgreSQL Configuration
Edit the PostgreSQL configuration file (usually located at /etc/postgresql/12/main/postgresql.conf) to include the following settings:
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
listen_addresses = '*'
Additionally, configure the pg_hba.conf file to allow replication connections:
host replication replicator <subnet> md5
Replace <subnet> with the appropriate subnet for your network.
Restart PostgreSQL
Restart the PostgreSQL service to apply changes:
sudo systemctl restart postgresql
These configurations ensure that PostgreSQL is ready for replication and can work seamlessly with Patroni.
Setting Up HAProxy for Load Balancing
To distribute connection requests and provide high availability, you need to set up HAProxy. HAProxy will act as a load balancer for your PostgreSQL nodes.
Installation
Install HAProxy using the following command:
sudo apt install haproxy
Configuration
Edit the HAProxy configuration file located at /etc/haproxy/haproxy.cfg to include the following settings:
global
log /dev/log local0
log /dev/log local1 notice
maxconn 4096
user haproxy
group haproxy
daemon
defaults
log global
mode tcp
option tcplog
option dontlognull
retries 3
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
frontend postgresql
bind *:5432
default_backend postgresql_backend
backend postgresql_backend
mode tcp
balance roundrobin
option httpchk
server node1 <node1-ip>:5432 check
server node2 <node2-ip>:5432 check
Replace <node1-ip> and <node2-ip> with the IP addresses of your PostgreSQL nodes. This configuration balances the load between the nodes and performs health checks.
Starting HAProxy
Start and enable the HAProxy service:
sudo systemctl start haproxy
sudo systemctl enable haproxy
Verify that HAProxy is running and correctly balancing connections.
Configuring a multi-master setup for PostgreSQL using Patroni ensures that your database is highly available and fault-tolerant. By setting up etcd, Patroni, and HAProxy, you create a robust environment for your PostgreSQL cluster. This setup allows multiple nodes to handle both read and write operations, enhancing performance and reliability.
In summary, you have learned to install and configure etcd for coordination, Patroni for high availability, PostgreSQL for replication, and HAProxy for load balancing. This combination provides a powerful solution for managing a multi-master PostgreSQL cluster, ensuring your data is always available and secure.
