【postgresql】repmgr 日常运维操作

戒掉贪嗔痴(薛双奇)

于 2024-09-02 16:57:54 发布

阅读量78

点赞数

分类专栏：数据库运维-PostgreSQL 文章标签： postgresql 运维大数据

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141824177

版权

数据库运维-PostgreSQL 专栏收录该内容

49 篇文章 5 订阅

订阅专栏

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)