postgresql 如何查看pg

当然如果你登录服务器所在主机，直接在$PGDAT/pg_wal下执行：

du -h --max-depth=1 ./ 

可以得到。

#du -h --max-depth=1 ./4.0K  ./Archive_status193M  ./

如果通过客户端怎么做呢？

答案：pg_ls_waldir()函数。pg_ls_waldir()是pg 10.0引入的函数，可以输出数据库WAL目录的所有文件。

postgres=# select sum(size) From pg_ls_waldir();
         sum  ----------- 201326592(1 row)

单位是byte，所以当前pg_wal的xLOG日志总大小为201326592/1024/1024=192M。

也可以使用：

postgres=# select count(*) from pg_ls_waldir();
     count -------  12(1 row)

12表示wal日志文件个数，总大小12*16=192M。

16表示单个wal日志文件大小，单位MB，WAL 日志文件大小默认为16MB。

bonus：

1、怎么调整单个wal日志文件大小？

答：使用 inITdb 调整WAL文件大小。

2、pg_ls_logdir() 也是pg10.0版本引入的函数，输出数据库日志目录的所有文件。

postgres=# select * from pg_ls_logdir();
            name        | size  |   modification   ----------------------------------+---------+------------------------ postgreSQL-2020-04-28_092020.log | 2277343 | 2020-04-29 11:34:56+08 postgresql-2020-04-28_092020.csv | 140050 | 2020-04-29 11:34:56+08

3、如何列出/data文件夹中的文件？

答：pg_ls_dir

postgres=# select pg_ls_dir('/data');
       pg_ls_dir    ----------------------

补充：postgresql 查看wal生成频率和大小

–wal 文件生成数量

–linux ls --full-time stat filename

–pg_stat_file返回一个记录，其中包含

– 1 size 文件尺寸

– 2 access 最后访问时间戳(linux:最近访问) 、

– 3 modification 最后修改时间戳(linux:最近更改–) 、

– 4 change 最后文件状态改变时间戳（只支持 Unix 平台）(linux:最近改动) 、

– 5 creation 文件创建时间戳（只支持 Windows）

– 6 isdir 一个boolean指示它是否为目录 isdir

– select * from pg_stat_file('/VAR/lib/postgresql/9.1/main/pg_xlog/0000000200000BBB000000A9');
    – /var/lib/postgresql/9.1/main/pg_xlog– /var/log/postgresql– /mnt/nas_dbbackup/archivelog

with tmp_file as (  select t1.file,      t1.file_ls,      (pg_stat_file(t1.file)).size as size,      (pg_stat_file(t1.file)).access as access,      (pg_stat_file(t1.file)).modification as last_update_time,      (pg_stat_file(t1.file)).change as change,      (pg_stat_file(t1.file)).creation as creation,      (pg_stat_file(t1.file)).isdir as isdir   from (select dir||'/'||pg_ls_dir(t0.dir) as file,          pg_ls_dir(t0.dir) as file_ls       from ( select '/var/lib/postgresql/9.1/main/pg_xlog'::text as dir           --需要修改这个物理路径           --select '/mnt/nas_dbbackup/archivelog'::text as dir           --select setting as dir from pg_settings where name='log_directory'          ) 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) 14 then 1 else 0 end) as wal_num_13_14,    sum(case when date_part('hour',tf0.last_update_time) >
    =14 and date_part('hour',tf0.last_update_time) 15 then 1 else 0 end) as wal_num_14_15,    sum(case when date_part('hour',tf0.last_update_time) >
    =15 and date_part('hour',tf0.last_update_time) 16 then 1 else 0 end) as wal_num_15_16,    sum(case when date_part('hour',tf0.last_update_time) >
    =16 and date_part('hour',tf0.last_update_time) 17 then 1 else 0 end) as wal_num_16_17,    sum(case when date_part('hour',tf0.last_update_time) >
    =17 and date_part('hour',tf0.last_update_time) 18 then 1 else 0 end) as wal_num_17_18,    sum(case when date_part('hour',tf0.last_update_time) >
    =18 and date_part('hour',tf0.last_update_time) 19 then 1 else 0 end) as wal_num_18_19,    sum(case when date_part('hour',tf0.last_update_time) >
    =19 and date_part('hour',tf0.last_update_time) 20 then 1 else 0 end) as wal_num_19_20,    sum(case when date_part('hour',tf0.last_update_time) >
    =20 and date_part('hour',tf0.last_update_time) 21 then 1 else 0 end) as wal_num_20_21,    sum(case when date_part('hour',tf0.last_update_time) >
    =21 and date_part('hour',tf0.last_update_time) 22 then 1 else 0 end) as wal_num_21_22,    sum(case when date_part('hour',tf0.last_update_time) >
    =22 and date_part('hour',tf0.last_update_time) 23 then 1 else 0 end) as wal_num_22_23,     sum(case when date_part('hour',tf0.last_update_time) >
    =23 and date_part('hour',tf0.last_update_time) 24 then 1 else 0 end) as wal_num_23_24from tmp_file tf0where 1=1 and tf0.file_ls not in ('archive_status')group by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd')order by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') desc;
     

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• postgresql 利用xlog进行热备操作
• Postgresql中xlog生成和清理逻辑操作
• Postgresql去重函数distinct的用法说明
• postgresql 12版本搭建及主备部署操作
• 开源数据库postgreSQL13在麒麟v10sp1源码安装过程详解
• postgresql初始化之initdb的使用详解
• postgresql之使用lsn 获取 wal文件名的实例
• PostgreSQL 实现定时job执行(pgAgent)

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！