refresh materialized view periodically postgres

Question

for optimization purposes I'm using a materialized view, to refresh it periodically I have set a cron job that runs each period t in my case every three hours. my questions are:

1. what's the best way to refresh a materialized view?
2. what can go wrong if using a cron job to refresh a materialized view?

I have come across a postgres plugin that schedule jobs link

Answer 1

the best way is to execute periodically a script that does the task:

the script is:

#!/bin/sh
psql -U user_name -d database_instance_name -c 'refresh materialized view view_name'

and add an entry in the crontab like:

@hourly  /full_path/script_name.sh

Answer 2

Also pg_cron extension can do job for Linux installation. Here is a example that i use, refreshing every 5 minute;

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('*/5 * * * *', 'REFRESH MATERIALIZED VIEW public.mv_anyquery', 
'127.0.0.1', 5432, 'geodb', 'postgres');

nodename(127.0.0.1) may be necessary trust authentication in pg_hba.conf.