PostgreSQL学习篇16.3 检查备库及流复制情况

检查异步流复制情况：主库查询：select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;postgres=# \d pg_stat_replication;          View "pg_catalog.pg_stat_replication"      Column      |           Type           | Modifiers------------------+--------------------------+----------- 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_location    | pg_lsn                   | write_location   | pg_lsn                   | flush_location   | pg_lsn                   | replay_location  | pg_lsn                   | sync_priority    | integer                  | sync_state       | text                     | 查看备库落后主库多少字节的WAL日志：select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication;检查同步流复制的情况：select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;将主库上WAL位置转换为WAL文件名和偏移量：select * from pg_xlogfile_name_offset('');postgres=# select write_location from pg_stat_replication; write_location---------------- 0/15008550(1 row)postgres=# select * from pg_xlogfile_name_offset('0/15008550');        file_name         | file_offset--------------------------+------------- 000000010000000000000015 |       34128(1 row)postgres=#查看备库状态：select pg_is_in_recovery();  --主库为f，备库为t如果不能连进去，可以用命令行工具：pg_controldata在hot standby，查看备库接收WAL日志和应用WAL日志的状态：pg_last_xlog_receive_location()pg_last_xlog_replay_location()pg_last_xact_replay_timestamp()