Nightly DB maintenance
I'm at the point where I need to start thinking about how the nightly update/refresh is going to work.
Each night the DB needs to update the data tables with any vetted rows from the edit schema and refresh the materialized views.
A good SO post suggests a few things:
1) cron - */30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"
2) trigger
3) listen/notify
Of those, only cron is practical as we will do maintenance on a schedule, not based on events.
Downside to cron is that it doesn't know if there have been problems.
As this will be a 2-step process (update data schema / refresh materialized views) our choice with cron is:
1) Ignore failures. If the failure is on the updates any refresh might be wasted.
But, a refresh doesn't take long (90 seconds-ish) so wasting cycles isn't a big deal.
If the failure is on the refresh how do we know? Would we need to do an audit to find discrepancies?
2) Write some code that runs before the view refresh that determines if errors occured during update and takes action (e.g. email and bail)
I also found pg_cron, which might be more elegant. Downside is that it needs to be installed
Note: if cron/psql is the answer, these psql flags will be useful:
-d database name
-c command
-b echo errors to standard or error output
-f filename (i.e. an sql file that contains the commands)
-L log file name
-q quiet