Use pg_cron

Available in VPC

Cloud DB for PostgreSQL provides the pg_cron extension. See the user guide below. pg_cron is an extension that uses cron expressions to schedule SQL jobs in PostgreSQL.

Install extensions from the console

Because Cloud DB for PostgreSQL does not provide superuser privileges, use the console to install extensions that require superuser privileges. For more information, see the following guide:

• Install extensions

When installing pg_cron, select the database where you plan to schedule jobs. During installation, the DB Server restarts. In high availability (HA) configurations, servers restart one at a time through the failover process.

pg_cron concept

After you install pg_cron, the cron schema is created in the selected database. Use the following components to schedule and manage jobs. Because Cloud DB for PostgreSQL does not provide superuser privileges, some functions are unavailable when you use a DB owner account as shown in the table below:

Scheduled jobs run with the permissions of the user who schedules them. Jobs cannot run on tables that the user who schedules them cannot access.

Schedule and manage jobs

Log in to the database selected during installation with the DB owner account and schedule jobs.

Schedule jobs

Use cron.schedule(작업이름, 스케줄, 실행할 SQL) to schedule jobs.

-- Run VACUUM ANALYZE on the sales.orders table every day at 3:00 AM
SELECT cron.schedule('nightly-vacuum-orders', '0 3 * * *', 'VACUUM ANALYZE sales.orders');

-- Delete orders older than 90 days every Sunday at 5:00 AM
SELECT cron.schedule('purge-old-orders', '0 5 * * 0', $$DELETE FROM sales.orders WHERE ordered_at < now() - interval '90 days'$$);

View and change jobs

View scheduled jobs in cron.job. Because cron.alter_job is unavailable, delete the job with cron.unschedule, then schedule it again with the same name to update its schedule or command.

-- View scheduled jobs (shows only jobs you registered)
SELECT jobid, jobname, schedule, command, active FROM cron.job;

-- Change the schedule: Delete the existing job and schedule it again with the new schedule.
SELECT cron.unschedule('purge-old-orders');
SELECT cron.schedule('purge-old-orders', '0 6 * * 0', $$DELETE FROM sales.orders WHERE ordered_at < now() - interval '90 days'$$);

-- Delete a job
SELECT cron.unschedule('nightly-vacuum-orders');

Example: Load daily sales aggregates

This example aggregates the previous day's orders early each morning and loads the results into a summary table. You can run this example as is, from creating the schema and tables through validation. This example assumes that mainowner is the database where maindb is installed and mainowner is its DB owner. After you log in to maindb as mainowner, register all jobs.

-- Log in to maindb as mainowner.
\c maindb mainowner

-- 1. Create a schema for this example. (Cloud DB for PostgreSQL does not recommend using the public schema, so use a separate schema.)
CREATE SCHEMA sales;

-- 2. Create the source orders table and add sample data. (Skip this step if you already have a table in use.)
CREATE TABLE sales.orders (
    order_id   bigserial     PRIMARY KEY,
    amount     numeric(12,2) NOT NULL,
    ordered_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO sales.orders (amount, ordered_at) VALUES
    (15000, current_date - 1 + time '09:30'), -- Order from yesterday
    (32000, current_date - 1 + time '1:10 PM'), -- Order from yesterday
    (8900, current_date - 1 + time '20:05'), -- Order from yesterday
    (47000, current_date + time '08:00'); -- Order from today (not included in yesterday's aggregate)

-- 3. Create a summary table to store the aggregate results.
CREATE TABLE sales.daily_sales_summary (
    sales_date   date          PRIMARY KEY,
    order_cnt    integer       NOT NULL,
    total_amount numeric(14,2) NOT NULL
);

-- 4. First, schedule the job to run every minute to quickly verify that it works.
-- Use schema-qualified names and update the result with ON CONFLICT to prevent duplicates when the job runs again for the same date.
SELECT cron.schedule(
    'rollup-daily-sales',
    '* * * * *',
    $$INSERT INTO sales.daily_sales_summary (sales_date, order_cnt, total_amount)
      SELECT current_date - 1, count(*), coalesce(sum(amount), 0)
      FROM sales.orders
      WHERE ordered_at >= current_date - 1
        AND ordered_at <  current_date
      ON CONFLICT (sales_date)
      DO UPDATE SET order_cnt    = EXCLUDED.order_cnt,
                    total_amount = EXCLUDED.total_amount$$
);

-- 5. After waiting 1-2 minutes, check the loaded data and job execution history.
SELECT sales_date, order_cnt, total_amount
FROM sales.daily_sales_summary
ORDER BY sales_date DESC
LIMIT 10;

SELECT jobid, status, return_message, start_time
FROM cron.job_run_details
WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'rollup-daily-sales')
ORDER BY start_time DESC
LIMIT 5;

-- 6. After you confirm the job runs correctly, delete the test job and reschedule it to the production schedule (daily at 12:05 AM).
--    (Because cron.alter_job is unavailable, delete the job with cron.unschedule and schedule it again.)
SELECT cron.unschedule('rollup-daily-sales');
SELECT cron.schedule(
    'rollup-daily-sales',
    '5 0 * * *',
    $$INSERT INTO sales.daily_sales_summary (sales_date, order_cnt, total_amount)
      SELECT current_date - 1, count(*), coalesce(sum(amount), 0)
      FROM sales.orders
      WHERE ordered_at >= current_date - 1
        AND ordered_at <  current_date
      ON CONFLICT (sales_date)
      DO UPDATE SET order_cnt    = EXCLUDED.order_cnt,
                    total_amount = EXCLUDED.total_amount$$
);

Monitor job execution history

You can view job execution history in cron.job_run_details. Only the history of jobs you registered is displayed.

-- View recent execution history
SELECT jobid, jobname, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

Because cron.job_run_details is not cleaned up automatically, we recommend scheduling a cleanup job as follows:

-- Delete execution history older than 30 days every Sunday at 5:00 AM
SELECT cron.schedule('cleanup-cron-history', '0 5 * * 0', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '30 days'$$);