'Is there way to use pg_cron from the google cloud sql?

Currently I need to move my database from postgreSql to google cloud sql. I use pg_cron to remove stale records like this:

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

I've read folowing article: https://cloud.google.com/sql/docs/postgres/extensions

And was not found anything related to pg_cron

Also I've read Does Cloud SQL Postgres have any cron-like tool? but it looks like overengineering for my task.

Is there a simpler way?

javapostgresqlgoogle-cloud-sqlspring-cloud-gcp


Solution 1:[1]

Unfortunately pg_cron is not supported by Cloud SQL. In order to be able to run this extension you need to do it as SUPERUSER. As mentioned in the post you found Cloud SQL is a fully-managed service. This means that while some operations such as seting up, maintain, managing and administering your databases are managed easily, you don't have the SUPERUSER privilege.

There is an open Feature Request regarding this improvement but there is no estimated time of arrival for this.

If the workaround given in the same post is not suitable for you, you can always create a Compute Engine VM instance and set up PostgreSQL there. This will allow you to fully manage your database.

Solution 2:[2]

As of November 2021 pg_cron is available:

https://cloud.google.com/sql/docs/release-notes#November_19_2021

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 David Michael Gang

Related Questions