How can we schedule auto vacuum between specific times, such as 10 PM and 6 AM?

Question

How can we schedule auto vacuum in Azure Database for PostgreSQL flexible server in between specific times, such as 10 PM and 6 AM?

Answer 1

Hi Naveen Kumar Kakarla (Quadrant Resource),

Welcome to Micrsoft Q&A forum.

As I understand, you want to schedule auto vacuum between specific times, such as 10 PM and 6 AM.

As mentioned in the above answer by Dillon, make use of pg_cron extension to achieve the same.

The commands would look like below:

SELECT cron.schedule('0 10 * * *', 'VACUUM');

SELECT cron.schedule('0 18 * * *', 'VACUUM');

Reference: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#pg_cron

Let us know for further queries.

Thanks

Answer 2

Hi Naveen,

Have a look at using pg_cron extension to schedule this:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#pg_cron

pg_cron is a simple, cron-based job scheduler for PostgreSQL that runs inside the database as an extension. The pg_cron extension can be used to run scheduled maintenance tasks within a PostgreSQL database. For example, you can run periodic vacuum of a table or removing old data jobs.

Some examples:

To run vacuum every day at 10:00am (GMT) in default database 'postgres'

SELECT cron.schedule('0 10 * * *', 'VACUUM');

If this is helpful please accept answer.