PostgreSQL的查看主从同步状态
PostgreSQL的查看主从同步状态
PostgreSQL 提供了一些系统视图和函数，查看和监控主从同步的状态。
1 在主节点上查看同步状态
pg_stat_replication 视图
在主节点上，可以通过查询 pg_stat_replication 视图来查看复制的详细状态信息，包括每个从节点的同步进度、滞后情况以及连接状态。
postgres=# \d pg_stat_replication
View "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_receiver
View "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。如果两者没有显著滞后，可以认为同步状态正常。