pg_partman Extension Upgrade Quirks and Background Workers

A field reference for upgrading pg_partman without stalling partition maintenance: how the optional pg_partman_bgw background worker interacts with shared_preload_libraries, why the config table schema can change between versions, and how to keep run_maintenance running across the bump.

Up: Compatibility Matrix Synchronization — this page is the pg_partman-specific cell of that matrix, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies

pg_partman manages time- and serial-based partition sets, and unlike PostGIS it barely touches the query planner — its risk surface is entirely operational. It maintains a configuration table (part_config) that drives which partition sets exist and how far ahead partitions are pre-created, and it ships an optional background worker, pg_partman_bgw, that calls run_maintenance() on a schedule. That worker is a preload library: it only runs if pg_partman_bgw is listed in shared_preload_libraries, and its behaviour is tuned by pg_partman_bgw.* GUCs in postgresql.conf. This reference applies whenever you move pg_partman between 4.x and 5.x, or across 5.x minors, on PostgreSQL 14–17.

The upgrade is low-lock but easy to get subtly wrong, because a schema change to part_config or a rename of a maintenance function can silently stop new partitions from being created — a failure you notice days later when writes hit a missing partition. Rehearse the bump and then let maintenance run for a full cycle on a clone through test environment routing before promoting, and categorize any surfaced failure with the error categorization framework.

The three coupled surfaces a pg_partman upgrade must keep aligned A pg_partman upgrade must keep three surfaces aligned. The part_config configuration table may gain or rename columns between versions and drives every partition set. The maintenance functions, chiefly run_maintenance, may change signatures. The optional pg_partman_bgw background worker only runs when listed in shared_preload_libraries and calls run_maintenance on a schedule. If the worker calls a renamed function or reads a changed part_config, partition pre-creation stops silently, and writes eventually hit a missing partition. part_config column set may change drives every partition set run_maintenance() signature may change pre-creates partitions pg_partman_bgw preload-gated worker calls maintenance on a timer Silent stall missing future partition write fails days later drift here →

Concept: Maintenance Continuity Across the Bump

The core concept is that pg_partman’s catalog upgrade (ALTER EXTENSION pg_partman UPDATE) and its operational continuity are separate concerns. The ALTER EXTENSION step migrates the part_config table and function definitions transactionally and is low-risk. The risk is the background worker: pg_partman_bgw is a long-lived process that loaded the old function definitions at startup, and after the extension updates its functions, the worker may keep calling a signature that no longer exists until it is restarted. Because it is a preload worker, restarting it means either a full server restart or a pg_reload_conf() plus worker recycle, depending on version.

The second concept is pre-creation lead time as a safety buffer. pg_partman pre-creates partitions premake intervals ahead of now. That buffer is exactly what lets you upgrade without a maintenance outage: as long as future partitions already exist, a briefly stalled worker does not cause a write failure. Sizing that buffer generously before the upgrade converts a hard failure into a soft one, and the buffer interacts with the lock and downtime math in threshold tuning for downtime windows.

Runnable Implementation

The procedure widens the pre-creation buffer, performs the transactional extension update, then forces a maintenance run and restarts the worker so it reloads the new functions.

-- 1. Widen the safety buffer BEFORE upgrading so a stalled worker cannot
--    cause a missing-partition write error mid-upgrade. Pre-create further out.
UPDATE partman.part_config
SET    premake = GREATEST(premake, 8)
WHERE  parent_table IS NOT NULL;

SELECT partman.run_maintenance();   -- materialize the widened buffer now

-- 2. Transactional catalog upgrade. part_config migrations and function
--    redefinitions apply atomically here.
ALTER EXTENSION pg_partman UPDATE;

-- 3. Prove the new maintenance path works before trusting the worker.
SELECT partman.run_maintenance(p_analyze := false);

After step 3, restart the background worker so it drops its cached old function definitions. On most versions that means a sudo systemctl restart postgresql because pg_partman_bgw is preload-scoped; automate the restart with the transactional-safety guards from ALTER EXTENSION automation and keep a rollback point via snapshot and point-in-time recovery.

Expected Output & Verification

A healthy upgrade leaves the extension at the new version and every partition set with a comfortable lead of future partitions:

-- Confirm the version advanced and count future partitions per set.
SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';

SELECT parent_table,
       count(*) FILTER (WHERE child_start_time > now()) AS future_partitions
FROM   partman.show_partitions_info()      -- 5.x reporting helper
GROUP  BY parent_table
ORDER  BY future_partitions;

Every partition set should report at least premake future partitions. A set showing zero means the worker never resumed — investigate before the next write window. Record the shipped version and the pg_partman_bgw GUCs in compatibility matrix synchronization so nodes gate on both the extension and its preload configuration.

Edge Cases & Gotchas

The 4.x → 5.x jump renames and restructures part_config. Version 5 dropped several 4.x columns and changed the partitioning model to lean entirely on native declarative partitioning. A migration that assumes 4.x column names raises:

ERROR:  column "type" of relation "part_config" does not exist

Read the 5.x release notes, migrate custom automation that reads part_config directly, and validate the new schema in a simulation before promoting. Treat this as a SCHEMA_DRIFT requiring code changes, not a retry.

The background worker keeps calling an old function signature. If you skip the worker restart, the log fills with:

ERROR:  function partman.run_maintenance(text, boolean, boolean) does not exist

emitted by the worker on its timer. The catalog is fine; the process is stale. Restart the server (or recycle the worker) so it reloads the current function set.

A misconfigured pg_partman_bgw.dbname silently disables maintenance. The worker only maintains databases named in pg_partman_bgw.dbname. If an upgrade or config-management run drops your database from that list, maintenance stops with no error at all. Assert the GUC after every restart, and keep the value under version control and branching so config drift is caught in review.

premake too small turns a brief stall into an outage. If the buffer is one interval and the worker is down across a boundary, the next insert hits a nonexistent partition and fails. Widening premake before the upgrade (step 1) is the cheap insurance that makes the whole operation forgiving.