查询瀚高数据库产生的归档量

原创于 2019-11-14 10:00:51 发布 · 532 阅读

CC 4.0 BY-SA版权

文章标签：

139 篇文章

订阅专栏

本文介绍了一种查询瀚高数据库归档文件数量及大小的方法，通过SQL语句统计不同时间段内的归档文件数，帮助理解数据库活动情况。

文档用途

查询瀚高数据库产生的归档量

详细信息

使用如下语句可查询瀚高数据库产生的归档数量。

with tmp_file as (

select t1.file,

t1.file_ls,

(pg_stat_file(t1.file)).modification as last_update_time,

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 'D:\highgo\database\4.3.2\data\pg_xlog'::text as dir --需要修改这个物理路径

) t0

) t1

where 1=1

order by (pg_stat_file(file)).modification desc

)

select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id,

sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all,

sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01,

sum(case when date_part('hour',tf0.last_update_time) >=1 and date_part('hour',tf0.last_update_time) <2 then 1 else 0 end) as wal_num_01_02,

sum(case when date_part('hour',tf0.last_update_time) >=2 and date_part('hour',tf0.last_update_time) <3 then 1 else 0 end) as wal_num_02_03,

sum(case when date_part('hour',tf0.last_update_time) >=3 and date_part('hour',tf0.last_update_time) <4 then 1 else 0 end) as wal_num_03_04,

sum(case when date_part('hour',tf0.last_update_time) >=4 and date_part('hour',tf0.last_update_time) <5 then 1 else 0 end) as wal_num_04_05,

sum(case when date_part('hour',tf0.last_update_time) >=5 and date_part('hour',tf0.last_update_time) <6 then 1 else 0 end) as wal_num_05_06,

sum(case when date_part('hour',tf0.last_update_time) >=6 and date_part('hour',tf0.last_update_time) <7 then 1 else 0 end) as wal_num_06_07,

sum(case when date_part('hour',tf0.last_update_time) >=7 and date_part('hour',tf0.last_update_time) <8 then 1 else 0 end) as wal_num_07_08,

sum(case when date_part('hour',tf0.last_update_time) >=8 and date_part('hour',tf0.last_update_time) <9 then 1 else 0 end) as wal_num_08_09,

sum(case when date_part('hour',tf0.last_update_time) >=9 and date_part('hour',tf0.last_update_time) <10 then 1 else 0 end) as wal_num_09_10,

sum(case when date_part('hour',tf0.last_update_time) >=10 and date_part('hour',tf0.last_update_time) <11 then 1 else 0 end) as wal_num_10_11,

sum(case when date_part('hour',tf0.last_update_time) >=11 and date_part('hour',tf0.last_update_time) <12 then 1 else 0 end) as wal_num_11_12,

sum(case when date_part('hour',tf0.last_update_time) >=12 and date_part('hour',tf0.last_update_time) <13 then 1 else 0 end) as wal_num_12_13,

sum(case when date_part('hour',tf0.last_update_time) >=13 and date_part('hour',tf0.last_update_time) <

更多详细信息请登录【瀚高技术支持平台】查看瀚高技术支持平台