查看同步状态

主库使用 pg_stat_replication 监控流复制

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 2484
usesysid         | 16384
usename          | replicator
application_name | pg2
client_addr      | 192.168.75.92
client_hostname  | 
client_port      | 33720
backend_start    | 2022-06-13 16:20:44.529605+08
backend_xmin     | 734
state            | streaming
sent_lsn         | 0/50001C0
write_lsn        | 0/50001C0
flush_lsn        | 0/50001C0
replay_lsn       | 0/50001C0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2022-06-13 16:28:33.781275+08

postgres=#

备库使用 pg_stat_wal_receiver 监控流复制

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 6541
status                | streaming
receive_start_lsn     | 0/5000000
receive_start_tli     | 1
written_lsn           | 0/50001C0
flushed_lsn           | 0/50001C0
received_tli          | 1
last_msg_send_time    | 2022-06-13 16:29:13.702837+08
last_msg_receipt_time | 2022-06-13 16:29:13.703523+08
latest_end_lsn        | 0/50001C0
latest_end_time       | 2022-06-13 16:25:43.374935+08
slot_name             | 
sender_host           | 192.168.75.91
sender_port           | 5432
conninfo              | user=replicator password=******** channel_binding=prefer dbname=replication host=192.168.75.91 port=5432 application_name=pg2 fallback_application_name=PGCluster sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

postgres=#

查看备库落后主库多少字节的WAL日志：

postgres=# select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(),write_lsn)) delay_wal_size,* from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
delay_wal_size   | 0 bytes
pid              | 2484
usesysid         | 16384
usename          | replicator
application_name | pg2
client_addr      | 192.168.75.92
client_hostname  | 
client_port      | 33720
backend_start    | 2022-06-13 16:20:44.529605+08
backend_xmin     | 734
state            | streaming
sent_lsn         | 0/50001C0
write_lsn        | 0/50001C0
flush_lsn        | 0/50001C0
replay_lsn       | 0/50001C0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2022-06-13 16:32:04.140511+08

postgres=#

查看备库接收WAL日志和应用WAL日志的状态：

select * from pg_last_wal_receive_lsn();
select * from pg_last_wal_replay_lsn();  
select * from pg_last_xact_replay_timestamp(); 

postgres=# select * from pg_last_wal_receive_lsn();
 pg_last_wal_receive_lsn 
-------------------------
 0/60001C0
(1 row)

postgres=# select * from pg_last_wal_replay_lsn();
 pg_last_wal_replay_lsn 
------------------------
 0/60001C0
(1 row)

postgres=# select * from pg_last_xact_replay_timestamp();
 pg_last_xact_replay_timestamp 
-------------------------------
 
(1 row)

postgres=#

主备的判断方式

数据库层面

# 通过系统函数查看(f为主库，t为备库:如果恢复仍在进行中为true)
select pg_is_in_recovery();

# 查看只读模式
show transaction_read_only;

操作系统层

# 操作系统上查看WAL发送进程或WAL接收进程(看walsender或者walreceiver)
ps -ef | grep "wal" | grep -v "grep"
ps -ef | grep postgres 

# 查看数据库控制文件信息（返回in production表示为主库，返回in archive recovery表示是备库）
pg_controldata | grep cluster

# 12以前的版本还可以检查是否存在recovery.conf配置文件