pg_cron and more

0) What you’ll end up with

• pg_cron enabled at the instance level (via shared_preload_libraries) and installed in a database
• pg_partman installed in sales (schema partman)
• A pg_cron job that periodically runs pg_partman maintenance (partition creation/retention)

AWS confirms:

• pg_cron requires adding pg_cron to shared_preload_libraries and restarting.
• pg_partman is supported on RDS and is enabled per database via CREATE EXTENSION ….

1) Create & attach a custom DB parameter group (required for pg_cron)

In the AWS Console

1. RDS → Parameter groups → Create parameter group
   • Parameter group family: postgres15
   • Type: DB Parameter Group
   • Name: e.g. pg15-cron-partman
2. Open your new parameter group and edit:

Parameters to set

A) shared_preload_libraries

• Add pg_cron to the list.
• Keep what’s already there (commonly pg_stat_statements).
• Example final value:
  • pg_stat_statements,pg_cron

AWS explicitly requires adding pg_cron to shared_preload_libraries.

B) cron.database_name (choose where pg_cron stores its metadata)

• Default is typically the postgres database, and AWS notes the scheduler is set there by default.
• Since you want to operate in sales, I recommend setting:
  • cron.database_name = sales

This makes pg_cron’s metadata tables live in sales, which is usually what you want for a single-app DB.

Note: changing these static params requires a DB reboot to take effect.

Attach the parameter group to the DB instance

1. RDS → Databases → your instance → Modify
2. Set DB parameter group = pg15-cron-partman
3. Apply (immediate is fine, but it won’t take effect until reboot)
4. Reboot the DB instance

2) Install pg_cron in the correct database

Because you set cron.database_name = sales, connect to sales as your master user (commonly postgres) and run:

-- Connect to sales
CREATE EXTENSION pg_cron;

AWS documents that creating the extension requires an account with rds_superuser privileges (your master user typically has what you need).

Quick verification

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_cron';

SELECT * FROM cron.job LIMIT 5;

3) Grant pg_cron usage to an application role (optional but recommended)

If you want a non-master role to schedule jobs, AWS recommends granting access to the cron schema.

Example (adjust role name):

-- As master user / rds_superuser
GRANT USAGE ON SCHEMA cron TO sales_app;

-- Optional: allow viewing job run history (least privilege is your call)
GRANT SELECT ON ALL TABLES IN SCHEMA cron TO sales_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA cron GRANT SELECT ON TABLES TO sales_app;

Important: the job will still fail if that role doesn’t have permissions on the objects it tries to touch.

4) Install pg_partman in the sales database

AWS’s RDS guide recommends creating a partman schema and installing the extension there.

-- Connect to sales
CREATE SCHEMA IF NOT EXISTS partman;

CREATE EXTENSION pg_partman WITH SCHEMA partman;

If you hit a permissions error, AWS notes you need rds_superuser or grant it to the role performing the install.

Verification

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_partman';

-- Display the age of each table in transactions along with its total size
select
 c.oid,
 age(c.relfrozenxid) as age_in_transactions,
 , pg_size_pretty(pg_total_relation_size(c.oid)) as total_size
from  pg_class as c
join pg_namespace as n on c.relnamespace = n.oid
where c.relkind in ('r','t','m')
  and n.nspname not in ('pg_catalog', 'information_schema')
  and c.relname not like 'pg_%_toast'
order by age_in_transactions desc

-- Display the age of the database in transactions along with the autovacuum freeze max age setting
select datname,
       age(datfrozenxid) as age_in_transactions,
       current_setting('autovacuum_freeze_max_age')::int as autovacuum_freeze_max_age
from pg_database
order by age_in_transactions desc;

with max_age as (
    select 2000000000 as max_old_xid,
    setting as autovacuum_freeze_max_age
    from pg_settings
    where name = 'autovacuum_freeze_max_age' )
, per_database_stats as (
    select datname,
        age(datfrozenxid) as oldest_current_xid,
        datfrozenxid,
        max_old_xid::int,
        autovacuum_freeze_max_age::int
    from pg_database
    join max_age on (true)
    where datallowconn )
select max(oldest_current_xid) as oldest_current_xid,
       max(round(100.0*(oldest_current_xid/max_old_xid)::numeric, 2)) as percent_toward_wraparound,
       max(round(100.0*(oldest_current_xid/autovacuum_freeze_max_age)::numeric, 2)) as percent_toward_autovacuum_freeze_max_age
from per_database_stats;