3

I would like to use Cloud SQL with PostgreSQL with an internal cron-like tool. In the extensions list I can't find anything like that.

Do you know any solution/alternative which wouldn't include having an extra Compute Engine instance from where we can make calls ?

A.Queue
  • 1,549
  • 6
  • 21
Sergiu
  • 231
  • 1
  • 3
  • 14
  • As I understand you are asking to recommend a library, sadly this is off-topic for Cloud SQL. Otherwise it might be that I am misunderstanding what you are asking. – A.Queue Aug 01 '18 at 09:00
  • I would like to know a solution to use cron-like tool inside of Cloud SQL(like every 1min to make some select/insert in Cloud SQL without external API). – Sergiu Aug 01 '18 at 13:29

2 Answers2

3

You can't run cron internally in Cloud SQL as it is fully managed and you only get the access to the database itself.

Workaround

If you need to run this inserts and selects each minute then you can use Google's Task queues to send a request to an App Engine service that will do all that.

cron:
    - description: "make some select/insert"
      url: /tasks/populate
      schedule: every 1 mins
      target: populate_postgres

You have 28 free instance hours a day to use with App Engine instances.

Community
  • 1
  • 1
A.Queue
  • 1,549
  • 6
  • 21
0

You can run cron internally in Cloud SQL thanks to pg_cron extension support added on November 19, 2021.

pg_cron - Provides a cron-based job scheduler. This extension enables cron syntax to schedule PostgreSQL commands directly from the database. For more information about the extension, see the pg_cron section. Cloud SQL for PostgreSQL uses version 10 (or higher).

gcloud sql instances patch INSTANCE_NAME --database-flags=cloudsql.enable_pg_cron=on

CREATE EXTENSION pg_cron;

-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule(
          'delete outdated events',
          '30 3 * * 6', 
          $$ DELETE FROM events WHERE event_time < now() - '1 week'::interval $$
       );
Zegarek
  • 6,424
  • 1
  • 13
  • 24