每晚执行的PostgreSQL脚本

Question

我正在尝试模拟基于物化视图的快照，参考了valena.com上的这篇文章并创建了所需的物化视图。

接下来我的任务是在PostgreSQL中每晚执行刷新物化视图脚本。我正在使用pgAdmin，并发现我需要在数据库服务器(Linux)上安装pgagent，在pgAdmin中编写pgscript创建作业。

这是我需要的吗？还是有更好的方法可以每晚运行此脚本？

 for all i in tables that begin with name 'mview_%'
   SELECT refresh_matview(i);
 end loop;

- cableload

Answer 1

我刚刚在我的crontab中添加了一个条目：

*/3 * * * * /scripts/matviewsRefresh.sh 

这将每隔三分钟调用脚本，您可以进行调整。 而在 matviewsRefresh.sh 文件中：

  echo 'select matview_refresh_all();' | su - postgres -c "psql MYDBNAME"

当然，matview_refresh_all是一个 pl/pgsql 函数，循环刷新所有的物化视图（我添加了一张辅助表记录每个视图上次刷新的时间，每个视图有不同的刷新频率）。

- leonbloy

---

谢谢leonbloy，这正是我要找的简单方法。非常感谢您的帮助。 - cableload

Answer 2

定时任务

就像 @leonbloy 所说的。

此外，您还可以将定时任务放置在您的Postgres系统用户的crontab中，并简化调用：

psql mydb -c 'select maint.f_mv_update()' 

自动刷新物化视图

本文旨在介绍一种可以在非工作时间稍微锁定一下表格的环境中使用的方法。如果您没有这样的条件，您可能希望并行创建新表格，然后删除原始表格并将副本重命名以尽量减少阻塞。

我会为我所有 mv 规则中的对象保留一个单独的模式 maint。
下面是我的刷新所有物化视图的函数：

CREATE OR REPLACE FUNCTION maint.f_mv_update()
  RETURNS void AS
$func$
DECLARE
   _r    record;
BEGIN

SET LOCAL work_mem='256MB';               -- more memory for sorting et al?
SET LOCAL client_min_messages=warning;    -- suppress index creation notices

-- With concurrent load you may need to lock some tables to avoid deadlocks
-- LOCK tbl1, tbl2;

FOR _r IN
   -- cast to regclass asserts table name is valid
   SELECT (mv_schema || '.' || mv_tab)::regclass AS tbl 
         ,drop_index
         ,mv_query
         ,create_index
   FROM   maint.mv
   WHERE  active
   ORDER  BY mv_id
LOOP
   IF _r.drop_index IS NOT NULL THEN      -- drop indexes (for performance!)
      EXECUTE _r.drop_index;
   END IF;

   EXECUTE 'TRUNCATE TABLE '   || _r.tbl;
   EXECUTE 'INSERT INTO '      || _r.tbl || ' ' || _r.mv_query;

   IF _r.create_index IS NOT NULL THEN    -- recreate Indexes (also CLUSTER?)
      EXECUTE _r.create_index;
   END IF;

   EXECUTE 'ANALYZE ' || _r.tbl;          -- ANALYZE to refresh statistics
END LOOP;

RESET client_min_messages;

UPDATE maint.mv
SET last_up = localtimestamp(0) WHERE active; -- remember update

END
$func$ LANGUAGE plpgsql VOLATILE SET search_path=maint,pg_temp;

REVOKE ALL ON FUNCTION maint.f_mv_update() FROM public;

COMMENT ON FUNCTION maint.f_mv_update() IS 'Update materialized Views.
Uses table maint.mv';

与该表相结合，注册所有希望属于该方案的物化视图。

CREATE TABLE maint.mv
(
  mv_id integer PRIMARY KEY, -- surrogate primary key...
  active boolean NOT NULL DEFAULT true,
  last_up timestamp(0) NOT NULL DEFAULT '2000-1-1 0:0'::timestamp, -- last update
  log_up timestamp(0) NOT NULL DEFAULT now()::timestamp(0), -- last change of row
  mv_schema text NOT NULL, -- Schema of mv table
  mv_tab text NOT NULL, -- Name of mv table
  mv_query text NOT NULL, -- SQL-query to fill mv
  drop_index text, -- SQL to drop indexes before refill
  create_index text, -- SQL to recreate indexes after refill
  note text
);
REVOKE ALL ON TABLE maint.mv FROM public;

示例行：

INSERT INTO maint.mv
       (mv_id, mv_schema, mv_tab, mv_query, drop_index, create_index)
VALUES ( 17, 'mv', 'mytbl'
       ,'SELECT mytbl_id, count(*) FROM mytbl GROUP BY 1;'
       ,'DROP INDEX IF EXISTS mv.mytbl_mytbl_id_idx;'
       ,'CREATE INDEX mytbl_mytbl_id_idx ON mv.mytbl (my_tbl_id);');

呼叫：

SELECT maint.f_mv_update();

- Erwin Brandstetter

---

请注意，使用 TRUNCATE 比 DELETE 更快，但在并发性方面不够安全。我不确定这种方法是否可能导致某些并发事务看到一个空的物化视图。 - leonbloy

有没有办法保证每天执行语句，因为如果服务器在那个时间段内宕机，cron作业将不会再次运行？ - user158

有没有办法确保每天都执行语句，因为如果服务器在那个时间段内宕机，cron作业将不会再次运行？ - undefined