Schedule icon
Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
Fail icon

PostgreSQL Extension Version Update Guard

Detect PostgreSQL extensions whose extversion lags the available default, run ALTER EXTENSION UPDATE behind an approval gate, then re-read to confirm.

Categories
DataInfrastructure

Upgrading the PostgreSQL binaries installs newer extension files on disk. So does a pg_upgrade. Neither changes what each database has registered. pg_extension.extversion keeps the old number while pg_available_extensions.default_version moves on, so the database keeps running the old SQL and C definitions.

The symptoms are confusing. A function the release notes say is fixed is still broken. A new feature the docs describe does not exist. Nothing in PostgreSQL warns you, because both versions are valid states. ALTER EXTENSION ... UPDATE is the post-upgrade step that is easy to forget.

This guard reads both catalogs, lists every extension whose registered version lags the default on disk with its from version and to version, runs the update behind a dry-run plus an approval gate, then re-reads extversion and fails the run if any target is still behind.

This blueprint was created by zkasuran.

Safety

  • Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and never updates an extension.
  • A change happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
  • An extension update is a real change. It runs the upgrade scripts the extension author shipped, which can add, drop or redefine functions, operators and types. That is why the finding carries the from version and the to version per extension, so the release notes can be read before anyone approves.
  • extension_skiplist leaves an extension alone for good. Use it where a version is pinned on purpose.
  • ALTER EXTENSION ... UPDATE fails when the author shipped no upgrade path between the installed version and the default. The detection reports that up front as path_available, the loop lets the statement fail without stopping the pass, then the re-read fails the run and names the extension. Nothing is hidden.
  • An extension whose files are gone from disk is not reported, because the join to pg_available_extensions drops it. That case needs the binaries fixed, not an update.

How it works

  1. detect (io.kestra.plugin.jdbc.postgresql.Query) joins pg_extension to pg_available_extensions and keeps the rows where extversion differs from default_version. Each row carries the from version, the to version, an EXISTS check over pg_extension_update_paths() plus the exact ALTER EXTENSION statement.
  2. summarize (io.kestra.plugin.core.output.OutputValues) builds the human readable target list and the JSON the remediation loop iterates, so the log, the alert and the loop all read the same set.
  3. log_detect (io.kestra.plugin.core.log.Log) logs the count, the per-extension version move and the mode.
  4. guard (io.kestra.plugin.core.flow.If) branches on the count. With none found, clean logs and the run ends. Otherwise findings_alert posts the finding on every detection.
  5. mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true and dry_run is false. Otherwise report_only lists what would be updated and stops.
  6. approval_gate (io.kestra.plugin.core.flow.Pause) holds the run until a human resumes it. update_extensions (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) then runs one ALTER EXTENSION ... UPDATE per target.
  7. reverify re-runs the comparison and verify_result fails the run through io.kestra.plugin.core.execution.Fail if any targeted extension is still behind.

What you get

  • A check that reads what the database has actually registered, not what the packages on disk claim, so it catches the gap a binary upgrade leaves behind.
  • The from version and the to version per extension, so release notes can be checked before the update is approved.
  • An up-front path_available flag from pg_extension_update_paths(), so an extension with no upgrade path is visible in the report instead of being a surprise at apply time.
  • An allowlist for a staged rollout plus a skiplist for versions you pin on purpose.
  • A real verification step. The run passes only when the second read of pg_extension.extversion shows every target at the default version.
  • Slack alerts on the findings and on any failure.

Who it's for

  • DBAs and platform engineers who have just upgraded PostgreSQL binaries or finished a pg_upgrade and need the per-database follow-up done and proven.
  • Teams running many databases on one cluster, where each one carries its own registered extension versions.
  • Anyone chasing a bug that the extension release notes say was fixed in a version the database is not actually running.

Why orchestrate this with Kestra

PostgreSQL does not tell you that a database is running an old extension version, has no scheduler to look, no approval step before the upgrade scripts run and no way to prove the update took effect. A one-off script updates what it finds then reports success whether or not the version moved. Kestra adds the missing pieces. It schedules the sweep, keeps it read-only by default, holds the change behind a human approval with the version move on screen, runs the statements one at a time, re-reads the catalog to turn a hopeful fix into a verified one, applies the same connection settings to every task then posts a Slack alert the moment a step fails.

Tested end to end

Run on Kestra 2.0.5 against a PostgreSQL 16 server, with a local HTTP sink standing in for Slack. The database was seeded with four extensions installed at an explicitly older version (hstore 1.4 of 1.8, citext 1.4 of 1.6, pg_trgm 1.3 of 1.6, intarray 1.2 of 1.5), two already at their default version (pgcrypto 1.3, tablefunc 1.0) plus a local extension built with two versions and no upgrade script between them. Every count and version was confirmed with psql against pg_extension joined to pg_available_extensions.

  • Detector can fail: with only the two already current extensions present it returned 0. After the four older ones were installed it returned exactly 4, never the two current ones, confirmed with psql.
  • Dry run detected the four, alerted and left every extversion unchanged.
  • Remediate with approval moved all four to their default version and the re-read reported 0 of them still behind. The two already current extensions were untouched.
  • The local extension with no upgrade path failed its ALTER EXTENSION inside the loop, the re-read found it still at its old version and the run failed and alerted rather than reporting success.
  • Declining the approval left all four at their old versions.
  • An unreachable host failed the first query and fired the Slack alert.

Prerequisites

A PostgreSQL server reachable over JDBC. The connecting user must be able to read pg_extension plus pg_available_extensions. To run ALTER EXTENSION ... UPDATE it must be the extension owner or a superuser.

Secrets

  • POSTGRES_USERNAME: PostgreSQL user, read when use_secret_auth is true.
  • POSTGRES_PASSWORD: password for that user.
  • SLACK_WEBHOOK_URL: used by findings_alert and the errors block.
  • In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name Type Default Description
postgres_url STRING jdbc:postgresql://localhost:5432/postgres PostgreSQL JDBC URL for the target database.
use_secret_auth BOOL true Read credentials from secrets. Set false only for inline test credentials.
extension_allowlist STRING "" Comma separated extension names to consider. Empty considers all of them.
extension_skiplist STRING "" Comma separated extension names to leave alone.
dry_run BOOL true Report without changing anything.
auto_remediate BOOL false Update behind the approval gate when dry_run is false.

Quick start

  1. Set POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
  2. Import this flow.
  3. Run it with postgres_url pointed at a database on a cluster whose binaries were upgraded. The default dry run reports which extensions still carry an older registered version.
  4. Read the release notes for the versions in the report. To apply them, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

  • Set extension_allowlist to one extension per run for a staged rollout across a busy cluster.
  • Put a deliberately pinned extension in extension_skiplist so the sweep stops reporting it.
  • Point the flow at each database in turn with a parent flow, since extension versions are registered per database rather than per cluster.
  • Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.
  • Report an extension whose installed version is newer than the default, which happens when binaries are rolled back. The inequality already catches it. The update path check reports no path, so handle that case by fixing the packages.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.