前端之家收集整理的这篇文章主要介绍了
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,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()