【PgSQL流复制管理】检测主备库复制状态

master:

postgres=# select pid,usename,application_name,client_addr,client_port,backend_start,state,write_lsn,replay_lsn,sync_priority,sync_state from pg_stat_replication;
pid | usename | application_name | client_addr | client_port | backend_start | state | write_lsn | replay_lsn | sync_priority | sync_state
------+---------+------------------+-----------------+-------------+-------------------------------+-----------+--------------+--------------+---------------+------------
1531 | repuser | walreceiver | 100.126.209.202 | 47058 | 2021-12-04 16:07:47.759557+08 | streaming | FCF/3C1D0C70 | FCF/3C1D0C70 | 0 | async
(1 row)

字段state显示的同步状态有：startup（连接中）、catchup（同步中）、streaming（同步）；

字段sync_state显示的模式有：async（异步）、sync（同步）、potential（虽然现在是异步模式，但是有可能升级到同步模式）

复制用户

postgres=# select * from pg_user where usesysid='16384';
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
repuser | 16384 | f | f | t | f | ******** | |
(1 row)

主从延迟：

postgres=# SELECT
postgres-# pg_wal_lsn_diff(A .c1, replay_lsn) /(1024 * 1024) AS slave_latency_MB,
postgres-# pg_wal_lsn_diff(A .c1, sent_lsn) /(1024 * 1024) AS send_latency_MB,
postgres-# pg_wal_lsn_diff(A .c1, flush_lsn) /(1024 * 1024) AS flush_latency_MB,
postgres-# state,
postgres-# backend_start,
postgres-# now()::timestamp with time zone
postgres-# FROM pg_stat_replication, pg_current_wal_lsn() AS A(c1)
postgres-# WHERE client_addr='100.126.209.202' and application_name = 'repuser'
postgres-# ORDER BY slave_latency_MB, send_latency_MB DESC
postgres-# LIMIT 1;
slave_latency_mb | send_latency_mb | flush_latency_mb | state | backend_start | now
------------------+-----------------+------------------+-------+---------------+-----
(0 rows)

数据库活动相关的信息

 postgres=# select datname,client_addr,client_port,backend_start,query_start,query from pg_stat_activity where datname = 'zedb';

 

standby 节点：

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

postgres=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2021-12-05 11:54:06.781469+08
(1 row

 

查看后端进程运行状态

postgres=# select pid,state,CURRENT_TIMESTAMP - least(query_start,xact_start) AS runtime,
substr(query,1,25) AS current_query
from pg_stat_activity
where not pid = pg_backend_pid();
pid | state | runtime | current_query
-------+-------+---------+---------------
826 | | |
828 | | |
827 | | |
10088 | | |
(4 rows)