DBAtlas · Database operations atlas

Find extensions with pending updates and update them

PostgreSQLExtensions

Command

SELECT e.extname,
       e.extversion AS installed,
       a.default_version AS available,
       n.nspname AS schema_name,
       format('ALTER EXTENSION %I UPDATE;', e.extname) AS fix
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extversion IS DISTINCT FROM a.default_version
ORDER BY e.extname;

-- Is there an update path?
SELECT * FROM pg_extension_update_paths('pgstattuple') WHERE path IS NOT NULL;

ALTER EXTENSION pgstattuple UPDATE;

What it does

Compares each extension's installed version in the database with the default version of the package on the server, and generates the ALTER EXTENSION command for those with pending updates.

When to use it

After PostgreSQL minor/major upgrades and extension package updates, and during periodic maintenance.

Caution

Upgrading the OS package does not update the SQL side automatically; ALTER EXTENSION ... UPDATE must be run in every database separately (especially important after pg_upgrade). The update script may take locks and alter objects (e.g. PostGIS); test it first and read the release notes. To move to a specific version: ALTER EXTENSION pgstattuple UPDATE TO '1.5'; Some extensions (e.g. pg_stat_statements) also require a restart to reload the shared library.

Open in DBAtlas →

Related