Skip to main content

Holistics-postgres cron jobs and schedule

Cron setup commands​

public.campaigns​

Every night at 2:00am UTC the materialized view public.campaigns is updated

select cron.schedule('daily overnight refresh of public.campaigns materialized view', '00 2 * * *', 'refresh materialized view public.campaigns;')

public.customers​

Every night at 2:02am UTC the materialized view public.customers is updated

select cron.schedule('daily overnight refresh of public.customers materialized view', '02 2 * * *', 'refresh materialized view public.customers;')

public.customer_users​

Every night at 2:04am UTC the materialized view public.customer_users is updated

select cron.schedule('daily overnight refresh of public.customer_users materialized view', '04 2 * * *', 'refresh materialized view public.customer_users;')

public.financial_agreements​

Every night at 2:06am UTC the materialized view public.financial_agreements is updated

select cron.schedule('daily overnight refresh of public.financial_agreements materialized view', '06 2 * * *', 'refresh materialized view public.financial_agreements;')

Every night at 2:08am UTC the materialized view public.customers_campaigns_agreements_link_table is updated

select cron.schedule('daily overnight refresh of public.customers_campaigns_agreements_link_table materialized view', '08 2 * * *', 'refresh materialized view public.customers_campaigns_agreements_link_table;')

public.bugs​

Every night at 2:10am UTC the materialized view public.bugs is updated

select cron.schedule('daily overnight refresh of public.bugs materialized view', '10 2 * * *', 'refresh materialized view public.bugs;')

public.trybers​

Every night at 2:30am UTC the materialized view public.trybers is updated

**select** cron.schedule('daily overnight refresh of public.trybers materialized view', '30 2 * * *', 'refresh materialized view public.trybers;')

public.trybers_augmentation​

Every night at 2:32am UTC the materialized view public.trybers_augmentation is updated

select cron.schedule('daily overnight refresh of public.trybers_augmentation materialized view', '32 2 * * *', 'refresh materialized view public.trybers_augmentation;')

public.tryber_has_lang​

Every night at 2:34am UTC the materialized view public.tryber_has_lang is updated

select cron.schedule('daily overnight refresh of public.tryber_has_lang materialized view', '34 2 * * *', 'refresh materialized view public.tryber_has_lang;')

public.tryber_has_enabled_device​

Every night at 2:36am UTC the materialized view public.tryber_has_enabled_device is updated

select cron.schedule('daily overnight refresh of public.tryber_has_enabled_device materialized view', '36 2 * * *', 'refresh materialized view public.tryber_has_enabled_device;')

public.tryber_has_acknowledged_provider​

Every night at 2:38am UTC the materialized view public.tryber_has_acknowledged_provider is updated

select cron.schedule('daily overnight refresh of public.tryber_has_acknowledged_provider materialized view', '38 2 * * *', 'refresh materialized view public.tryber_has_acknowledged_provider;')

public.trybers_pivoted_acknowledged_providers​

Every night at 2:40am UTC the materialized view public.trybers_pivoted_acknowledged_providers is updated

select cron.schedule('daily overnight refresh of public.trybers_pivoted_acknowledged_providers materialized view', '40 2 * * *', 'refresh materialized view public.trybers_pivoted_acknowledged_providers;')

ops tables indices​

Every night from 3:00am UTC we create indexes for the ops set of tables if they do not already exist

INSERT INTO cron.job (
  schedule,
  command,
  database,
  username,
  active,
  jobname
)
VALUES (
  '00 3 * * *',
  'CREATE UNIQUE INDEX IF NOT EXISTS opsinvoices_uniqueindex_id ON ops_invoices (id);
   CREATE INDEX IF NOT EXISTS opsinvoices_poid ON ops_invoices (po_id);
   CREATE INDEX IF NOT EXISTS opsinvoices_supplierid ON ops_invoices (supplier_id);
   CREATE INDEX IF NOT EXISTS opsinvoices_issuedate ON ops_invoices (issuedate);
   CREATE INDEX IF NOT EXISTS opsinvoices_duedate ON ops_invoices (duedate);',
  'postgres',
  'appq_holistics',
  True,
  'daily overnight creation of indexes for the ops_invoices table'
),
(
  '01 3 * * *',
  'CREATE UNIQUE INDEX IF NOT EXISTS opspurchaseorders_uniqueindex_id ON ops_purchase_orders (id);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_label ON ops_purchase_orders (label);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_issuedate ON ops_purchase_orders (issuedate);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_amount ON ops_purchase_orders (amount);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_cpid ON ops_purchase_orders (cp_id);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_supplierid ON ops_purchase_orders (supplier_id);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_suppliername ON ops_purchase_orders (supplier_name);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_suppliercode ON ops_purchase_orders (supplier_code);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_supplierlabel ON ops_purchase_orders (supplier_label);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_clickuptaskid ON ops_purchase_orders (clickuptask_id);
   CREATE INDEX IF NOT EXISTS opspurchaseorders_purchaseagentemail ON ops_purchase_orders (purchaseagent_email);',
  'postgres',
  'appq_holistics',
  True,
  'daily overnight creation of indexes for the ops_purchase_orders table'
),(
  '02 3 * * *',
  'CREATE UNIQUE INDEX IF NOT EXISTS opssuppliers_uniqueindex_id ON ops_suppliers (id);
   CREATE INDEX IF NOT EXISTS opssuppliers_code ON ops_suppliers (code);
   CREATE INDEX IF NOT EXISTS opssuppliers_suppliername ON ops_suppliers (supplier_name);
   CREATE INDEX IF NOT EXISTS opssuppliers_label ON ops_suppliers (label);',
  'postgres',
  'appq_holistics',
  True,
  'daily overnight creation of indexes for the ops_suppliers table'
);

We had to manually put the job inside the cron.job table because it would not accept it otherwise

DELETE cron.jobs​

Every 1st of the month, at 6am, we delete jobs older than 1 year

SELECT cron.schedule('monthly overnight delete of cron.jobs older than 365 days','00 6 1 * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '365 days'$$);

Active scheduled cron jobs​

jobidschedulecommanddatabaseuseractivejobname
600 6 1 * *DELETE FROM cron.job_run_details WHERE end_time < now() - interval '365 days'postgresappq_holisticstmonthly overnight delete of cron.jobs older than 365 days
700 2 * * *refresh materialized view public.campaigns;postgresappq_holisticstdaily overnight refresh of public.campaigns materialized view
802 2 * * *refresh materialized view public.customers;postgresappq_holisticstdaily overnight refresh of public.customers materialized view
904 2 * * *refresh materialized view public.customer_users;postgresappq_holisticstdaily overnight refresh of public.customer_users materialized v
1006 2 * * *refresh materialized view public.financial_agreements;postgresappq_holisticstdaily overnight refresh of public.financial_agreements material
1108 2 * * *refresh materialized view public.customers_campaigns_agreements_link_table;postgresappq_holisticstdaily overnight refresh of public.customers_campaigns_agreement
130 2 * * *refresh materialized view public.trybers;postgresappq_holisticstdaily overnight refresh of public.trybers materialized view
232 2 * * *refresh materialized view public.trybers_augmentation;postgresappq_holisticstdaily overnight refresh of public.trybers_augmentation material
334 2 * * *refresh materialized view public.tryber_has_lang;postgresappq_holisticstdaily overnight refresh of public.tryber_has_lang materialized
436 2 * * *refresh materialized view public.tryber_has_enabled_device;postgresappq_holisticstdaily overnight refresh of public.tryber_has_enabled_device mat
538 2 * * *refresh materialized view public.tryber_has_acknowledged_provider;postgresappq_holisticstdaily overnight refresh of public.tryber_has_acknowledged_provi
2240 2 * * *refresh materialized view public.trybers_pivoted_acknowledged_providers;postgresappq_holisticstdaily overnight refresh of public.trybers_pivoted_acknowledged_
2400 3 * * *CREATE UNIQUE INDEX IF NOT EXISTS opsinvoices_uniqueindex_id ON ops_invoices (id);postgresappq_holisticstdaily overnight creation of indexes for the ops_invoices table
2501 3 * * *CREATE UNIQUE INDEX IF NOT EXISTS opspurchaseorders_uniqueindex_id ON ops_purchase_orders (id);postgresappq_holisticstdaily overnight creation of indexes for the ops_purchase_orders
2602 3 * * *CREATE UNIQUE INDEX IF NOT EXISTS opssuppliers_uniqueindex_id ON ops_suppliers (id);postgresappq_holisticstdaily overnight creation of indexes for the ops_suppliers table
2710 2 * * *refresh materialized view public.bugs;postgresappq_holisticstdaily overnight refresh of public.bugs materialized view

Query to check the active cron job list table​

select jobid, schedule, command, database, username, active, jobname from cron.job;

Query to check the status of the cron jobs launched​

SELECT * FROM cron.job_run_details;

PG cron docs​

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html