Author: Yan Bo Qingyun Technology database development engineer

Currently engaged in PostgreSQL product development work, keen on PostgreSQL database learning and research

| REPMGR profile

Repmgr [1] is an open source tool for managing replication and failover within a Cluster of PostgreSQL servers. Repmgr supports and enhances PostgreSQL’s built-in stream replication, which provides a single read/write primary server and one or more read-only secondary servers.

The RepMgr stream replication management tool manages PostgreSQL cluster nodes in a distributed manner. Each node in the cluster has a repMgr. conf configuration file, which records the ID, node name, connection information, PGDATA directory of the database and other configuration parameters of the node. After the parameters are configured, one-click deployment of cluster nodes can be implemented using the repmgr command.

The architecture diagram of RepMgr (image source: repmgr.org/) is as follows:

After cluster nodes are deployed, each node can use the RepMgrd daemon to monitor the node database status. Each node metadata table can be maintained independently. These metadata tables record information about all cluster nodes.

The election principle

In the event of an Auto Failover, after the standby node fails to connect to the primary node for several times (the number and interval of attempts can be modified through the repMgr. conf configuration file), repMgrd elects a candidate standby node (see the election mechanism below) among all standby nodes to be promoted to the new primary node. Other standby nodes Follow to the new master to form a new cluster.

Repmgr election Candidate standby nodes are elected in the following order: LSN > Priority > Node_ID

  • The system preferentially elects a node with a large LSN as a candidate standby node.
  • If the LSN is the same, the node is compared according to the Priority Priority (the Priority is configured in the configuration file. If the Priority is 0, the node is prohibited from being promoted to the active node).
  • If the priority is the same, the Node ID of the Node is compared, and the smaller one is elected first.

Two tools

Repmgr mainly provides two tools, repMgr and repMgrd.

Repmgr is a command line tool that performs administrative tasks and facilitates the management of PostgreSQL server clusters. It has the following features:

  • Setting the secondary Server
  • Promote for
  • Master-slave switch
  • Displays the status of servers in a replication cluster

Repmgrd is a daemon that actively monitors servers in a replication cluster and supports the following tasks:

  • Monitor and record replication cluster information
  • Fault detection and failover
  • Notification of events in the cluster (requires custom scripts to accept notifications)

Users and metadata

To manage the replication cluster effectively, RepMgr needs to store information about the nodes in the cluster in repMgr dedicated database tables. This schema is automatically created by the RepMgr extension, which is installed in the first step of initializing the cluster managed by RepMgr (RepMgr Primary Register) and contains the following objects:

  • Tables:
    • repmgr.events: records events of interest
    • repmgr.nodes: Connection and status information of each node in the replication cluster
    • repmgr.monitoring_history: repmgrd Indicates historical standby monitoring information written by repmgrd
  • Views:
    • repmgr.show_nodesBased on:repmgr.nodesTable, in addition to showing the names of nodes upstream from the server
    • repmgr.replication_status: Displays the monitoring status of each standby when repMgrd is enabled. The REPMgr metadata information can be stored in an existing database or in your own dedicated database.

Note: RepMgr metadata information cannot be stored on PostgreSQL servers that are not part of the replication cluster managed by RepMgr. Repmgr needs a user who can access the database and make the necessary changes. This user may not be a superuser, but some operations (such as the initial installation of the RepMgr extension) will require a superuser connection (you can specify –superuser using the command line option if needed).

| install repmgr

Note: The same “main” repMgr version (for example 5.2.1.x) must be installed on all nodes of the cluster [2].

Repmgr version

Repmgr version Supported PostgreSQL version The latest version
Repmgr 5.2 9.4, 9.5, 9.6, 10, 11, 12, 13 5.2.1 (2020-12-07)
Repmgr 5.1 9.3, 9.4, 9.5, 9.6, 10, 11, 12 5.1.0 (2020-04-13)
Repmgr 5.0 9.3, 9.4, 9.5, 9.6, 10, 11, 12 5.0 (2019-10-15)
repmgr 4.x 9.3, 9.4, 9.5, 9.6, 10, 11 4.4 (2019-06-27)
  • Repmgr 2.x and 3.x series are no longer maintained and are not listed here.
  • The RepMgr 4.x series will not be released after the release of RepMgr 5.0.

The installation process

Take repMgr 5.2.x as an example, Clone and install repMgr from the source repository.

$ git clone https://github.com/EnterpriseDB/repmgr
$ git checkout REL5_2_STABLE
$ cd repmgr/
./configure
$ make install
Copy the code

After make install succeeds, two executable files repmgr and repmgrd will be found in pg_bin_path.

| use repmgr

Basic syntax for the repmgr tool [3] :

repmgr [OPTIONS] primary {register|unregister} repmgr [OPTIONS] standby {register|unregister|clone|promote|follow|switchover} repmgr [OPTIONS] node {status|check|rejoin|service} repmgr [OPTIONS] cluster {show|event|matrix|crosscheck|cleanup} repmgr [OPTIONS] witness {register|unregister} repmgr [OPTIONS]  service {status|pause|unpause} repmgr [OPTIONS] daemon {start|stop}Copy the code
  • General configuration options
-b, --pg_bindir=PATH PATH of the PostgreSQL binary file (optional) -f, --config-file=PATH PATH of the repmgr configuration file -f, --force To perform potentially dangerous operations forciblyCopy the code
  • Database connection options
-d, -- dbName = dbname Database to connect to (default: postgres) -h, --host=HOSTNAME Database server host -p, --port= port Database server port (default: postgres) "5432") -u, --username= username database username to connect to (default: "postgres")Copy the code
  • Node-specific options
-d, --pgdata=DIR Location of the node data directory --node-id Specifies a node by ID (only applicable to some operations) --node-name specifies a node by name (only applicable to some operations)Copy the code
  • Recording options
--dry-run shows what happens to the action, but does not execute it -l, --log-level sets the log level (overwriting the configuration file; Default value: NOTICE) --log-to-file records to files defined in repMgr. conf (or logging tool) -q, --quiet disables all log output except errors -t, --terse does not display details, prompts and other non-critical output -v, --verbose displays additional log output (for debugging)Copy the code

Common operations

  • Action class
The command describe
repmgr primary register Register the current node as the primary node
repmgr primary unregister Deregister the primary primary node
repmgr standby clone The current node uses pg_basebackup to copy the data directory from the primary primary node
repmgr standby register Registers the current node as a standby node
repmgr standby unregister Deregister a Standby Node
repmgr standby promote Promote the standby node to the primary primary node
repmgr standby follow In a single-master multi-slave architecture, the standby node points back to the new primary primary node
repmgr standby switchover Promote the specified standby node to the primary primary node and demote the primary primary node to the standby node
repmgr witness register Register the current node as the witness server node
repmgr witness unregister Unregister the witness server node
  • See the class
The command describe
repmgr node status View the basic information and replication status of each node
repmgr node check Check the status of ha cluster nodes
repmgr node rejoin Rejoin a failed node to the cluster
repmgr cluster show View the basic information and status of registered nodes in a cluster
repmgr cluster matrix View the matrix information of all nodes in the cluster
repmgr cluster crosscheck View cross-connection detection between all nodes in the cluster in pairs
repmgr cluster event View cluster event records
repmgr cluster cleanup Clear cluster monitoring history

Next up

In the next installment, we will use RepMgr to take you step-by-step through building a PostgreSQL high availability cluster.

reference

[1]. Repmgr:github.com/EnterpriseD…

[2].5.2.1 Documentation: repmgr.org/docs/5.2/

[3]. Common operations: blog.csdn.net/weixin_3769…