确定主从库
方法一
ps -ef|grep "wal" |grep -v "grep如果输出wal sender…streaming 进程说明当前数据库为主库
如果输出wal receiverstreaing 进程说明当前数据库为备用库
方法二
select * from pg_stat_replication;在主库上查询pg_stat_replication视图，如果返回记录说明是主库，备库上查询此视图无记录
方法三
select * from pg_stat_wal_receiver;如果返回记录说明是备库，流复制主库上此视图无记录
方法四
select pg_is_in_recovery();如果返回t说明是备库，返回f说明是主库
方法五
$ pg_controldata /app/pg/data_25_18802/data| grep cluster
Database cluster state: in production通过pg_controldata命令查看数据库控制信息，内容包含WAL日志信息、checkpoint、数据块等信息。
通过Database cluster state信息可以判断是主库还是备库。返回 in production表示为主库；返回 in archive recovery表示是备库。
物理复制检查
主库查看当前LSN
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
32AF/8CCE8000
(1 row)#9.x版本
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
110/9C8294B0
(1 row)查延时时间和事务
postgres=# select * from pg_stat_replication\gx --查看所有从库
-[ RECORD 1 ]----+------------------------------
pid | 33746
usesysid | 16384
usename | repl
application_name | xxxxx_18802
client_addr | xxxxx
client_hostname |
client_port | 18542
backend_start | 2020-11-11 09:26:52.53924+08
backend_xmin |
state | streaming
sent_lsn | 0/9000B78
write_lsn | 0/9000B78
flush_lsn | 0/9000B78
replay_lsn | 0/9000B78
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | quorum
reply_time | 2020-11-11 10:37:07.075014+08主要关注flush_lsn、reply_lsn、flush_lag、reply_lag。
查延时wal日志的日志量
postgres=# select
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay,
t.*
from pg_stat_replication t\gx
-[ RECORD 1 ]----+---------------------------------
sent_delay | 0 bytes
write_delay | 0 bytes
flush_delay | 0 bytes
replay_delay | 0 bytes
pid | 56651
usesysid | 16384
usename | repl
application_name | xxxx_18801
client_addr | xxxxx
client_hostname |
client_port | 45920
backend_start | 2023-06-20 11:24:52.832079+08
backend_xmin | 216337073
state | streaming
sent_lsn | 32AF/8F129710
write_lsn | 32AF/8F129710
flush_lsn | 32AF/8F129710
replay_lsn | 32AF/8F129710
write_lag | 00:00:00.000178
flush_lag | 00:00:00.000222
replay_lag | 00:00:00.000501
sync_priority | 1
sync_state | quorum
reply_time | 2023-09-18 08:59:39.174314+08在从库检查延时时间
postgres=# select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp(), now() - pg_last_xact_replay_timestamp() as replay_lag;
pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp | replay_lag
-------------------------+------------------------+-------------------------------+-----------------
32AF/9CAE1A68 | 32AF/9CAE1A68 | 2023-09-18 09:09:34.454073+08 | 00:00:00.050197
(1 row)其它状态检查
-- 查看当前WAL应用是否暂停了
postgres=# select pg_is_wal_replay_paused();
pg_is_wal_replay_paused
-------------------------
f
(1 row)
-- 查看WAL接收到的位点
postgres=# select pg_last_wal_receive_lsn();
pg_last_wal_receive_lsn
-------------------------
1/C0000060
(1 row)
-- 查看WAL的应用位点
postgres=# select pg_last_wal_replay_lsn();
pg_last_wal_replay_lsn
------------------------
1/C0000060
(1 row)
-- 查看wal receiver的统计信息
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_get_wal_receiver();
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 11349
status | streaming
receive_start_lsn | 1/C0000000
receive_start_tli | 1
received_lsn | 1/C0000060
received_tli | 1
last_msg_send_time | 2017-07-11 17:23:14.372327+08
last_msg_receipt_time | 2017-07-11 17:23:14.372361+08
latest_end_lsn | 1/C0000060
latest_end_time | 2017-07-11 17:15:13.819553+08
slot_name |
conninfo | user=rep password=******** dbname=replication host=127.0.0.1 port=2921 fallback_application_name=walreceiver sslmode=disable sslcompression=1 target_session_attrs=any
postgres=# select pg_wal_replay_pause();
-[ RECORD 1 ]-------+-
pg_wal_replay_pause |
-- 暂停WAL的应用，例如要做一些排错时
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | t
postgres=# select pg_wal_replay_resume();
-[ RECORD 1 ]--------+-
pg_wal_replay_resume |
-- 继续应用WAL
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | f逻辑复制
某些情况下，逻辑复制的延时情况通过上面的 方法查出来是不准确的，可以通过下面的方法进行查询
select
b.slot_name,
a.pid,
a.usename,
a.application_name,
a.state,
a.sent_lsn,
a.write_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), a.write_lsn)) diff_size,
(pg_wal_lsn_diff(pg_current_wal_lsn(), a.write_lsn) / c.size)::int as diff_num,
date_trunc('second', d.modification - c.modification) delay_time,
a.flush_lsn,
a.replay_lsn,
a.sync_state,
b.database,
b.active,
b.restart_lsn,
b.confirmed_flush_lsn
from
pg_stat_replication a
join pg_replication_slots b on
a.pid = b.active_pid
left join pg_ls_waldir() c on
c.name = pg_walfile_name(a.write_lsn)
left join pg_ls_waldir() d on
d.name = pg_walfile_name(pg_current_wal_lsn())
order by
b.slot_name；
slot_name | pid | usename | application_name | state | sent_lsn | write_lsn | diff_size | diff_num | delay_time | flush_lsn | replay_lsn | sync_state | database | active | restart_lsn | confirmed_flush_lsn
----------------------------+-------+-----------+----------------------------------+-----------+---------------+---------------+-----------+----------+------------+---------------+---------------+------------+----------+--------+---------------+---------------------
slot1 | 21601 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A3EDB6F0 | 1870 kB | 0 | 00:00:02 | 32A8/99503AE0 | 32A8/99503AE0 | async | db11 | t | 32A0/92FBC620 | 32A8/99503AE0
slot2 | 5080 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A400EDC8 | 640 kB | 0 | 00:00:00 | 32AF/A3F4D8E8 | 32AF/A3F4D8E8 | async | db11 | t | 32AF/949F9B90 | 32AF/A3F4D8E8
slot3 | 42160 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A40A0C80 | 57 kB | 0 | 00:00:00 | 32A8/B84B4620 | 32A8/B84B4620 | async | db11 | t | 32A4/5475DA70 | 32A8/B84B4620
slot4 | 72831 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A3F536D0 | 1390 kB | 0 | 00:00:02 | 32AF/9FEE3850 | 32AF/9FEE3850 | async | db11 | t | 32AF/98C4E718 | 32AF/9FEE3850
slot5 | 21608 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A3F472D8 | 1439 kB | 0 | 00:00:02 | 32A8/99503AE0 | 32A8/99503AE0 | async | db11 | t | 32A0/B6DA0FC0 | 32A8/99503AE0
slot6 | 21588 | sync | Sync_inc@xxxxxxxxxxxxxxxxxxxx | streaming | 32AF/A40AEF90 | 32AF/A4079BE8 | 213 kB | 0 | 00:00:00 | 32A8/BC7AB210 | 32A8/BC7AB210 | async | db11 | t | 32A3/E1AB3C68 | 32A8/BC7AB210
(6 rows)如果没有主键或者位置索引的表被发布之后，将无法进行dml操作，以下查出被发布但是没有设置去全列标识的表
select
c.relnamespace::regnamespace::text, c.relname, c.relreplident
from pg_class c
where
(c.relnamespace::regnamespace::text, c.relname)
in (select pt.schemaname, pt.tablename from pg_publication_tables pt)
and c.oid not in (select conrelid from pg_constraint pc where pc.contype ='p')
and c.relreplident = 'd'