Exploration of PostgreSQL Cluster Management: Promoting Patroni Replica Node

Abstract:

This article includes the steps of removing one of the replica nodes in the Patroni cluster from the cluster and turning it into an independent writeable node.

1. Introduction

PostgreSQL is a powerful open-source relational database management system known for its extensibility and robust features. Managing a PostgreSQL cluster involves various tasks, including replication, promotion of replicas, and upgrading extensions.

2. Replica node on Patroni

Replication is a method that increases high availability and reduces fault tolerance in database systems. In this section, we will examine the steps to convert the replica node in an existing Patroni cluster into an independent node.

2.1 Stop Patroni Service [Just run it on the replica node you want to promote]

root
  systemctl stop patroni.service

2.2 Updating and Installing Necessary Packages

root
  apt update
  apt install postgresql-15 pgbadger postgresql-common postgresql-15-repack postgresql-15-postgis-3 postgresql-client-15

3. Promoting a Replica

Promotion is the process of promoting a replica to the primary database server role. This step ensures continuity of operations in the event of primary node failure. Under normal circumstances, Patroni does this job automatically for you, but the subject of this article involves manually promoting a replica node in the Patroni cluster for a special reason.

3.1. Starting PostgreSQL

postgres
  pg_ctl -D /pg_data/15/main start

3.2. Altering System Configuration and Reloading Configuration

psql
  alter system set archive_command TO '/bin/true';
  select pg_reload_conf();

3.3. Initiating the Promotion Process

psql
  select pg_promote();

3.4. Monitoring Log Files for Confirmation

tail -f $PGDATA/log/postgresql-...