refresh materialized view periodically postgres
the best way is to execute periodically a script that does the task:
the script is:
#!/bin/shpsql -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
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.