--- title: 'pg_partman + pg_cron: Postgres 18 Auto Partitions (2026)' description: 'Tutorial: install pg_partman 5.4.3 + pg_cron on Postgres 18.4, configure daily range partitions with retention, automate maintenance, troubleshoot.' url: https://nerdleveltech.com/pg-partman-pg-cron-postgres-18-partition-automation-tutorial language: en date: '2026-05-20' category: backend tags: - pg_partman - pg_cron - postgres - postgresql - postgres-18 - partitioning - declarative-partitioning - retention - background-jobs - backend markdown_url: https://nerdleveltech.com/pg-partman-pg-cron-postgres-18-partition-automation-tutorial.md attribution: 'Source: NerdLevelTech — https://nerdleveltech.com/pg-partman-pg-cron-postgres-18-partition-automation-tutorial' terms: https://nerdleveltech.com/llms.txt --- # pg_partman + pg_cron: Postgres 18 Auto Partitions (2026) To partition a PostgreSQL 18 table by day with pg_partman 5.4.3, install `postgresql-18-partman` and `postgresql-18-cron`, add both to `shared_preload_libraries`, call `partman.create_parent('schema.table', 'created_at', '1 day', 'range')`, set `retention = '30 days'` and `retention_keep_table = false` on `part_config`, then schedule `CALL partman.run_maintenance_proc();` from pg_cron every hour. The whole loop — pre-create future partitions, drop the oldest ones past retention — runs without touching application code. ## TL;DR You'll build a hands-on, runnable `events` table partitioned by day on PostgreSQL 18.4 using pg_partman 5.4.3, with pg_cron driving the maintenance call. By the end you'll have 5 future partitions pre-created, 30-day retention auto-dropping the oldest, and a clear path to scale this pattern to billions of rows. Total walkthrough time is about 25 minutes from a fresh Docker pull. ## What you'll learn - How to install pg_partman 5.4.3 and pg_cron 1.6.7 on Postgres 18 with the official PGDG packages. - The pg_partman 5.x `create_parent` signature (and why the old `p_type => 'native'` value is gone). - How to configure 30-day retention with the right `retention_keep_table` setting so partitions actually drop. - How to schedule `partman.run_maintenance_proc()` with pg_cron — and when to prefer it over pg_partman's own background worker. - How to work around the Postgres 18 breaking change that forbids unlogged parent partitioned tables. - How to verify the partitions, the retention, and the cron job are all behaving as expected. ## Prerequisites - A modern Docker Desktop or Docker Engine with `docker compose` v2 (the v1 hyphenated `docker-compose` syntax also works if you adjust the commands). - Familiarity with `psql` and basic SQL. - ~25 minutes and ~2 GB of disk for the Postgres image and a small volume. We pin Postgres 18.4 (released 2026-05-14)[^pg18-4] and pg_partman 5.4.3 (released 2026-03-05)[^partman-543]. **Do not pin pg_partman 5.4.2** — the extension control file ships the wrong version string so updates from 5.4.1 break; 5.4.3 fixes the regression[^partman-543]. ## Step 1 — Build a Postgres 18 image with both extensions The official `postgres:18.4` image[^postgres-image] does not include pg_partman or pg_cron, but it does ship with the PGDG apt repository already wired into `/etc/apt/sources.list.d/`[^pgdg-apt] — so a plain `apt-get install postgresql-18-partman postgresql-18-cron` works without any extra repo setup. Create a `Dockerfile`: ```dockerfile # Dockerfile FROM postgres:18.4 RUN apt-get update \ && apt-get install -y --no-install-recommends \ postgresql-18-partman \ postgresql-18-cron \ && rm -rf /var/lib/apt/lists/* # Pass settings as -c overrides instead of replacing config_file outright — # this way the image's auto-generated postgresql.conf (data_directory, # hba_file, lc_messages, etc.) still loads underneath and we only set # the knobs the extensions care about. Replacing config_file entirely is # fragile because the auto-generated values live inside the data volume. CMD ["postgres", \ "-c", "shared_preload_libraries=pg_partman_bgw,pg_cron", \ "-c", "cron.database_name=events", \ "-c", "pg_partman_bgw.dbname="] ``` Three things the `-c` overrides set up: - `shared_preload_libraries` loads both background workers at server start. They must be present before the first `CREATE EXTENSION` or the create will succeed but nothing will run in the background. - `cron.database_name = 'events'` tells pg_cron which database to install its `cron.*` catalog into. **pg_cron can only be installed into one database per cluster**[^pgcron-readme]; cross-database scheduling is done with `cron.schedule_in_database()` later. - `pg_partman_bgw.dbname = ` (empty) keeps pg_partman's own background worker loaded but idle — when this CSV list is unset, the worker skips calling `run_maintenance()` entirely[^partman-docs]. pg_cron becomes the single scheduler. Build and run it with a docker-compose file to keep the data around between restarts: ```yaml # docker-compose.yml services: pg18: build: . environment: POSTGRES_PASSWORD: dev POSTGRES_DB: events ports: ["5432:5432"] volumes: - pgdata:/var/lib/postgresql/data volumes: pgdata: ``` Bring it up: ```bash docker compose up -d --build docker compose logs pg18 | tail -5 ``` You should see `database system is ready to accept connections`. If you see `FATAL: extension "pg_cron" must be loaded via shared_preload_libraries`, the `-c shared_preload_libraries=...` flag isn't reaching postgres — re-check the `CMD` line in the Dockerfile and rebuild with `docker compose up -d --build`. ## Step 2 — Create the extensions and the parent table Connect with `psql`: ```bash docker compose exec pg18 psql -U postgres -d events ``` pg_partman expects to live in its own schema; the conventional name is `partman`[^partman-docs]. pg_cron always installs into `cron`. Both extensions create types, tables, and functions, so this has to run as a superuser: ```sql CREATE EXTENSION IF NOT EXISTS pg_cron; CREATE SCHEMA IF NOT EXISTS partman; CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman; \dx ``` The `\dx` output should list both `pg_cron` (version 1.6.7) and `pg_partman` (version 5.4.3). Now create the parent table. The crucial Postgres 18 rule: **a partitioned table cannot be `UNLOGGED`** — the parent must be logged, and the child partitions inherit that[^pg18-unlogged-mail][^partman-774]. If you want unlogged child partitions you have to mark the pg_partman *template* table as unlogged after the fact; we'll cover that in the troubleshooting section. For now, a plain `LOGGED` parent: ```sql CREATE TABLE public.events ( event_id BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL, user_id UUID NOT NULL, event_type TEXT NOT NULL, payload JSONB NOT NULL DEFAULT '{}'::jsonb, PRIMARY KEY (event_id, created_at) ) PARTITION BY RANGE (created_at); ``` Two things to notice: the partition key (`created_at`) is part of the primary key — Postgres requires this for any unique constraint on a partitioned table. And the table is empty: pg_partman handles partition creation, so you don't precreate child tables yourself. ## Step 3 — Call create_parent the pg_partman 5 way The pg_partman 5.x `create_parent` signature is different from the 4.x tutorials you'll find at the top of Google. The first four arguments are required, and `p_type` only accepts `'range'` or `'list'` — the old `'native'` value was removed in the 5.0 transition to declarative-only partitioning and now errors with `ERROR: native is not a valid partitioning type for pg_partman`[^partman-native-removed][^partman-docs]: ```sql SELECT partman.create_parent( p_parent_table => 'public.events', p_control => 'created_at', p_type => 'range', p_interval => '1 day', p_premake => 5, p_start_partition => (date_trunc('day', now()) - interval '2 days')::text ); ``` What the arguments mean: - `p_control` — the column to partition on. Must already exist on the parent. - `p_type` — `'range'` (time- or number-based) or `'list'` (number-only, interval=1)[^partman-docs]. - `p_interval` — an interval string. `'1 day'`, `'1 hour'`, `'1 week'`, and `'1 month'` are the common shapes. Unlike pre-5.0 pg_partman, the 5.x line dropped the old convenience strings (`'daily'`, `'hourly'`, `'weekly'`, `'monthly'`) — every interval must now be a value Postgres's own `interval` type accepts[^partman-docs][^crunchy-intervals]. - `p_premake` — how many partitions ahead of "now" to keep pre-created. Default is 4; we set 5 here, so you'll end up with the current day plus 5 future days. - `p_start_partition` — bounds the earliest partition. We start 2 days back to give the demo two slightly-older partitions you can immediately watch get dropped by retention. `create_parent` returns `t` (true) on success and creates the child partitions plus a row in `partman.part_config`: ```sql SELECT parent_table, control, partition_interval, premake FROM partman.part_config WHERE parent_table = 'public.events'; ``` ``` parent_table | control | partition_interval | premake ----------------------+------------+--------------------+--------- public.events | created_at | 1 day | 5 ``` And the partitions themselves: ```sql SELECT * FROM partman.show_partitions('public.events'); ``` You should see 8 child tables (`events_p20260518` through `events_p20260525` if today is the 20th) — two historic, the current day, and five future days. Names follow `events_p{YYYYMMDD}` for daily intervals (pg_partman 5.0 simplified suffixes to `YYYYMMDD` with no separators)[^partman-docs]. ## Step 4 — Configure retention and actually drop old partitions Out of the box pg_partman does **not** drop old partitions. Retention is opt-in via `part_config`, and it has a gotcha: the default `retention_keep_table = true` only *uninherits* old partitions, leaving them as standalone tables in the database[^partman-docs]. To get true auto-cleanup you must flip it to `false`: ```sql UPDATE partman.part_config SET retention = '30 days', retention_keep_table = false WHERE parent_table = 'public.events'; ``` A few sentences on why the default is conservative: pg_partman's defaults assume accidental data loss is the worst outcome, so it leans toward "uninherit, leave a table you can re-attach later" rather than `DROP TABLE`[^partman-docs]. For an events log where 30 days is the legal retention ceiling, `false` is what you actually want. ## Step 5 — Schedule maintenance with pg_cron pg_partman ships its own background worker (`pg_partman_bgw`) that calls `run_maintenance_proc()` every `pg_partman_bgw.interval` seconds (default 3600)[^partman-docs]. So why bother with pg_cron? Three reasons. First, the pg_partman BGW runs every `pg_partman_bgw.interval` seconds with no per-job granularity — you can't say "every 5 minutes during business hours, every hour otherwise." pg_cron takes full cron expressions, scheduled per job. Second, pg_cron gives you per-job history in `cron.job_run_details`, which is much easier to alert on than parsing Postgres logs. Third, if you already run pg_cron for `VACUUM` schedules or stats refreshes, adding partition maintenance to the same scheduler is one less moving part. Schedule the maintenance procedure once an hour. Because pg_cron's extension lives in the `events` database (set by `cron.database_name` in Step 1), `cron.schedule` runs the SQL there directly[^pgcron-readme]: ```sql SELECT cron.schedule( 'pg_partman-maintenance', '0 * * * *', $$CALL partman.run_maintenance_proc()$$ ); ``` The schedule string is standard cron syntax in UTC. Verify the job is registered: ```sql SELECT jobid, schedule, command, active FROM cron.job WHERE jobname = 'pg_partman-maintenance'; ``` And the per-run history (this will be empty until the next top-of-hour): ```sql SELECT jobid, runid, status, return_message, start_time, end_time FROM cron.job_run_details ORDER BY start_time DESC LIMIT 5; ``` If you don't want to wait an hour to see anything, force the procedure once by hand: ```sql CALL partman.run_maintenance_proc(); ``` This is a procedure (not a function) so it must be invoked with `CALL`. After it runs, `partman.show_partitions('public.events')` should still show your full ladder of future partitions — `run_maintenance_proc` is idempotent. ## Step 6 — Verify routing, retention, and the cron loop Insert a few rows spanning a wide date range, including one outside the retention window: ```sql INSERT INTO public.events (event_id, created_at, user_id, event_type) SELECT gs, now() - (gs || ' hours')::interval, gen_random_uuid(), 'pageview' FROM generate_series(1, 200) gs; ``` Confirm Postgres routed them into the right child tables (you should see a count > 0 for the current day's partition and zero hits on the parent itself): ```sql SELECT tableoid::regclass AS partition, count(*) FROM public.events GROUP BY partition ORDER BY partition; ``` Now insert a row inside a partition that's older than retention but younger than your earliest precreated partition, then run maintenance: ```sql INSERT INTO public.events VALUES (999, now() - interval '40 days', gen_random_uuid(), 'historical'); CALL partman.run_maintenance_proc(); SELECT * FROM partman.show_partitions('public.events'); ``` The 40-day-old row landed in a partition that's now past `retention = '30 days'`, so the next maintenance call drops it. (If you instead wanted that partition preserved for audit, you'd set `retention_keep_table = true` from Step 4 and the table would be uninherited but still queryable directly.) ## Troubleshooting The five error modes worth knowing before you ship this to production: **`ERROR: native is not a valid partitioning type for pg_partman`** — you're using a pg_partman 4.x tutorial against a 5.x install. Replace `p_type => 'native'` with `'range'` (or `'list'`). The `native` value was removed in 5.0 when pg_partman dropped trigger-based partitioning entirely[^partman-native-removed]. **`ERROR: partitioned tables cannot be unlogged`** — you tried to `CREATE UNLOGGED TABLE ... PARTITION BY RANGE`. Postgres 18 enforces what older versions silently allowed[^pg18-unlogged-mail]. If you want unlogged child partitions for a metrics-style table where durability doesn't matter, create the parent `LOGGED` and then `ALTER TABLE partman.template_public_events SET UNLOGGED;` — new child partitions inherit the unlogged flag through pg_partman's template system[^partman-774]. **Old partitions aren't dropping** — check `retention_keep_table` on the row in `partman.part_config`. The default `true` only uninherits old partitions, so they accumulate as standalone tables until you `DROP TABLE` them manually. Set it to `false` for actual cleanup. **`FATAL: extension "pg_cron" must be loaded via shared_preload_libraries`** — pg_cron requires startup-time loading and a Postgres restart. The Docker container's `CMD` must include `-c shared_preload_libraries=pg_partman_bgw,pg_cron` (or the equivalent set in postgresql.conf) *before* the first start. After changing the Dockerfile, `docker compose up -d --build` to rebuild. **Cron jobs run but never write to `cron.job_run_details`** — almost always a `cron.database_name` mismatch: pg_cron stores history in the database named there. If you installed `pg_cron` into `events` but `cron.database_name` still says `postgres`, history goes nowhere. Restart with the correct value. ## Next steps and further reading This pattern composes naturally with the rest of the Postgres 18 toolchain. If you're upgrading to Postgres 18 in production, the [pg_createsubscriber zero-downtime upgrade tutorial](https://nerdleveltech.com/postgres-18-zero-downtime-upgrade-pg-createsubscriber-tutorial) walks through the cutover. For vector workloads on the same partitioned schema, see the [pgvector + HNSW Postgres 18 production tuning guide](https://nerdleveltech.com/pgvector-hnsw-postgres-18-production-tuning-tutorial). And if you'd rather queue background jobs in Postgres than schedule them with cron, the [pg-boss job-queue tutorial](https://nerdleveltech.com/pg-boss-postgres-job-queue-node-typescript-production-tutorial) is the queue-shaped sibling of this scheduler-shaped post. External reading worth bookmarking: the pg_partman documentation on the development branch[^partman-docs], pg_partman's release notes for the 5.x line[^partman-543], and the pg_cron README for advanced schedule syntax including the `cron.schedule_in_database` variant when your jobs target a different database than the one pg_cron is installed in[^pgcron-readme]. ## Related reads - [Postgres LISTEN/NOTIFY for Job Queues: 2026 Guide](https://nerdleveltech.com/postgres-listen-notify-job-queue) [^pg18-4]: PostgreSQL 18.4 release announcement, May 14 2026 — https://www.postgresql.org/docs/release/ [^partman-543]: pg_partman 5.4.3 on PGXN, released March 5 2026 — https://pgxn.org/dist/pg_partman/5.4.3/ [^postgres-image]: Postgres official Docker image tag list — https://hub.docker.com/_/postgres [^pgdg-apt]: PostgreSQL Debian/Ubuntu APT repository documentation — https://wiki.postgresql.org/wiki/Apt [^partman-docs]: pg_partman canonical documentation (development branch) — https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md [^pg18-unlogged-mail]: PostgreSQL hackers thread on partitioned-table unlogged restriction (Michael Paquier) — https://www.postgresql.org/message-id/ZiiyGFTBNkqcMQi_@paquier.xyz [^partman-774]: pg_partman issue #774 — Postgres 18 unlogged parent restriction handling — https://github.com/pgpartman/pg_partman/issues/774 [^partman-native-removed]: pg_partman discussion #691 — `'native'` is no longer a valid p_type in 5.x — https://github.com/pgpartman/pg_partman/discussions/691 [^crunchy-intervals]: Crunchy Data — Time Partitioning and Custom Time Intervals in Postgres with pg_partman — https://www.crunchydata.com/blog/time-partitioning-and-custom-time-intervals-in-postgres-with-pg_partman [^pgcron-readme]: pg_cron README and CHANGELOG — https://github.com/citusdata/pg_cron