29

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
TmTron
  • 17,012
  • 10
  • 94
  • 142
  • 1
    `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 – Blagoj Atanasovski Aug 19 '19 at 10:34

1 Answers1

50

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
TmTron
  • 17,012
  • 10
  • 94
  • 142
  • 2
    This is exactly what I would have suggested! Always nice when you answer your own questions :) – davidk Aug 19 '19 at 17:02
  • 1
    yes, sometimes it only takes a comment (thanks @Blagoj Atanasovski) and things become clearer :) – TmTron Aug 19 '19 at 17:07
  • 3
    Running `\dx` in psql is always an easy way to get what versions of extensions are installed in postgres – technogeek1995 Sep 05 '19 at 19:11
  • What if after ALTER EXTENSION timescaledb UPDATE; installed version don't change? – Mr Jedi Mar 10 '21 at 12:58
  • @MrJedi you should ask a new question - and include the info what the default/installed version are before you try to update. – TmTron Mar 10 '21 at 13:34