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

前端之家收集整理的这篇文章主要介绍了PostgreSQL学习篇16.3 检查备库及流复制情况前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
  1. 检查异步流复制情况:
  2. 主库查询
  3. select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  4.  
  5. postgres=# \d pg_stat_replication;
  6. View "pg_catalog.pg_stat_replication"
  7. Column | Type | Modifiers
  8. ------------------+--------------------------+-----------
  9. pid | integer |
  10. usesysid | oid |
  11. usename | name |
  12. application_name | text |
  13. client_addr | inet |
  14. client_hostname | text |
  15. client_port | integer |
  16. backend_start | timestamp with time zone |
  17. backend_xmin | xid |
  18. state | text |
  19. sent_location | pg_lsn |
  20. write_location | pg_lsn |
  21. flush_location | pg_lsn |
  22. replay_location | pg_lsn |
  23. sync_priority | integer |
  24. sync_state | text |
  25.  
  26.  
  27. 查看备库落后主库多少字节的WAL日志:
  28. select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication;
  29.  
  30. 检查同步流复制的情况:
  31. select pid,sync_state from pg_stat_replication;
  32.  
  33. 将主库上WAL位置转换为WAL文件名和偏移量:
  34. select * from pg_xlogfile_name_offset('');
  35. postgres=# select write_location from pg_stat_replication;
  36. write_location
  37. ----------------
  38. 0/15008550
  39. (1 row)
  40.  
  41. postgres=# select * from pg_xlogfile_name_offset('0/15008550');
  42. file_name | file_offset
  43. --------------------------+-------------
  44. 000000010000000000000015 | 34128
  45. (1 row)
  46.  
  47. postgres=#
  48.  
  49.  
  50. 查看备库状态:
  51. select pg_is_in_recovery(); --主库为f,备库为t
  52. 如果不能连进去,可以用命令行工具:
  53. pg_controldata
  54.  
  55. hot standby,查看备库接收WAL日志和应用WAL日志的状态:
  56. pg_last_xlog_receive_location()
  57. pg_last_xlog_replay_location()
  58. pg_last_xact_replay_timestamp()

猜你在找的Postgre SQL相关文章