系统平台：
Linux x86-64 Red Hat Enterprise Linux 7
版本：
4.5
1.配置hg_job的相关参数
①
修改配置文件
vim /opt/HighGo4.5.6-see/data/postgresql.conf
|
修改前：
#shared_preload_libraries = 'hg_job' # (change requires restart)
修改后：
shared_preload_libraries = 'hg_job' # (change requires restart)
hg.job_database = 'mvdb' # (设定为创建定时任务的数据库名)
hg.job_queue_processes = 0 #并发数先设置为0
hg.job_queue_interval = 60 #扫描间隔为60秒 |
保存退出
重启数据库服务，pg_ctl restart
②在数据库的data目录下创建hg_job目录
mkdir /opt/HighGo4.5.6-see/data/hg_job
2.创建测试用户和测试库
|
①psql命令或者客户端连接数据库
psql -U sysdba -d highgo
②执行下面创建语句
create user mvuds with password 'xxxxxx';
create database mvdb owner mvuds;
③切换用户和数据库
\c mvdb mvuds
④创建测试表
create schema mvuds;
create table mvuds.t1(id int,sname text);
⑤创建物化视图
create materialized view mvuds.mv_t1 as select * from mvuds.t1;
⑥
创建免密文件，密码设置为实际的密码
如果pgpass文件中有内容，在最后空白行中添加
vim ~/.pgpass
#hostname:port:database:username:password
localhost:5866:mvdb:sysdba:xxxxxx
保存退出，然后修改文件权限：
chmod 0600 ~/.pgpass |
3.在测试库中创建hg_job扩展
|
①关闭三权
psql -U syssso -d mvdb
select set_secure_param('hg_sepofpowers','off');
\q
pg_ctl restart
②创建hg_job扩展
psql -U sysdba -d mvdb
create extension hg_job;
\q
③创建扩展成功后，打开三权
psql -U syssso -d mvdb
select set_secure_param('hg_sepofpowers','on');
\q
pg_ctl restart |
4.创建定时任务
①设置为每隔2分钟执行一次
|
psql -U sysdba -d mvdb
select hgjob.job_create(
$body$
do $do$
begin
refresh materialized view mvuds.mv_t1;
end;
$do$;
$body$
,$$now() + interval '2 minute'$$,now()); |
②配置并发数大于0的值
vim /opt/HighGo4.5.6-see/data/postgresql.conf
|
修改前：
hg.job_queue_processes = 0
修改后：
hg.job_queue_processes = 5 |
③重启数据库
pg_ctl restart
④查看创建的定时任务
|
mvdb=# select * from hgjob.job;
jobid | jobenabled | jobwhat | jobinterval | jobnextrun | jobstartrun
| joblastrun | jobuser | jobcount
-------+------------+----------------------------------------------------+-----------------------------+-------------------------------+---------------------------
----+-------------------------------+---------+----------
2 | t | +| now() + interval '2 minute' | 2022-01-12 15:09:38.431608+08 | 2022-01-12 15:07:03.982518
+08 | 2022-01-12 15:07:38.431608+08 | sysdba | 1
| | do $do$ +| | |
| | |
| | begin +| | |
| | |
| | refresh materialized view mvuds.mv_t1;+| | |
| | |
| | end; +| | |
| | |
| | $do$; +| | |
| | |
| | | | |
| | |
(1 行记录) |
5.测试
①测试数据准备
|
mvdb=> select * from mv_t1;
id | sname
----+-------
1 | zhao
2 | qian
3 | sun
4 | li
(4 行记录)
mvdb=> select * from t1;
id | sname
----+-------
1 | zhao
2 | qian
3 | sun
4 | li
(4 行记录) |
②t1表中插入2条记录
|
mvdb=> insert into t1 values(5,'zhou');
INSERT 0 1
mvdb=> insert into t1 values(6,'wu');
INSERT 0 1 |
③查询t1表和物化视图mv_t1中的数据
|
mvdb=> select * from t1;
id | sname
----+-------
1 | zhao
2 | qian
3 | sun
4 | li
5 | zhou
6 | wu
(6 行记录)
mvdb=> select * from mv_t1;
id | sname
----+-------
1 | zhao
2 | qian
3 | sun
4 | li
(4 行记录) |
④间隔2分钟之后，查看定时任务执行情况
|
mvdb=# select * from hgjob.job;
jobid | jobenabled | jobwhat | jobinterval | jobnextrun | jobstartrun
| joblastrun | jobuser | jobcount
-------+------------+----------------------------------------------------+-----------------------------+-------------------------------+---------------------------
----+-------------------------------+---------+----------
2 | t | +| now() + interval '2 minute' | 2022-01-12 15:11:38.606462+08 | 2022-01-12 15:07:03.982518
+08 | 2022-01-12 15:09:38.606462+08 | sysdba | 2
| | do $do$ +| | |
| | |
| | begin +| | |
| | |
| | refresh materialized view mvuds.mv_t1;+| | |
| | |
| | end; +| | |
| | |
| | $do$; +| | |
| | |
| | | | |
| | |
(1 行记录) |
⑤再次查询物化视图mv_t1的数据，已经刷新了
|
mvdb=> select * from mv_t1;
id | sname
----+-------
1 | zhao
2 | qian
3 | sun
4 | li
5 | zhou
6 | wu
(6 行记录) |
至此，结束。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69994931/viewspace-2861830/
，如需转载，请注明出处，否则将追究法律责任。
