How to run database maintenance utilities in postgres operator

[eshikhov]

How to properly run database maintenance utilities such as vacuumlo, reindexdb, and others on a schedule with your operator?

[FxKu]

I guess, you can create a K8s cron jobs to do so. You can also use pg_cron inside the databse to run some SQL code for regular maintenance. pg_cron is already installed in the postgres database of the Spilo container. Functions are allowed to be called by the cron_admin role. The Postgres Operator will grant cron_admin to all database owner roles so that scheduling cron jobs can be part of migration scripts. There is one minor change though, by default cron.schedule function creates cron job which will be executed only in postgres database. To overcome this limitation we provide another special function, with three arguments:

SELECT cron.schedule_in_database('* * * * *', 'my_database', 'SELECT 1');

Note that the migration script must connect to the postgres database to register the cron jobs because pg_cron is installed there and not in the application database. There you would also inspect existing cron jobs (cron.job table) and details about past job runs (cron.job_run_details table).

[eshikhov]

pg_cron is a good thing and we also use it, but as far as I know it can only execute database queries, here you need to run a script that is located in the system folder with the postgres database Paths in the statement /usr/bin/vacuumlo /usr/lib/postgresql/10/bin/vacuumlo /usr/lib/postgresql/11/bin/vacuumlo /usr/lib/postgresql/12/bin/vacuumlo /usr/lib/postgresql/13/bin/vacuumlo /usr/lib/postgresql/14/bin/vacuumlo /usr/lib/postgresql/9.6/bin/vacuumlo