I am trying to schedule pg_cron job using liquiBase, script is marked as EXECUTED in DATABASECHANGELOG, however, in CRON.JOB table there are no jobs added.
Part of data: liquibase.properties: |- changeLogFile:changelog-master.xml url:jdbc:postgresql://...?currentSchema=...&sslmode=disable username:{{ .Values.username }} liquibase.hub.mode=off liquibase.logLevel=FINE
changelog-master.xml: |-
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd">
<changeSet id="1" author="toka" runInTransaction="true">
<sqlFile path="0001-add-pg-cron.sql" relativeToChangelogFile="true"/>
</changeSet>
</databaseChangeLog>
0001-add-pg-cron.sql: |-
DROP EXTENSION IF EXISTS pg_cron CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_cron;
GRANT USAGE ON SCHEMA cron TO "app_writer";
GRANT USAGE ON SCHEMA cron TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cron TO postgres;
SELECT CRON.SCHEDULE('*/5 * * * *', 'DELETE FROM table WHERE ModifiedDate < now()::date - 30');
I am running it in container with following configuration:
command: ["/bin/bash", "-c"]
args:
- |
sleep 15;
cp /home/appown/ro-config/* /liquibase
ls -al liquibase;
./liquibase --defaultsFile=liquibase.properties update
In liquiBase logs in GCP I can see that script has been executed and extension was added, USAGE and ALL PRIVILEGES were granted, however, job was not added succesfully. There aren't any issues with different queries (table creation etc.) just with pg_cron.
Same query executed with Query tool in pg_admin works fine.
