一、主备数据一致性
1、通过 wal 检测两个数据库实例数据是否一致（该sql语句需要在主库上执行）
1.1 pg_stat_replication 可以进行流复制监控（pg_stat_replication视图显示WAL发送进程的详细信息）
postgres=#SELECT * FROM pg_stat_replication ; -[RECORD 1 ]--—-----+-—---------------------- pid | 7683 usesysid | 16384 usename | repuser application_name | node2 client_addr | 192.168.28.75 client_hostname | client_port | 57870 backend_start | 2017-09-05 11:50:31.629468+08 backend_xmin | state | streaming sent_lsn | 3/643CB568 write_lsn | 3/643CB568 flush_lsn | 3/643CB488 replay_lsn | 3/643CB030 write_lag | 00:00:00.000224 flush_lag | 00:00:00.001562 replay_lag | 00:00:00.006596 sync_priority | 1 sync_state | sync
视图中的主要字段解释如下:
- pid: WAL发送进程的进程号。
- usename: WAL发送进程的数据库用户名。
- application_name :连接WAL发送进程的应用别名，此参数显示值为备库recovery.conf配置文件中primary_conninfo参数application_name选项的值。
- client_addr:连接到WAL发送进程的客户端IP地址，也就是备库的IP。backend_start: WAL发送进程的启动时间。
- state:显示WAL发送进程的状态，startup表示WAL进程在启动过程中; catchup表示备库正在追赶主库;streaming表示备库已经追赶上了主库，并且主库向备库发送WAL日志流，这个状态是流复制的常规状态;backup表示通过pg_basebackup正在进行备份; stopping表示 WAL发送进程正在关闭。
- sent_lsn: WAL发送进程最近发送的WAL日志位置。
- write_Isn :备库最近写入的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写入备库 WAL日志文件。
- flush_Isn:备库最近写入的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。
- replay_lsn:备库最近应用的WAL日志位置。
- write_lag :主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流还没写入备库 WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。
- flush_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，但还没有应用WAL日志）并返回确认信息的时间。
- replay_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，并且已应用WAL日志）并返回确认信息的时间。
- sync_priority:基于优先级的模式中备库被选中成为同步备库的优先级，对于基于quorum的选举模式此字段则无影响。
- sync_state:同步状态，有以下状态值，async表示备库为异步同步模式; potential表示备库当前为异步同步模式，如果当前的同步备库宕机，异步备库可升级成为同步备库;sync表示当前备库为同步模式;quorum表示备库为quorum standbys 的候选。
1.2 pg_stat_wal_receiver 监控备库的信息（显示WAL接收进程详细信息）
postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------
pid | 22573
status | streaming
receive_start_lsn | 3/2D000000
receive_start_tli | 1
received_lsn | 3/852DC428
received_tli | 1
last_msg_send_time | 2017-09-06 15:35:28.178167+08
last_msg_receipt_time | 2017-09-06 15:35:28.177706+08
latest_end_lsn | 3/852DC508
latest_end_time | 2017-09-0615:35:28.178167+08
slot_name |
conninfo | user=repuser passfile=/home/postgres/.pgpass dbname=replication
host=192.168.28.74 port=1921 application_name=node2 fallback_application_name=walreceiver sslmode=disable sslcompression=1 target_session_attrs=any
说明：
- pid:WAL接收进程的进程号。status: WAL接收进程的状态。
- receive_start_lsn: WAL接收进程启动后使用的第一个 WAL日志位置。received_lsn:最近接收并写入WAL日志文件的WAL位置。
- last_msg_send_time :备库接收到发送进程最后一个消息后，向主库发回确认消息的发送时间。
- last_msg_receipt_time:备库接收到发送进程最后一个消息的接收时间。
- conninfo: WAL接收进程使用的连接串，连接信息由备库SPGDATA目录的recovery.
- conf配置文件的 primary_conninfo参数配置
1.3 数据一致性检测
1.3.1 使用 WAL日志应用延迟量衡量 监控主备同步数据延迟
select pid ,usename,client_addr,state,pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_depaly,pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_delay,write_lag,flush_lag,replay_lag from pg_stat_replication;
说明：
- lsn：在pg中的每写入一条记录都会在wal日志中增加一条wal记录，写入这个记录的位置就是lsn，全称为Log Sequence Number，lsn的值是wal文件中字节偏移量
- pg_current_wal_lsn()：函数显示流复制主库当前WAL日志文件写入的位置
- pg_wal_Isn_diff()：函数计算两个WAL日志位置之间的偏移量，返回单位为字节数
- write_Isn :备库最近写入的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写入备库 WAL日志文件。
- flush_Isn:备库最近写入的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。
- replay_lsn:备库最近应用的WAL日志位置。
1.3.2 使用 WAL延迟时间衡量 监控主备同步数据延迟
SELECT pid, usename,client_addr,state,write_lag,flush_lag, replay_lag FROM pg_stat_replication;
说明：
- write_lag :主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流还没写入备库 WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。
- flush_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，但还没有应用WAL日志）并返回确认信息的时间。
- replay_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，并且已应用WAL日志）并返回确认信息的时间。
write_lag、flush_lag、replay_lag 为 PostgreSQL10版本新增字段，10版本前pg_statreplication视图不提供这三个字段，但是也有办法监控主备延时，在流复制备库执行以下SQL：
SELECT EXTRACT(SECOND FROM now ()- pg_last_xact_replay_timestamp());
说明：
pg_last_xact_replay_timestamp函数显示备库最近WAL日志应用时间，通过与当前时间比较可粗略计算主备库延时，这种方式的优点是即使主库宕掉，也可以大概判断主备延时。缺点是如果主库上只有读操作，主库不会发送WAL日志流到备库，pg_last_xact_replay_timestamp 函数返回的结果就是一个静态的时间，这个公式的判断结果就不严谨了。
1.3.3 通过创建主备延时测算表方式
这种方法在主库上创建一张主备延时测算表，并定时往表插入数据或更新数据，之后在备库上计算这条记录的插入时间或更新时间与当前时间的差异来判断主备延时，这种方法不是很严谨，但很实用，当主库宕机时，这种方式依然可以大概判断出主备延时。
2、通过 pg_controldata 命令获取两个pg实例的时间线来判定两边数据是否一致
$ pg_controldata | grep TimeLineID 时间线检查
说明：
该命令需要在主备机的 pg 实例上均运行，获取各自的时间线进行比对。
二、统计数据库大小
1、查询所有库数据大小
postgres=# SELECT pg_database.datname as "Database",
postgres-# pg_size_pretty(pg_database_size(pg_database.datname)) as "Size"
postgres-# FROM pg_database
postgres-# ORDER BY pg_database_size(pg_database.datname) DESC;
Database | Size
------------------+---------
postgres | 65 GB
lm_lmlicensedb | 14 MB
svm_svmdb | 11 MB
lsm_casdb | 7933 kB
mps_mpsdb | 7773 kB
isecure_portaldb | 7741 kB
template0 | 7601 kB
template1 | 7601 kB
(8 rows)
说明：
- pg_size_pretty：以人性化角度展示数据库大小
2、查询所有数据库总大小
SELECT pg_size_pretty(sum(pg_database_size(pg_database.datname))) as "Size" FROM pg_database
3、查询所有表总大小（也就相当于查询所有数据库总大小）
postgres=# select
postgres-# pg_size_pretty(sum(t.size))
postgres-# from (
postgres(# SELECT
postgres(# table_schema || '.' || table_name AS table_full_name
postgres(# , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
postgres(# FROM information_schema.tables
postgres(# ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
postgres(# ) t;
pg_size_pretty
----------------
65 GB
(1 row)
参考资料：
PostgreSQL实战之物理复制和逻辑复制（四）_postgres write_lsn 不动-CSDN博客
标签：WAL,备库,Postgres,lsn,数据库,命令,pg,日志,postgres From： https://www.cnblogs.com/sandyflower/p/18124045