From Standalone to High Availability: Convert Your PostgreSQL Database to a Patroni Cluster

Apr 23, 2024

Mins to Read

All

In Database Management, achieving High Availability(HA) is very important, particularly for systems handling critical data and applications. In a Production Environment, any downtime can translate into substantial losses - be it operational, financial or reputational. By ensuring high availability for databases, businesses can avoid the risks associated with system/network failures, hardware malfunctions, or planned/scheduled maintenance activities.

Patroni: Open-Source Solution for HA with PostgreSQL

Popular open source RDBMS provides HA solutions with automated failovers in their own ways. Example, MySQL with InnoDB Cluster, PostgreSQL with Patroni(An open source external framework solution). During the initial implementation, high availability may be overlooked, leading to the configuration of the database as a standalone system.

However, as the system grows and the need for reliability increases, transitioning from a standalone setup to a HA cluster becomes a critical and necessary process. It's important to note that this conversion process is not always straightforward and may require careful planning and execution.

In this blog post, we will explore the process of converting a standalone PostgreSQL instance into a highly available Patroni Cluster, with a key focus on achieving this transition with minimal downtime and keeping it simple as much as possible.

Prerequisites for Converting to a Patroni Cluster

To proceed with this conversion, ensure that you do have following prerequisites

Running PostgreSQL Instance: You must have a PostgreSQL instance already up and running on your system.

Temporary sudo and Internet Access: Ensure that you have temporary sudo access and internet connectivity for downloading and installing any required packages.

Superuser Access to PostgreSQL: You'll need superuser access to PostgreSQL in order to create any necessary users required by Patroni

Location of Data and Bin Directories: Know the locations of the data directory (where PostgreSQL stores its data files) and the bin directory (where PostgreSQL executable files are located) on your system. This information will be necessary for configuring Patroni.

Step-by-Step Guide: Converting Standalone PostgreSQL to Patroni (with Demo)

To demonstrate the conversion process, we have set up a test server with the IP Address 192.168.33.11 (Node1) running Ubuntu 20.04. PostgreSQL has already been installed, configured, and is currently up and running on this server.

 
root@primary:/var/lib/postgresql/16/main# sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/main/ -o  "-c config_file=/etc/postgresql/16/main/postgresql.conf" status

pg_ctl: server is running (PID: 18521)

/usr/lib/postgresql/16/bin/postgres "-D" "/var/lib/postgresql/16/main" "-c" "config_file=/etc/postgresql/16/main/postgresql.conf"

root@primary:~# ps -ef | grep post

root       18484   17459  0 15:20 pts/1    00:00:00 su postgres

postgres   18485   18484  0 15:20 pts/1    00:00:00 bash

postgres   18521       1  0 15:21 ?        00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf

postgres   18522   18521  0 15:21 ?        00:00:00 postgres: postgres_cluster1: checkpointer

postgres   18523   18521  0 15:21 ?        00:00:00 postgres: postgres_cluster1: background writer

postgres   18524   18521  0 15:21 ?        00:00:00 postgres: postgres_cluster1: startup recovering 000000010000000000000001

root       18527    1414  0 15:21 pts/0    00:00:00 grep --color=auto post

Setting Up the Cluster (etcd Installation & Initialization)

Let's begin the installation of the necessary packages for Patroni, starting with etcd on Node1. Since these operations won't affect the currently running PostgreSQL instance, we can proceed at any convenient time without causing any disruptions.

Etcd Installation & Initialization:

Installation of Etcd is straightforward. It can be installed by using the following

 
apt-get install etcd

Once the installation completes, configure the etcd on the file /etc/default/etcd.

 
ETCD_NAME=prod_node1

ETCD_INITIAL_CLUSTER="prod_node1=http://192.168.33.11:2380"

ETCD_INITIAL_CLUSTER_TOKEN="testing"

ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.33.11:2380"

ETCD_LISTEN_PEER_URLS="http://192.168.33.11:2380"

ETCD_LISTEN_CLIENT_URLS="http://192.168.33.11:2379,http://127.0.0.1:2379"

ETCD_ADVERTISE_CLIENT_URLS="http://192.168.33.11:2379"

ETCD_DATA_DIR="/var/lib/etcd/postgresql"

ETCD_INITIAL_CLUSTER_STATE="new”

The above configuration can be used to setup and initialize the etcd cluster just by replacing the IP mentioned with your IP address and the node name.

Once the configuration is done, restart the etcd using the following command and check the etcd cluster status as well.

 
systemctl restart etcd

 
root@primary:~# etcdctl member list

a7cd7bdd11cbea30: name=prod_node1 peerURLs=http://192.168.33.11:2380 clientURLs=http://192.168.33.11:2379 isLeader=true

root@primary:~#

isLeader=true shows the proper configuration of etcd cluster on node 1 and we are good to proceed with the patroni setup.

Patroni Configuration

Lets install patroni on node 1

 
apt-get install patroni

 
root@primary:~# patroni --version

patroni 3.2.2

root@primary:~#

For the configuration, use the below content in your /etc/patroni/patroni.yml file.

 
scope: postgres_cluster

name: prod_node1

restapi:

  listen: 0.0.0.0:8008

  connect_address: 192.168.33.11:8008

etcd:

  host: 192.168.33.11: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:

        wal_level: logical

        max_wal_senders: 10

        max_replication_slots: 10

postgresql:

  listen: 0.0.0.0:5432

  connect_address: 192.168.33.11:5432

  data_dir: "/var/lib/postgresql/16/main/"

  bin_dir: "/usr/lib/postgresql/16/bin/"

  pgpass: /tmp/pgpass

  authentication:

    replication:

      username: mydb_repl

      password: B5bHWt@I&~_%HGS

    superuser:

      username: mydb_super

      password: ?n&ua{Yyn?5d2IP

  parameters:

    unix_socket_directories: '/var/run/postgresql'

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

Synchronizing PostgreSQL with Patroni

Before starting the patroni service, create the users defined under authentication section

 
postgres=# CREATE USER mydb_repl WITH REPLICATION ENCRYPTED PASSWORD 'B5bHWt@I&~_%HGS';

CREATE ROLE

postgres=# CREATE USER mydb_super WITH SUPERUSER ENCRYPTED PASSWORD '?n&ua{Yyn?5d2IP';

CREATE ROLE

Also, disable the postgres systemd unit as Patroni will manage the starting and stopping of the postgres process further.

 
root@primary:~# systemctl disable postgresql

Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install.

Executing: /lib/systemd/systemd-sysv-install disable postgresql

Removed /etc/systemd/system/multi-user.target.wants/postgresql.service.

root@primary:~#

Start the patroni service

 
systemd start patroni

 
2024-04-07 15:22:14,870 INFO: No PostgreSQL configuration items changed, nothing to reload.

localhost:5432 - accepting connections

2024-04-07 15:22:14,901 INFO: establishing a new patroni heartbeat connection to postgres

2024-04-07 15:22:15,032 INFO: promoted self to leader by acquiring session lock

server promoting

2024-04-07 15:22:16,132 INFO: no action. I am (prod_node1), the leader with the lock

2024-04-07 15:22:26,099 INFO: no action. I am (prod_node1), the leader with the lock

2024-04-07 15:22:36,099 INFO: no action. I am (prod_node1), the leader with the lock

Patroni has started and self promoted itself as a leader as it is the only node available now.

As a final step, it's crucial to restart PostgreSQL using Patroni to ensure that all subsequent startup procedures of PostgreSQL are managed by Patroni. This step completes the integration of PostgreSQL into the Patroni cluster, allowing Patroni to oversee the management and operation of PostgreSQL for high availability. This operation needs to be performed during maintenance as it needs a quick restart.

 
root@primary:~# /usr/bin/patronictl -c /etc/patroni/patroni.yml restart postgres_cluster

+ Cluster: postgres_cluster (7355037865848155544) --+-----------+

| Member     | Host          | Role   | State   | TL | Lag in MB |

+------------+---------------+--------+---------+----+-----------+

| prod_node1 | 192.168.33.11 | Leader | running |  2 |           |

+------------+---------------+--------+---------+----+-----------+

When should the restart take place (e.g. 2024-04-07T16:39)  [now]: now

Are you sure you want to restart members prod_node1? [y/N]: y

Restart if the PostgreSQL version is less than provided (e.g. 9.5.2).

 
root@primary:~# ps -ef | grep postg

root       18484   17459  0 15:20 pts/1    00:00:00 su postgres

postgres   18485   18484  0 15:20 pts/1    00:00:00 bash

postgres   18530   18485  0 15:22 pts/1    00:00:02 /usr/bin/python3 /usr/bin/patroni /etc/patroni/patroni.yml

postgres   18718       1  0 15:39 ?        00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main/ --config-file=/etc/postgresql/16/main/postgresql.conf --listen_addresses=0.0.0.0 --port=5432 --cluster_name=postgres_cluster1 --wal_level=logical --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on

postgres   18722   18718  0 15:39 ?        00:00:00 postgres: postgres_cluster1: checkpointer

postgres   18723   18718  0 15:39 ?        00:00:00 postgres: postgres_cluster1: background writer

postgres   18725   18718  0 15:39 ?        00:00:00 postgres: postgres_cluster1: walwriter

postgres   18726   18718  0 15:39 ?        00:00:00 postgres: postgres_cluster1: autovacuum launcher

postgres   18727   18718  0 15:39 ?        00:00:00 postgres: postgres_cluster1: logical replication launcher

postgres   18732   18718  0 15:40 ?        00:00:00 postgres: postgres_cluster1: mydb_super postgres 127.0.0.1(59984) idle

root       18738    1414  0 15:40 pts/0    00:00:00 grep --color=auto postg

We have successfully made our transition from a standalone to a highly available patroni cluster. Further replicas can be added by performing installation and configuration of etcd & patroni on the new nodes. Data Stream will be carried over by patroni to build the replicas.

Also read: Revolutionizing PostgreSQL Schema Changes with pg_osc

Benefits of Using Patroni for High Availability

Automatic failover, simplified management, and flexible configuration make Patroni a game-changer.

Patroni takes over when your primary server fails, promoting a standby to minimize downtime.
Manage your cluster with ease - Patroni automates complex tasks, freeing up your time.
Scale effortlessly - add standby replicas for increased fault tolerance and read performance.

Mydbops helps you leverage Patroni for a robust PostgreSQL setup.

Contact Mydbops today for a free consultation! Leverage Patroni for a highly available and well-managed PostgreSQL environment.