查询各个时间段内PostgreSQL产生的wal情况

以前做oracle数据库的时候，经常会使用一条SQL语句查询数据库的归档产生量，PostgreSQL与oracle的redo不同的一点在于，pg使用的wal（pg9之前为xlog）数量并不是固定的，会根据数据库内参数的设置发生动态变化。因此通常可以通过统计wal在各个时间段内产生的数量确认数据库繁忙程度。

本文提供的SQL语句因涉及访问操作系统文件，普通用户没有权限，需要使用数据库的管理员用户。

1、pg10及以后的版本可以通过pg_ls_waldir查询，不区分操作系统平台。

select to_char(date_trunc('day',wal.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',wal.modification) >=0 and date_part('hour',wal.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',wal.modification) >=0 and date_part('hour',wal.modification) <1 then 1 else 0 end) as w0_1,
        sum(case when date_part('hour',wal.modification) >=1 and date_part('hour',wal.modification) <2 then 1 else 0 end) as w1_2,
        sum(case when date_part('hour',wal.modification) >=2 and date_part('hour',wal.modification) <3 then 1 else 0 end) as w2_3,
        sum(case when date_part('hour',wal.modification) >=3 and date_part('hour',wal.modification) <4 then 1 else 0 end) as w3_4,
        sum(case when date_part('hour',wal.modification) >=4 and date_part('hour',wal.modification) <5 then 1 else 0 end) as w4_5,
        sum(case when date_part('hour',wal.modification) >=5 and date_part('hour',wal.modification) <6 then 1 else 0 end) as w5_6,
        sum(case when date_part('hour',wal.modification) >=6 and date_part('hour',wal.modification) <7 then 1 else 0 end) as w6_7,
        sum(case when date_part('hour',wal.modification) >=7 and date_part('hour',wal.modification) <8 then 1 else 0 end) as w7_8,
        sum(case when date_part('hour',wal.modification) >=8 and date_part('hour',wal.modification) <9 then 1 else 0 end) as w8_9,
        sum(case when date_part('hour',wal.modification) >=9 and date_part('hour',wal.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',wal.modification) >=10 and date_part('hour',wal.modification) <11 then 1 else 0 end) as w10_11,
        sum(case when date_part('hour',wal.modification) >=11 and date_part('hour',wal.modification) <12 then 1 else 0 end) as w11_12,
        sum(case when date_part('hour',wal.modification) >=12 and date_part('hour',wal.modification) <13 then 1 else 0 end) as w12_13,
        sum(case when date_part('hour',wal.modification) >=13 and date_part('hour',wal.modification) <14 then 1 else 0 end) as w13_14,
        sum(case when date_part('hour',wal.modification) >=14 and date_part('hour',wal.modification) <15 then 1 else 0 end) as w14_15,
        sum(case when date_part('hour',wal.modification) >=15 and date_part('hour',wal.modification) <16 then 1 else 0 end) as w15_16,
        sum(case when date_part('hour',wal.modification) >=16 and date_part('hour',wal.modification) <17 then 1 else 0 end) as w16_17,
        sum(case when date_part('hour',wal.modification) >=17 and date_part('hour',wal.modification) <18 then 1 else 0 end) as w17_18,
        sum(case when date_part('hour',wal.modification) >=18 and date_part('hour',wal.modification) <19 then 1 else 0 end) as w18_19,
        sum(case when date_part('hour',wal.modification) >=19 and date_part('hour',wal.modification) <20 then 1 else 0 end) as w19_20,
        sum(case when date_part('hour',wal.modification) >=20 and date_part('hour',wal.modification) <21 then 1 else 0 end) as w20_21,
        sum(case when date_part('hour',wal.modification) >=21 and date_part('hour',wal.modification) <22 then 1 else 0 end) as w21_22,
        sum(case when date_part('hour',wal.modification) >=22 and date_part('hour',wal.modification) <23 then 1 else 0 end) as w22_23,
        sum(case when date_part('hour',wal.modification) >=23 and date_part('hour',wal.modification) <24 then 1 else 0 end) as w23_24
from (select * from  pg_ls_waldir()) wal
where wal.name not in ('archive_status')
  and wal.name not like '%.backup'
group by to_char(date_trunc('day',wal.modification),'yyyymmdd')
order by to_char(date_trunc('day',wal.modification),'yyyymmdd') desc;

2、pg9.6及之前的版本，在Linux下使用如下语句查询

with tmp_file as (
    select t1.file,
           t1.file_ls,
           (pg_stat_file(t1.file)).modification as modification,
           round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
      from (select dir||'/'||pg_ls_dir(t0.dir) as file,
                   pg_ls_dir(t0.dir) as file_ls
              from (select 'pg_xlog'::text as dir) t0
            ) t1
)
select to_char(date_trunc('day',tf0.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <1 then 1 else 0 end) as w0_01,
        sum(case when date_part('hour',tf0.modification) >=1 and date_part('hour',tf0.modification) <2 then 1 else 0 end) as w1_02,
        sum(case when date_part('hour',tf0.modification) >=2 and date_part('hour',tf0.modification) <3 then 1 else 0 end) as w2_03,
        sum(case when date_part('hour',tf0.modification) >=3 and date_part('hour',tf0.modification) <4 then 1 else 0 end) as w3_04,
        sum(case when date_part('hour',tf0.modification) >=4 and date_part('hour',tf0.modification) <5 then 1 else 0 end) as w4_05,
        sum(case when date_part('hour',tf0.modification) >=5 and date_part('hour',tf0.modification) <6 then 1 else 0 end) as w5_06,
        sum(case when date_part('hour',tf0.modification) >=6 and date_part('hour',tf0.modification) <7 then 1 else 0 end) as w6_07,
        sum(case when date_part('hour',tf0.modification) >=7 and date_part('hour',tf0.modification) <8 then 1 else 0 end) as w7_08,
        sum(case when date_part('hour',tf0.modification) >=8 and date_part('hour',tf0.modification) <9 then 1 else 0 end) as w8_09,
        sum(case when date_part('hour',tf0.modification) >=9 and date_part('hour',tf0.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',tf0.modification) >=10 and date_part('hour',tf0.modification) <11 then 1 else 0 end) as wal_10_11,
        sum(case when date_part('hour',tf0.modification) >=11 and date_part('hour',tf0.modification) <12 then 1 else 0 end) as wal_11_12,
        sum(case when date_part('hour',tf0.modification) >=12 and date_part('hour',tf0.modification) <13 then 1 else 0 end) as wal_12_13,
        sum(case when date_part('hour',tf0.modification) >=13 and date_part('hour',tf0.modification) <14 then 1 else 0 end) as wal_13_14,
        sum(case when date_part('hour',tf0.modification) >=14 and date_part('hour',tf0.modification) <15 then 1 else 0 end) as wal_14_15,
        sum(case when date_part('hour',tf0.modification) >=15 and date_part('hour',tf0.modification) <16 then 1 else 0 end) as wal_15_16,
        sum(case when date_part('hour',tf0.modification) >=16 and date_part('hour',tf0.modification) <17 then 1 else 0 end) as wal_16_17,
        sum(case when date_part('hour',tf0.modification) >=17 and date_part('hour',tf0.modification) <18 then 1 else 0 end) as wal_17_18,
        sum(case when date_part('hour',tf0.modification) >=18 and date_part('hour',tf0.modification) <19 then 1 else 0 end) as wal_18_19,
        sum(case when date_part('hour',tf0.modification) >=19 and date_part('hour',tf0.modification) <20 then 1 else 0 end) as wal_19_20,
        sum(case when date_part('hour',tf0.modification) >=20 and date_part('hour',tf0.modification) <21 then 1 else 0 end) as wal_20_21,
        sum(case when date_part('hour',tf0.modification) >=21 and date_part('hour',tf0.modification) <22 then 1 else 0 end) as wal_21_22,
        sum(case when date_part('hour',tf0.modification) >=22 and date_part('hour',tf0.modification) <23 then 1 else 0 end) as wal_22_23,
        sum(case when date_part('hour',tf0.modification) >=23 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as wal_23_24
from tmp_file tf0
where tf0.file_ls not in ('archive_status')
	and tf0.file_ls not like '%.backup'
group by to_char(date_trunc('day',tf0.modification),'yyyymmdd')
order by to_char(date_trunc('day',tf0.modification),'yyyymmdd') desc;

3、pg9.6及之前版本，在windows环境下，使用如下语句查询，与Linux下的仅有“\”和“/”区别。

with tmp_file as (
    select t1.file,
           t1.file_ls,
           (pg_stat_file(t1.file)).modification as modification,
           round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
      from (select dir||'\'||pg_ls_dir(t0.dir) as file,
                   pg_ls_dir(t0.dir) as file_ls
              from (select 'pg_xlog'::text as dir) t0
            ) t1
)
select to_char(date_trunc('day',tf0.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <1 then 1 else 0 end) as w0_01,
        sum(case when date_part('hour',tf0.modification) >=1 and date_part('hour',tf0.modification) <2 then 1 else 0 end) as w1_02,
        sum(case when date_part('hour',tf0.modification) >=2 and date_part('hour',tf0.modification) <3 then 1 else 0 end) as w2_03,
        sum(case when date_part('hour',tf0.modification) >=3 and date_part('hour',tf0.modification) <4 then 1 else 0 end) as w3_04,
        sum(case when date_part('hour',tf0.modification) >=4 and date_part('hour',tf0.modification) <5 then 1 else 0 end) as w4_05,
        sum(case when date_part('hour',tf0.modification) >=5 and date_part('hour',tf0.modification) <6 then 1 else 0 end) as w5_06,
        sum(case when date_part('hour',tf0.modification) >=6 and date_part('hour',tf0.modification) <7 then 1 else 0 end) as w6_07,
        sum(case when date_part('hour',tf0.modification) >=7 and date_part('hour',tf0.modification) <8 then 1 else 0 end) as w7_08,
        sum(case when date_part('hour',tf0.modification) >=8 and date_part('hour',tf0.modification) <9 then 1 else 0 end) as w8_09,
        sum(case when date_part('hour',tf0.modification) >=9 and date_part('hour',tf0.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',tf0.modification) >=10 and date_part('hour',tf0.modification) <11 then 1 else 0 end) as wal_10_11,
        sum(case when date_part('hour',tf0.modification) >=11 and date_part('hour',tf0.modification) <12 then 1 else 0 end) as wal_11_12,
        sum(case when date_part('hour',tf0.modification) >=12 and date_part('hour',tf0.modification) <13 then 1 else 0 end) as wal_12_13,
        sum(case when date_part('hour',tf0.modification) >=13 and date_part('hour',tf0.modification) <14 then 1 else 0 end) as wal_13_14,
        sum(case when date_part('hour',tf0.modification) >=14 and date_part('hour',tf0.modification) <15 then 1 else 0 end) as wal_14_15,
        sum(case when date_part('hour',tf0.modification) >=15 and date_part('hour',tf0.modification) <16 then 1 else 0 end) as wal_15_16,
        sum(case when date_part('hour',tf0.modification) >=16 and date_part('hour',tf0.modification) <17 then 1 else 0 end) as wal_16_17,
        sum(case when date_part('hour',tf0.modification) >=17 and date_part('hour',tf0.modification) <18 then 1 else 0 end) as wal_17_18,
        sum(case when date_part('hour',tf0.modification) >=18 and date_part('hour',tf0.modification) <19 then 1 else 0 end) as wal_18_19,
        sum(case when date_part('hour',tf0.modification) >=19 and date_part('hour',tf0.modification) <20 then 1 else 0 end) as wal_19_20,
        sum(case when date_part('hour',tf0.modification) >=20 and date_part('hour',tf0.modification) <21 then 1 else 0 end) as wal_20_21,
        sum(case when date_part('hour',tf0.modification) >=21 and date_part('hour',tf0.modification) <22 then 1 else 0 end) as wal_21_22,
        sum(case when date_part('hour',tf0.modification) >=22 and date_part('hour',tf0.modification) <23 then 1 else 0 end) as wal_22_23,
        sum(case when date_part('hour',tf0.modification) >=23 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as wal_23_24
from tmp_file tf0
where tf0.file_ls not in ('archive_status')
	and tf0.file_ls not like '%.backup'
group by to_char(date_trunc('day',tf0.modification),'yyyymmdd')
order by to_char(date_trunc('day',tf0.modification),'yyyymmdd') desc;

以上语句查询结果均如下图所示，第一列为时间，具体到天，第二列为当天产生的总数。第三列之后，为每个时间段产生的日志数量。如w0_1列表示，当天0点到1点产生的wal数量