1.repmgr 日常运维操作
$ repmgr primary register --force -f /postgresql/app/postgresql/repmgr.conf
--1备库执行
$ repmgr standby register --force -f /postgresql/app/postgresql/repmgr.conf
--2备库执行
$ repmgr standby register --force -f /postgresql/app/postgresql/repmgr.conf
--64节点执行
[pgsql@pg3:/home/pgsql]$repmgr standby register --force -f /postgresql/app/postgresql/repmgr.conf
INFO: connecting to local node "pg3" (ID: 3)
INFO: connecting to primary database
INFO: standby registration complete
NOTICE: standby node "pg3" (ID: 3) successfully registered
$ repmgr witness register --force -f /postgresql/app/postgresql/repmgr.conf -h fgedudb61 -U repmgr -d repmgr
2.检查集群状态
[pgsql@pg3:/home/pgsql]$repmgr -f /postgresql/app/postgresql/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------
1 | pg1 | primary | * running | | default | 100 | 6 | host=192.168.1.10 port=5432 user=repmgr dbname=repmgr connect_timeout=2
2 | pg2 | standby | running | pg1 | default | 100 | 6 | host=192.168.1.11 port=5432 user=repmgr dbname=repmgr connect_timeout=2
3 | pg3 | standby | running | pg1 | default | 100 | 6 | host=192.168.1.12 port=5432 user=repmgr dbname=repmgr connect_timeout=2
4 | pg4 | witness | * running | pg1 | default | 0 | n/a | host=192.168.1.13 port=5432 user=repmgr dbname=repmgr connect_timeout=2
(2)crosscheck
[pgsql@pg3:/home/pgsql]$repmgr -f /postgresql/app/postgresql/repmgr.conf cluster crosscheck
INFO: connecting to database
Name | ID | 1 | 2 | 3 | 4
------+----+---+---+---+---
pg1 | 1 | * | * | * | *
pg2 | 2 | * | * | * | *
pg3 | 3 | * | * | * | *
pg4 | 4 | * | * | * | *
(3)集群事件
[pgsql@pg3:/home/pgsql]$repmgr -f /postgresql/app/postgresql/repmgr.conf cluster event
Node ID | Name | Event | OK | Timestamp | Details
---------+------+----------------------------+----+---------------------+---------------------------------------------------------------------------------------
3 | pg3 | standby_register | t | 2024-09-02 16:47:12 | standby registration succeeded; upstream node ID is 1 (-F/--force option was used)
2 | pg2 | standby_register | t | 2024-09-02 16:47:03 | standby registration succeeded; upstream node ID is 1 (-F/--force option was used)
1 | pg1 | primary_register | t | 2024-09-02 16:46:50 | existing primary record updated
1 | pg1 | child_node_new_connect | t | 2024-09-02 16:43:33 | new standby "pg2" (ID: 2) has connected
2 | pg2 | standby_register | t | 2024-09-02 16:43:32 | standby registration succeeded; upstream node ID is 1 (-F/--force option was used)
2 | pg2 | repmgrd_standby_reconnect | t | 2024-09-02 16:43:26 | node restored as standby after 195 seconds, monitoring connection to upstream node -1
2 | pg2 | standby_clone | t | 2024-09-02 16:43:16 | cloned from host "192.168.1.10", port 5432; backup method: pg_basebackup; --force: Y
1 | pg1 | child_node_new_connect | t | 2024-09-02 16:37:00 | new witness "pg4" (ID: 4) has connected
1 | pg1 | child_node_new_connect | t | 2024-09-02 16:37:00 | new standby "pg3" (ID: 3) has connected
3 | pg3 | repmgrd_failover_follow | t | 2024-09-02 16:36:56 | node "pg3" (ID: 3) now following new upstream node "pg1" (ID: 1)
3 | pg3 | standby_follow | t | 2024-09-02 16:36:56 | standby attached to upstream node "pg1" (ID: 1)
4 | pg4 | repmgrd_upstream_reconnect | t | 2024-09-02 16:36:56 | witness monitoring connection to primary node "pg1" (ID: 1)
4 | pg4 | repmgrd_failover_follow | t | 2024-09-02 16:36:55 | witness node "pg4" (ID: 4) now following new primary node "pg1" (ID: 1)
1 | pg1 | repmgrd_reload | t | 2024-09-02 16:36:55 | monitoring cluster primary "pg1" (ID: 1)
1 | pg1 | repmgrd_failover_promote | t | 2024-09-02 16:36:55 | node "pg1" (ID: 1) promoted to primary; old primary "pg2" (ID: 2) marked as failed
1 | pg1 | standby_promote | t | 2024-09-02 16:36:55 | server "pg1" (ID: 1) was successfully promoted to primary
2 | pg2 | child_node_new_connect | t | 2024-09-02 16:35:26 | new witness "pg4" (ID: 4) has connected
2 | pg2 | repmgrd_start | t | 2024-09-02 16:35:21 | monitoring cluster primary "pg2" (ID: 2)
4 | pg4 | repmgrd_start | t | 2024-09-02 16:35:19 | witness monitoring connection to primary node "pg2" (ID: 2)
3 | pg3 | repmgrd_start | t | 2024-09-02 16:35:17 | monitoring connection to upstream node "pg2" (ID: 2)
(4)启动事件
[pgsql@pg3:/home/pgsql]$repmgr -f /postgresql/app/postgresql/repmgr.conf cluster event --event=repmgrd_start
Node ID | Name | Event | OK | Timestamp | Details
---------+------+---------------+----+---------------------+-------------------------------------------------------------
2 | pg2 | repmgrd_start | t | 2024-09-02 16:35:21 | monitoring cluster primary "pg2" (ID: 2)
4 | pg4 | repmgrd_start | t | 2024-09-02 16:35:19 | witness monitoring connection to primary node "pg2" (ID: 2)
3 | pg3 | repmgrd_start | t | 2024-09-02 16:35:17 | monitoring connection to upstream node "pg2" (ID: 2)
1 | pg1 | repmgrd_start | t | 2024-09-02 16:35:15 | monitoring connection to upstream node "pg2" (ID: 2)
4 | pg4 | repmgrd_start | t | 2024-09-02 14:45:25 | witness monitoring connection to primary node "pg1" (ID: 1)
3 | pg3 | repmgrd_start | t | 2024-09-02 14:26:14 | monitoring connection to upstream node "pg1" (ID: 1)
3 | pg3 | repmgrd_start | t | 2024-09-02 14:20:46 | monitoring connection to upstream node "pg1" (ID: 1)
3 | pg3 | repmgrd_start | t | 2024-09-02 14:20:30 | monitoring connection to upstream node "pg1" (ID: 1)
2 | pg2 | repmgrd_start | t | 2024-09-02 14:17:25 | monitoring connection to upstream node "pg1" (ID: 1)
2 | pg2 | repmgrd_start | t | 2024-09-02 14:17:00 | monitoring connection to upstream node "pg1" (ID: 1)
1 | pg1 | repmgrd_start | t | 2024-09-02 14:07:14 | monitoring cluster primary "pg1" (ID: 1)
1 | pg1 | repmgrd_start | t | 2024-09-02 14:06:22 | monitoring cluster primary "pg1" (ID: 1)
(5)复制统计i信息
postgres=# SELECT * FROM pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend
_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_pri
ority | sync_state | reply_time
------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------
------+-----------+------------+------------+------------+------------+-----------------+-----------------+-----------------+---------
------+------------+-------------------------------
7360 | 24891 | repmgr | pg2 | 192.168.1.11 | | 53596 | 2024-09-02 16:43:25.846084+08 |
| streaming | 0/61004630 | 0/61004630 | 0/61004630 | 0/61004630 | 00:00:00.000412 | 00:00:00.000846 | 00:00:00.000848 |
0 | async | 2024-09-02 16:51:17.453623+08
6956 | 24891 | repmgr | pg3 | 192.168.1.12 | | 60768 | 2024-09-02 16:36:55.831629+08 |
| streaming | 0/61004630 | 0/61004630 | 0/61004630 | 0/61004630 | 00:00:00.000921 | 00:00:00.001785 | 00:00:00.00197 |
0 | async | 2024-09-02 16:51:12.77574+08
(2 rows)
postgres=# SELECT * FROM pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time |
last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port |
conninfo
------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+
-------------------------------+----------------+-------------------------------+---------------+--------------+-------------+--------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
7591 | streaming | 0/60000000 | 6 | 0/61005660 | 0/61005660 | 6 | 2024-09-02 16:52:02.133378+08 |
2024-09-02 16:52:02.712264+08 | 0/61005660 | 2024-09-02 16:52:02.133378+08 | repmgr_slot_2 | 192.168.1.10 | 5432 | user=re
pmgr passfile=/home/pgsql/.pgpass channel_binding=prefer connect_timeout=2 dbname=replication host=192.168.1.10 port=5432 application_
name=pg2 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=pr
efer krbsrvname=postgres target_session_attrs=any
(1 row)