Skip to content

pg_extension_updater

Automatic extension updater

pg_lake : Automatic extension updater

Overview

ID Extension Package Version Category License Language
2562 pg_extension_updater pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd-- No Yes Yes Yes no no
Optional startup updater.

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake pg_extension_base
RPM PIGSTY 3.7.0 19 18 17 16 15 pg_lake_$v -
DEB PIGSTY 3.7.0 19 18 17 16 15 postgresql-$v-pg-lake -
Linux / PG PG19 PG18 PG17 PG16 PG15
el8.x86_64 N/A N/A N/A N/A N/A
el8.aarch64 N/A N/A N/A N/A N/A
el9.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el9.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A

Source

github.com/Snowflake-Labs/pg_lake/tree/main/pg_extension_updater

pig build pkg pg_lake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_lake;		# install via package name, for the active PG version
pig install pg_extension_updater;		# install by extension name, for the current active PG version

pig install pg_extension_updater -v 19;   # install for PG 19
pig install pg_extension_updater -v 18;   # install for PG 18
pig install pg_extension_updater -v 17;   # install for PG 17
pig install pg_extension_updater -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_extension_base';

Create this extension with:

CREATE EXTENSION pg_extension_updater CASCADE; -- requires pg_extension_base

Usage

Sources:

pg_extension_updater runs ALTER EXTENSION ... UPDATE for each extension whose installed version differs from its available default version when the database lifecycle worker starts. It is intended to reduce SQL/binary version mismatches after new extension files are deployed; it does not install missing extensions or replace release testing.

The reviewed upstream release is 3.6.0, with SQL extension version 3.6 and PostgreSQL 16–19 support. Existing packaged versions are tracked separately. Keep libraries and the query server from the same release.

Enable Automatic Updates

The updater depends on pg_extension_base, which must be preloaded cluster-wide:

shared_preload_libraries = 'pg_extension_base'

After restarting PostgreSQL, create the updater in every database where automatic updates are desired:

CREATE EXTENSION pg_extension_updater CASCADE;

Creating it in template1 causes new databases cloned from that template to contain the updater. The worker does not update extensions inside the template database itself, but starts in a newly cloned database.

Runtime Behavior

  • The #!shared_preload_libraries directive in the updater’s control file lets pg_extension_base load its library.
  • Installation registers the internal extension_updater.main(internal) lifecycle worker.
  • At worker startup, it reads pg_available_extensions and updates entries whose installed_version differs from default_version.
  • A failed ALTER EXTENSION is logged as a warning and is attempted only once during that worker run.
  • pg_extension_updater.enable is a postmaster setting, defaults to on, and disables the worker’s updates when set to off; changing it requires a restart.

Caveats

  • Automatic migration can execute arbitrary upgrade SQL supplied by every installed extension. Validate packages and upgrade paths before enabling it on production databases.
  • Review extension dependency changes and take application-specific backups independently; a warning does not roll back unrelated successful extension updates.
  • There is no user-facing force-update function or per-extension allowlist in version 3.5.
  • The 3.4 to 3.5 updater migration is empty; automatic updates still depend on reviewed upgrade scripts for each installed extension.

Upstream 3.6.0

Upstream 3.6.0 adds PostgreSQL 19 support and uses DuckDB 1.5.6. Deploy matching libraries and pgduck_server, keep pg_extension_base preloaded, restart loaded processes and apply the supplied SQL updates to version 3.6. This component’s 3.5-to-3.6 SQL migration is empty; the matching runtime and its dependencies still need the coordinated release upgrade.

Upstream 3.7.0

The current reviewed release is 3.7.0 and this component’s control version is 3.7. Earlier version sections describe historical behavior and package examples. Upgrade the complete family with matching libraries and pgduck_server, restart processes holding old code, and apply the supplied extension updates to SQL version 3.7. This does not establish package availability.

Table reader