postgresql 扩展pg_cron,pg_stat_statements安装配置

一、概述

pg_cron是基于cron的作业调度插件，语法与常规cron相同，但它可以直接从数据库执行PostgreSQL命令。
pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP SQL。

二、下载介质

pg_cron下载地址https://github.com/citusdata/pg_cron
注意：pg_cron一定不要yum安装，安装后找不到pg_config！！！！
pg_stat_statements在编译程序的contrib/pg_stat_statments目录下

三、安装部署

1、pg_cron安装部署：

#解压源码包
unzip pg_cron-main.zip
#声明环境变量
export PATH=/opt/postgresql/bin:$PATH
#编译安装
make && make install

2、pg_stat_statements安装：

cd /opt/software/postgresql-15.3/contrib/pg_stat_statements
make && make install

四、配置postgresql.conf

shared_preload_libraries = ‘pg_cron,pg_stat_statements’
#在哪个数据库做定时任务，就配置成哪个数据库
cron.database_name = ‘test’
#开启跟踪I/O消耗时间
track_io_timing = on

五、pg_cron的使用

#创建扩展pg_cron
create extension pg_cron;
#查询已创建的定时任务
select * from cron.job;
#创建定时任务，语法与crontab一样
SELECT cron.schedule('*/3 * * * *', $$REFRESH MATERIALIZED VIEW v_wuhua;$$);

六、pg_stat_statements的使用

#创建扩展pg_stat_statements
create extension pg_stat_statements
#常用的统计sql参考

#最耗IO SQL，单次调用最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;  

#总最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;  

#最耗时 SQL，单次调用最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;  

#总最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;  


#最耗共享内存 SQL

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;  

#最耗临时空间 SQL

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

EOF