PostgreSQL: Update extensions

PostgreSQL is a very great RDBMS, especially when you need extensions. But installing an extension also means you install a specific version (mostly the latest one).

When you regularly update your PostgreSQL binaries, i. e. when doing it from within the OS update, or when you're using something like Docker, your PostgreSQL cluster keeps up to date. But your extensions do not!

You can see it in the PostgreSQL log files:

2025-06-06 10:52:32.010 UTC [35] LOG:  the "timescaledb" extension is not up-to-date
2025-06-06 10:52:32.010 UTC [35] HINT:  The most up-to-date version is 2.20.2, the installed version is 2.18.2.

You can see the version also from within the database:

postgres=# SELECT extname,extversion FROM pg_extension;
   extname   | extversion
-------------+------------
 plpgsql     | 1.0
 timescaledb | 2.18.2
(2 rows)

Outdated extensions are - like other software components - a risk for stability and security. But blindly updating an extension to the latest version could also be a problem - especially when your application needs a specific version. So be sure that updating an extension will not break your application - maybe by using a test or quality system.

When you are sure the extension can be updated, you can do it like this:

postgres=# alter extension timescaledb update;
ALTER EXTENSION

If your session was using the extension before, you'll get an error like this:

postgres=# alter extension timescaledb update;
ERROR:  extension "timescaledb" cannot be updated after the old version has already been loaded
HINT:  Start a new session and execute ALTER EXTENSION as the first command. Make sure to pass the "-X" flag to psql.

It's quite easy to find out how to solve this: just open another psql session, or re-login, and try again:

postgres=# alter extension timescaledb update;
ALTER EXTENSION

You can also check your extensions from within the database:

postgres=# SELECT extname,extversion FROM pg_extension;
   extname   | extversion
-------------+------------
 plpgsql     | 1.0
 timescaledb | 2.20.2
(2 rows)

If you want to do some monitoring over your PostgreSQL clusters regarding extensions, you could use the view pg_available_extensions, which nut only shows which extensions can be installed, but also what the latest version is. And also which version is installed at the moment:

postgres=# select name,default_version,installed_version from pg_available_extensions where installed_version is not null;
    name     | default_version | installed_version
-------------+-----------------+-------------------
 plpgsql     | 1.0             | 1.0
 timescaledb | 2.20.2          | 2.20.2
(2 rows)

So if your extension is outdated, it would look like this:

postgres=# select name,default_version,installed_version from pg_available_extensions where default_version <> installed_version;
    name     | default_version | installed_version
-------------+-----------------+-------------------
 timescaledb | 2.20.2          | 2.18.2
(1 row)

And if everything's up to date, it looks like this:

postgres=# select name,default_version,installed_version from pg_available_extensions where default_version<>installed_version;
 name | default_version | installed_version
------+-----------------+-------------------
(0 rows)