[PG] PostgreSQL high availability solution repmgr deployment (very detailed)

Table of Contents

Introduction

1 Overview

1.1 Terminology

1.2 Components

1.2.1 repmgr

1.2.2 repmgrd

1.3 Repmgr users and metadata

2 Installation and deployment

2.0 Deployment environment

2.1 Installation requirements

2.1.1 Operating system

2.1.2 PostgreSQL version

2.1.3 Operating system users

2.1.4 Installation location

2.1.5 Version requirements

2.2 Installation

2.2.1 Software package installation

2.2.2 Source code compilation and installation

3 Quick start

3.2 PostgreSQL deployment main library

3.3 Create users and databases

3.4 Configure authentication file pg_hba.conf

3.5 Set up password-free login

3.6 repmgr configuration file

3.7 Register the master node

3.8 Clone standby Server

3.9 Verify that master-slave synchronization is normal

3.10 Register slave node

refer to

repmgr official website

5.3.3 version documentation

Latest documentation (version 5.4)

github repmgr

Introduction

repmgr is a set of open source tools for managing replication and failover within a PostgreSQL server cluster. It enhances PostgreSQL’s built-in replication capabilities with utilities for setting up standby servers, monitoring replication, and performing administrative tasks such as failover or switchover operations.

1 Overview

1.1 Terminology

The following terms are used throughout the repmgr documentation

  • replication cluster: In the repmgr documentation, “replication cluster” refers to a network of PostgreSQL servers connected through streaming replication.
  • node : A node is a single PostgreSQL server in a replication cluster.
  • upstream node: The node to which the standby server is connected to receive streaming replication. This can be the primary server, or in the case of cascaded replication, another standby server.
  • failover: This is the operation that occurs when the primary server fails and a suitable standby server is promoted as the new primary server. The repmgrd daemon supports automatic failover to minimize downtime.
  • switchover: In certain circumstances, such as hardware or operating system maintenance, it is necessary to take the primary server offline; in this case, a controlled switchover is required whereby a suitable standby database is promoted and removed from the replication cluster in a controlled manner Delete the existing master database. The repmgr command line client provides this functionality.
  • fencing: In a failover situation, after upgrading the new standby database, the previous primary database does not accidentally come back online, which would lead to a split-brain situation. To prevent this from happening, the failed primary database should be isolated, or “isolated,” from the application.
  • witness server: epmgr provides the ability to set up a so-called “witness server” to help determine the new primary server in a failover situation with multiple standby servers. The witness server itself is not part of the replication cluster, although it contains a replica of the repmgr metadata schema.

1.2 Component

repmgr is a set of open source tools for managing replication and failover within a cluster of PostgreSQL servers. It supports and enhances PostgreSQL’s built-in streaming replication, which provides a single read/write master and one or more read-only standbys that contain near-real-time replicas of the master’s database. It provides two main tools:

1.2.1 repmgr

Command line tools for performing administrative tasks such as:

  • Set up a backup server
  • Promote the standby server to the primary server
  • Active and standby server switching
  • Display the status of servers in a replication cluster

1.2.2 repmgrd

A daemon that actively monitors servers in a replication cluster and performs the following tasks:

  • Monitor and log replication performance
  • Perform failover by detecting the failure of the primary server and upgrading the most suitable backup server
  • Provide notifications about events in the cluster to user-defined scripts that can perform tasks such as sending alerts via email

1.3 Repmgr User and Metadata

In order to effectively manage a replication cluster, repmgr needs to store information about the servers in the cluster in a dedicated database schema. This schema is automatically created by the repmgr extension, which is installed during the first step of initializing a repmgr-managed cluster ( repmgr primary register) and contains the following objects:

table

  • repmgr.events: Record events of interest
  • repmgr.nodes: Replicates connection and status information for each server in the cluster
  • repmgr.monitoring_history: historical backup monitoring information written by repmgrd

view

  • repmgr.show_nodes: Based on table repmgr.nodes, also displays the name of the server’s upstream node
  • repmgr.replication_status: When monitoring of repmgrd is enabled, displays the current monitoring status of each standby database.

repmgr metadata schema can be stored in an existing database or its own dedicated database. Note that repmgr metadata schemas cannot be saved on database servers that are not part of a replication cluster managed by repmgr.

The database user must have permissions for repmgr to access the database and modify it. This user does not need to be superuser, but certain operations (such as the initial installation of the repmgr extension) will require a superuser connection (which can be specified using the command line option --superuser when required).

2 Installation and Deployment

2.0 Deployment Environment

PostgreSQL:13.12

repmgr 5.3.3

CentOS Linux release 7.2.1511 (Core)

repmgr can be installed from a binary package or source code provided by the operating system’s packaging system. In general, we recommend using binary packages unless they are not available for your operating system.

2.1 Installation Requirements

2.1.1 Operating System

repmgr was developed and tested on Linux and OS X, but should work on any UNIX-like system supported by PostgreSQL natively. Microsoft Windows is not supported.

2.1.2 PostgreSQL version

All servers in a replication cluster must run the same major version of PostgreSQL, and we recommend that they also run the same minor version.

2.1.3 Operating System User

A dedicated system user for repmgr is not required; since many repmgr and repmgrd operations require direct access to the PostgreSQL data directory, these commands should be executed by the postgres user .

2.1.4 Installation location

repmgr must be installed on every server in the replication cluster. If you install repmgr from a package, the package version must match the PostgreSQL version. If installing from source, repmgr must be compiled against the same major version.

2.1.5 version requirements

The following table outlines the PostgreSQL versions supported by repmgr versions.

Version compatibility matrix
repmgr version

Supported?

Whether to maintain

Latest release

The latest version

Supported PostgreSQL versions

Supported PostgreSQL versions

Notes

Notice
repmgr 5.4 (dev) 5.4.1 (2023-07-04) 9.4, 9.5, 9.6, 10, 11, 12, 13, 15
repmgr 5.3 YES 5.4.1 (2023-07-04) 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15 PostgreSQL 15 supported from repmgr 5.3.3
repmgr 5.2 NO 5.2.1 (2020-12-07) 9.4, 9.5, 9.6, 10, 11, 12, 13
repmgr 5.1 NO 5.1.0 (2020-04-13) 9.3, 9.4, 9.5, 9.6, 10, 11 , 12
repmgr 5.0 NO 5.0 (2019-10-15) 9.3, 9.4, 9.5 , 9.6, 10, 11, 12
repmgr 4.x NO 4.4 (2019-06-27) 9.3, 9.4, 9.5, 9.6, 10, 11
repmgr 3.x NO 3.3.2 (2017-05-30) 9.3, 9.4, 9.5, 9.6
repmgr 2.x NO 2.0.3 (2015-04-16) 9.0, 9.1, 9.2, 9.3, 9.4

2.2 Installation

2.2.1 Software Package Installation

The meaning of this command is to download a file and then execute it. This requires an external network. Production database servers generally do not have an external network.

curl https://dl.enterprisedb.com/default/release/get/13/rpm | sudo bash
yum list repmgr13

yum install repmgr13 -y

2.2.2 Source code compilation and installation

If the server does not have an external network, you can only use source code to install it.

The address for downloading the source code can be either of the following two addresses. One is the download address on the official website, and the other is the download address of gtihub.

wget https://github.com/EnterpriseDB/repmgr/archive/refs/tags/v5.3.3.tar.gz

// This is what I use

wget https://www.repmgr.org/download/repmgr-5.3.3.tar.gz

Install dependencies

yum check-update
yum groupinstall "Development Tools" -y
yum install -y yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum-builddep postgresql96// cannot be executed. There is no postgresql96yum source.

Replace with the following command
yum install -y cmake make gcc zlib gcc-c + + perl readline readline-devel zlib // Install the dependencies of pg13 installation by yourself

yum -y install yum-builddep flex libselinux-devel libxml2-devel libxslt-devel openssl-devel pam-devel readline-devel // on the blog

Download source code package

wget https://www.repmgr.org/download/repmgr-5.3.3.tar.gz

Unzip and install repmgr (install both primary and backup libraries)

tar -zxvf repmgr-5.3.3.tar.gz

Install using postgres user

chown -R postgres:postgres /home/packet/repmgr-5.3.3

su-postgres

cd /home/packet/repmgr-5.3.3

./configure

make install

Output the content to indicate that the binary command is installed in the directory ‘/usr/local/pgsql/bin/

/bin/install -c -m 755 repmgr repmgrd ‘/usr/local/pgsql/bin/’

Enter the directory and view /usr/local/pgsql/bin/. The following two commands are added.

You can verify whether the installation is successful by running repmgr –help

3 Quick Start

3.2 PostgreSQL deployment main library

Refer to my blog to deploy only the main database [PG] PostgreSQL13 master-slave flow replication deployment (details available) – CSDN Blog

3.3 Create users and databases

//Create user requires super user privileges
create user repmgr with superuser password 'repmgr' connection limit 10;

//Create metadatabase
 create database repmgr owner repmgr;

// repmgr will create repmgr schema to report errors to repmgr's metadata tables, functions, views, etc. It is recommended to set the search path of user repmgr as follows
ALTER USER repmgr SET search_path TO repmgr, "$user", public;

3.4 Configure authentication file pg_hba.conf 

# Allow user repmgr to connect to replication through local, 127.0.0.1, 10.79.21. network segments
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 10.79.21.0/24 trust

# Allow user repmgr to connect to repmgr schema through local, 127.0.0.1, 10.79.21.
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 10.79.21.0/24 trust

3.5 Set up password-free login

If there was no password for postgres before, you need to set a password first, otherwise you will be prompted to enter the password when copying the public key via ssh below.

Use postgres user, on both servers

on node1
ssh-keygen-trsa
ssh-copy-id [email protected]
ssh [email protected] date

on node2
ssh-keygen-trsa
ssh-copy-id [email protected]
ssh [email protected] date

// Database password-free login IP:port:schema?:user:passwd
vim.pgpass
10.79.21.30:5432:repmgr:repmgr:repmgr
10.79.21.29:5432:repmgr:repmgr:repmgr


Modify permissions to only allow postgres users to read and write
chmod 600 .pgpass

3.6 repmgr configuration file

repmgr.conf should not be stored in the PostgreSQL data directory as it may be overwritten when setting up or reinitializing the PostgreSQL server

vim repmgr.conf

node_id=1
node_name='node1'
conninfo='host=10.79.21.30 port=5432 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/home/storage/pgsql/data'

3.7 Register Master Node

For repmgr to support a replicated cluster, the master node must be registered with repmgr. This will install the repmgr extension and metadata objects and add metadata records for the master server

repmgr -f /etc/repmgr.conf primary register

$repmgr -f /etc/repmgr.conf primary register
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

Verify the status of the cluster as follows:

repmgr -f /etc/repmgr.conf cluster show

$repmgr -f /etc/repmgr.conf cluster show
 ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
---- + ------- + --------- + ----------- + ---------- + ---- ------ + ---------- + ---------- + ----------------------- -------------------------------------------------- -
 1 | node1 | primary | * running | | default | 100 | 1 | host=10.79.21.30 port=5432 user=repmgr dbname=repmgr connect_timeout=2

Records in the metadata table:

repmgr=# select * from nodes;
-[ RECORD 1 ]---- + ---------------------------------------- ----------------------------------
node_id | 1
upstream_node_id |
active|t
node_name | node1
type|primary
location | default
priority | 100
conninfo | host=10.79.21.30 port=5432 user=repmgr dbname=repmgr connect_timeout=2
repluser | repmgr
slot_name |
config_file | /etc/repmgr.conf

3.8 Clone standby Server

Edit the configuration file on the slave node
vim repmgr.conf
node_id=2
node_name='node2'
conninfo='host=10.79.21.29 port=5432 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/home/storage/pgsql/data'

Use the parameter –dry-run to check whether the slave library can be cloned

repmgr -h 10.79.21.30 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run
  • Check directory
  • Check whether the parameter max_wal_senders is greater than 2
  • Check parameter wal_log_hints
  • If the check passes, the backup command pg_basebackup -l “repmgr base backup” -D /home/storage/pgsql/data -h 10.79.21.30 -p 5432 -U repmgr -X stream will be executed.
$repmgr -h 10.79.21.30 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/home/storage/pgsql/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=10.79.21.30 user=repmgr dbname=repmgr
DETAIL: current installation size is 116 MB
INFO: "repmgr" extension is installed in database "repmgr"
INFO: replication slot usage not requested; no replication slot will be set up for this standby
INFO: parameter "max_wal_senders" set to 10
NOTICE: checking for available walsenders on the source node (2 required)
INFO: sufficient walsenders available on the source node
DETAIL: 2 required, 10 available
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: required number of replication connections could be made to the source server
DETAIL: 2 replication connections required
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
NOTICE: standby will attach to upstream node 1
HINT: consider using the -c/--fast-checkpoint option
INFO: would execute:
  pg_basebackup -l "repmgr base backup" -D /home/storage/pgsql/data -h 10.79.21.30 -p 5432 -U repmgr -X stream
INFO: all prerequisites for "standby clone" are met

You can see a warning. Set the parameter wal_log_hints to on and check again.

If there is no problem with the check, you can clone it

repmgr -h 10.79.21.30 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone

$repmgr -h 10.79.21.30 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone
NOTICE: destination directory "/home/storage/pgsql/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=10.79.21.30 user=repmgr dbname=repmgr
DETAIL: current installation size is 116 MB
INFO: replication slot usage not requested; no replication slot will be set up for this standby
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: checking and correcting permissions on existing directory "/home/storage/pgsql/data"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  pg_basebackup -l "repmgr base backup" -D /home/storage/pgsql/data -h 10.79.21.30 -p 5432 -U repmgr -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /home/storage/pgsql/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"

3.9 Verify that master-slave synchronization is normal

View on the main library

 SELECT * FROM pg_stat_replication;

can be seen

View on standy

SELECT * FROM pg_stat_wal_receiver;

3.10 Register slave node

Register the slave node using the following command

repmgr -f /etc/repmgr.conf standby register

$repmgr -f /etc/repmgr.conf standby register
INFO: connecting to local node "node2" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1)
INFO: standby registration complete
NOTICE: standby node "node2" (ID: 2) successfully registered
Verify successful registration

repmgr -f /etc/repmgr.conf cluster show
$repmgr -f /etc/repmgr.conf cluster show
 ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
---- + ------- + --------- + ----------- + ---------- + ---- ------ + ---------- + ---------- + ----------------------- -------------------------------------------------- -
 1 | node1 | primary | * running | | default | 100 | 1 | host=10.79.21.30 port=5432 user=repmgr dbname=repmgr connect_timeout=2
 2 | node2 | standby | running | node1 | default | 100 | 1 | host=10.79.21.29 port=5432 user=repmgr dbname=repmgr connect_timeout=2

Reference

repmgr official website

repmgr – Replication Manager for PostgreSQL clusters

5.3.3 version documentation

https://www.repmgr.org/docs/5.3/index.html

Latest Documentation (Version 5.4)

repmrg documentation

github repmgr

GitHub – EnterpriseDB/repmgr: A lightweight replication manager for PostgreSQL (Postgres)

refer to

Advanced database series (18): PostgreSQL based on repmgr high availability architecture practice_mingongge’s blog-CSDN blog

PostgreSQL + repmgr high availability deployment – Motianlun