How do you configure a multi-master setup for PostgreSQL using Patroni?

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://:2380,http://:2380" ETCD_LISTEN_CLIENT_URLS="http://:2379,http://:2379" ETCD_INITIAL_CLUSTER="node1=http://:2380,node2=http://:2380" ETCD_NAME="node1" Replace and 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: :8008 etcd: host: :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: :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 , , 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 md5 Replace 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 :5432 check server node2 :5432 check Replace and 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.