环境
centos7.9,postgresql-14 由于上篇的物化视图是手动刷新数据,sh本着程序员都是懒人的原则,不做成自动刷新是对不起自己的职业
步骤
- 建物化视图
CREATE MATERIALIZED VIEW view_xxx AS
( SELECT *
FROM table
)
WITH DATA;
WITH DATA 指刷新时可查询视图 WITH NO DATA 指刷新时不可查询视图
- 手动刷新命令
REFRESH MATERIALIZED VIEW concurrently view_xxx;
concurrently不锁表刷新数据 如果执行时报 HINT: 在物化视图的一个或多个列上创建不带WHERE子句的唯一索引. 建一个唯一索引
create unique index idx_id on view_xxxx(id);
- cron触发
提前装好sshpass
yum install sshpass -y
cron -e
sshpass -p "password" psql -U postgres -d db_name -c 'REFRESH MATERIALIZED VIEW concurrently view_xxxxx';
sshpass格式是sshpass -p "your_password" your_command
标签:concurrently,postgres,视图,物化,sshpass,刷新,view From: https://blog.51cto.com/bomby/7052425