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

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