利用pgAgnet实现定时刷新物化视图

场景

　　之前的timescaledb方案实现后，发现只对一些视图优化明显，大部分视图（与时间无关的）没有优化甚至更耗时。于是提出第二种方案，利用pgAgent和物化视图来优化。

　　我的版本：pg：10.19，pgAgnet:10

安装pgagent：

　　注意，pgagent在9.0之前，是pgadmin下的包。之后需要另外安装。这里推荐用yum安装，源码编译安装，我遇到以下问题，并且没有解决。

make: *** 没有规则可以创建目标“/usr/pgsql-10/lib/pgxs/src/makefiles/pgxs.mk

　　先查询pgAgent，看那个版本对应pg的版本：

yum search pgagent

　　

 

 

　　然后根据版本安装：

yum install pgagent_10.x86_64

　　执行以下语句：

psql -U postgres -d postgres -h IP地址 -p 5432 -f /usr/share/pgagent_10-4.0.0/pgagent.sql -W

　　一般安装后的pgagent放在/usr/share下。这句话表示创建pg下的pgagent模式，也可以直接输入指令来创建。

　　最后启动pgagent：

pgagent_10 hostaddr=127.0.0.1 dbname=postgres user=postgres

　　启动后就可以在pgadmin看到了：

 

 

　　其实也可以不安装pgadmin，直接安装pgAgent。只不过没有可视化界面，job的创建需要写sql来实现。

创建job：

　　

 

 　　job的编辑，则是：

 

 　　配置常规信息：

 

 　　这里的Hostagent最好配置一下，不然可能报：关系不存在的错。

　　每项的含义可以看问号的图标，有解释。job class一般不变，comment是注释。

　　第二步：

　　

 

 　　点击加号，可以创建一个job要执行的任务，点击编辑，进入：

 

 　　这里的连接字符串最好填一下。连接类型如果是local，可能会出现任务不执行的情况。然后code中写要执行的语句，这里我们刷新对应的物化视图：

 

 　　这里的刷新语句，一般为

REFRESH MATERIALIZED VIEW VMProductionMade with data;

　　这个时候有锁，用户不能在刷新时访问它。可以创建一个唯一的列的索引来支持并发刷新，使用：

CREATE UNIQUE INDEX idx_stime ON VMProductionMade (stime);

REFRESH MATERIALIZED VIEW CONCURRENTLY VMProductionMade with data;

　　然后是配置定时计划：

　　

 

 　　可以填写开始，结束时间，是否启用。在repeat中，选择重复周期：

 

 　　其实不选就是默认select all的。

　　exception是用来排除一些时间段，不执行该计划的。

 

 　　可以看每个时间点任务执行的结果：

 

 ·　　看一下结果：物化视图这个查询耗时：58ms，原视图耗时：60s以上

参考：

　　https://blog.csdn.net/weixin_43783011/article/details/110423729

　　https://www.likecs.com/show-204179550.html

　　http://t.zoukankan.com/wggj-p-9929184.html

　　https://www.modb.pro/db/102888