how to get timescaledb version of the database

Question

According to the docs:

TimescaleDB supports having different extension versions on different databases within the same PostgreSQL instance.

I can get the installed version of the instance with this SQL command:

SELECT extversion
FROM pg_extension
where extname = 'timescaledb';

But how can I get the version of a specific database?

Notes:

• I prefer to get the version via sql (not psql)
• the reason for this is that we'd like to know if we must update the datebase (i.e. execute ALTER EXTENSION timescaledb UPDATE;) or not

Maurer

SELECT extversion FROM pg_extension gives you the installed extensions for the current connected database. Try this. 1. Connect to db1; 2. Create extension create extension postgres_fdw; 3. execute your query, you'll see postgres_fdw with it's version; 4. create new db2; 5. connect to db2 and execute the query, you will not see postgres_fdw in the list of extensions – Primogenitor

Answer 1

Turns out, that my assumption was wrong:

SELECT extversion
FROM pg_extension
where extname = 'timescaledb';

returns the version of the currently connected database.

Here is how we can find out the versions:

SELECT default_version, installed_version FROM pg_available_extensions
where name = 'timescaledb';

• default_version: is the version installed in the PostgreSQL server instance
• installed_version: is the version that the current database is using

Example:

When the extension used by the database is not up-to-date, the versions do not match:

SELECT default_version, installed_version FROM pg_available_extensions
where name = 'timescaledb';

 default_version | installed_version 
-----------------+-------------------
 1.4.1           | 1.4.0

now update the extension

• connect via psql -X -U USER -W -D DBNAME
• execute ALTER EXTENSION timescaledb UPDATE;
• now the versions are the same

Maurer

This is exactly what I would have suggested! Always nice when you answer your own questions :) – Cadge

yes, sometimes it only takes a comment (thanks @Blagoj Atanasovski) and things become clearer :) – Maurer

Running \dx in psql is always an easy way to get what versions of extensions are installed in postgres – Outherod

What if after ALTER EXTENSION timescaledb UPDATE; installed version don't change? – Andrien

@MrJedi you should ask a new question - and include the info what the default/installed version are before you try to update. – Maurer

Answer 2

You should be using \dx, as it shows the actually loaded extension version, check the "main" database postgres first:

$ psql
\dx timescaledb
\c some_database
\dx timescaledb

the output looks like this:

                                    List of installed extensions
    Name     | Version | Schema |                            Description                            
-------------+---------+--------+-------------------------------------------------------------------
 timescaledb | 2.9.3   | public | Enables scalable inserts and complex queries for time-series data

or

psql -d database_name -c "\dx timescaledb"

pg_available_extensions will show available extension, however some database could be been created with much older extension version.

psql -c "SELECT default_version, installed_version FROM pg_available_extensions where name = 'timescaledb';"
 default_version | installed_version 
-----------------+-------------------
 2.13.0          | 2.10.1

Before extension upgrade you should check the supported paths and release notes, for your version, e.g.:

SELECT * FROM pg_extension_update_paths('timescaledb') WHERE source='2.9.3';

this corresponds to sql scripts on disk, where you can find all upgrade steps:

l /usr/share/postgresql/14/extension/timescaledb--2.9*

Extension upgrade can be done automatically (to the latest version):

psql  -X -c "ALTER EXTENSION timescaledb UPDATE;"

or to a specific version, assuming the upgrade path exists:

psql -X -c "ALTER EXTENSION timescaledb UPDATE TO '2.13.0';"

Loftis