PostgreSQL的查看主从同步状态
PostgreSQL的查看主从同步状态
PostgreSQL 提供了一些系统视图和函数，查看和监控主从同步的状态。
1 在主节点上查看同步状态
pg_stat_replication 视图
在主节点上，可以通过查询 pg_stat_replication 视图来查看复制的详细状态信息，包括每个从节点的同步进度、滞后情况以及连接状态。
postgres=# \d pg_stat_replicationView "pg_catalog.pg_stat_replication"Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | backend_xmin | xid | | | state | text | | | sent_lsn | pg_lsn | | | write_lsn | pg_lsn | | | flush_lsn | pg_lsn | | | replay_lsn | pg_lsn | | | write_lag | interval | | | flush_lag | interval | | | replay_lag | interval | | | sync_priority | integer | | | sync_state | text | | | reply_time | timestamp with time zone | | |
pid: 进程ID。usesysid: 使用者系统ID。usename: 复制连接的用户名。application_name: 复制连接的应用程序名称，通常每个从节点会设置一个唯一的名字。client_addr: 从节点的IP地址。state: 连接状态，可以是startup,catchup,streaming等。sent_lsn: 主节点最后发送给从节点的WAL记录的位置。write_lsn: 从节点接收到的最远的WAL记录的位置。flush_lsn: 从节点写入磁盘的最远的WAL记录的位置。replay_lsn: 从节点应用的最远的WAL记录的位置。sync_priority: 同步复制节点的优先级。sync_state: 同步状态，可以是async,potential,sync,quorum等。
示例
postgres=# select * from pg_stat_replication;pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | wr
ite_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+---
--------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------+-------------------------------55877 | 16384 | repmgr | test2 | 192.168.10.101 | | 34636 | 2024-09-15 12:13:48.651404-04 | | streaming | 0/44B8EF0 | 0/
44B8EF0 | 0/44B8EF0 | 0/44B8EF0 | 00:00:00.000237 | 00:00:00.000476 | 00:00:00.000537 | 0 | async | 2024-09-15 12:33:24.543761-04
(1 row)2 在从节点上查看同步状态
pg_stat_wal_receiver 视图
对从节点，可以通过查询 pg_stat_wal_receiver 视图来查看WAL接收器的状态。
postgres=# \d pg_stat_wal_receiverView "pg_catalog.pg_stat_wal_receiver"Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------pid | integer | | | status | text | | | receive_start_lsn | pg_lsn | | | receive_start_tli | integer | | | received_lsn | pg_lsn | | | received_tli | integer | | | last_msg_send_time | timestamp with time zone | | | last_msg_receipt_time | timestamp with time zone | | | latest_end_lsn | pg_lsn | | | latest_end_time | timestamp with time zone | | | slot_name | text | | | sender_host | text | | | sender_port | integer | | | conninfo | text | | | pid: 接收器进程ID。status: WAL接收状态。receive_start_lsn: 接收起始的LSN。received_lsn: 已接收的最新的LSN。last_msg_send_time: 主节点发送最后一个消息的时间。last_msg_receipt_time: 从节点接收最后一个消息的时间。latest_end_lsn: 最近接收的WAL记录的LSN。latest_end_time: 最近接收到的WAL记录的时间。
示例
postgres=# select * from pg_stat_wal_receiver;pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn |latest_end_time | slot_name | sender_host | sender_port | conninfo
-------+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+-------------------------------+----------------+
-------------------------------+-----------+----------------+-------------+------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------87404 | streaming | 0/3000000 | 5 | 0/44C0BF8 | 5 | 2024-09-15 12:35:33.842389-04 | 2024-09-15 12:35:33.842893-04 | 0/44C0BF8 |2024-09-15 12:35:33.842389-04 | | 192.168.10.100 | 5432 | user=repmgr passfile=/home/pg12/.pgpass dbname=replication host=192.168.10.100 port=5432 applic
ation_name=test2 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)
跨节点比对状态
为了确保主节点和从节点的状态是一致的，可以比对 pg_stat_replication 中的 sent_lsn 和 pg_stat_wal_receiver 中的 received_lsn。如果两者没有显著滞后，可以认为同步状态正常。