Dokku + Postgres : How to refresh a materialized view?

Question

I am using Dokku with dokku-postgres plugin

I am looking for a way to refresh my materialized views in a cron job. I know that I can do dokku postgres:connect mydbinstance to connect to the DB and then issue a REFRESH MATERIALIZED VIEW mymaterializedviewname to have my view refreshed.

But I am looking for a single command (cron). I could see here that the plugin doesn't seem to support issuing such a command straight away.

How could I achieve this ?

score 2 · Accepted Answer · edited May 23 '17 at 10:29

I couldn't find a command to do so. I ended up calling a shell script in cron, that triggers a psql function.

crontab :

0 0 * * * /home/scripts/refreshMaterializedViews.sh

refreshMaterializedViews.sh :

echo 'select RefreshAllMaterializedViews();' | dokku postgres:connect my-postgres-db

Where RefreshAllMaterializedViews() is a psql function to refresh all materialized views at once. I'll add it here for simplicity, but it's already all over S.O.

Psql function :

CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(schema_arg TEXT DEFAULT 'public')
RETURNS INT AS $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
    FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = schema_arg 
    LOOP
        RAISE NOTICE 'Refreshing %.%', schema_arg, r.matviewname;
        EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.matviewname; 
    END LOOP;

    RETURN 1;
END 
$$ LANGUAGE plpgsql;

Note that this function doesn't support 'concurrently'. Which means it will lock your Materialized views during the refresh. For other solutions, please refer to this S.O question.

Dokku + Postgres : How to refresh a materialized view?

1 Answers1