PostgreSQL script execution every night

Question

I am trying to mimic snapshot materialized view based on this article on valena.com and have created the materialized views that I need.

My next task is to execute the refresh materialized view scripts on a nightly basis in PostgreSQL. I am using pgAdmin and found out that I need to install pgagent on my database server (Linux) and create jobs in pgAdmin by writing pgscript.

Is that what I need, or is there a better way to run this script on a nightly basis?

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

Answer 1

I just placed an entry in my crontab:

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

This calls the script every three minutes, you can tune that. And inside matviewsRefresh.sh:

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

Of course, matview_refresh_all is a pl/pgsql function that loops over all my materialized views and refresh the old ones (I added an auxiliary table that records the time of last refresh for each mview, and each one has a different refresh frequency)

Answer 2

Cron job

What @leonbloy said.

Plus, you can also place the cronjob in the crontab of your postgres system user and simplify the call:

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

Refreshing materialized views automatically

This is intended for an environment, where you can afford to lock tables for a bit at off hours. If you don't have that luxury, you might want to create the new tables in parallel and then drop the original and rename the copy to keep blocking to a minimum.

I keep a separate schema maint for all objects in my mv regime.
My function to refresh them all:

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';

In combination with this table, where all materialized views are registered that want to belong to this regime.

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;

Example row:

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);');

Call:

SELECT maint.f_mv_update();