Cart
Cart

How to update TimescaleDB

What awaits us?

TimescaleDB is a popular extension for PostgreSQL that enables efficient processing of time series. When updating TimescaleDB, it is important to follow proper procedures to maintain the stability and functionality of databases that use this extension. This guide will walk you through the steps of updating TimescaleDB.

Checking the current version of TimescaleDB

Before you start the upgrade, check which version of TimescaleDB you currently have installed and active in your databases.

A command to check the installed version of the TimescaleDB extension:

sudo -u postgres psql -c "SELECT default_version, installed_version FROM pg_available_extensions WHERE name = 'timescaledb';"

This query will display the versions that are available and currently installed. For example, you may see version 2.11.2 installed.

TimescaleDB package updates

To update the TimescaleDB packages themselves, including additional components such as TimescaleDB tools and TimescaleDB loader, use the following command:

sudo dnf upgrade timescaledb-*

This command will ensure that all TimescaleDB components you have installed (including tools and loader) are updated to the latest version. This will allow you to take full advantage of all the new features and enhancements that TimescaleDB offers.

Identify databases that use TimescaleDB

The next step is to identify the databases that use the TimescaleDB extension. You can do this by querying PostgreSQL:

sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" | 
while read dbname; do 
  sudo -u postgres psql -d "$dbname" -c "SELECT '$dbname' as database, extname as extension FROM pg_extension WHERE extname = 'timescaledb';"; 
done

This command scans all databases (except template databases) and checks if they contain the TimescaleDB extension. If so, it lists the database name and extension.

This means that the TimescaleDB extension is used in the zabbix database, as seen in the result line (1 row). This output confirms that the zabbix database has an active TimescaleDB extension and therefore needs to be included in the update process.

Update TimescaleDB extension in databases

After updating the packages and identifying the databases, you need to update the TimescaleDB extensions in each database. To do this, use the following SQL statement for each database that uses TimescaleDB:

sudo -u postgres psql -d <your_database> -c "ALTER EXTENSION timescaledb UPDATE;"

This command updates the extension to the latest version in a specific database. Be sure to replace <your_database> with the name of the database you want to update.

If you encounter an error during the update with the message:

ERROR: loader version out-of-date
HINT: Please restart the database to upgrade the loader version.

You need to restart the PostgreSQL server. Be sure to replace the version number in the command with your current version of the PostgreSQL server:

sudo systemctl restart postgresql-<version>.service

For example, for PostgreSQL 16, use:

sudo systemctl restart postgresql-16.service

After the server restarts, repeat the command to update the TimescaleDB extension:

sudo -u postgres psql -d <your_database> -c "ALTER EXTENSION timescaledb UPDATE;"

This will ensure that the TimescaleDB extension is successfully updated and fully functional.

Restarting PostgreSQL server after an update

After successfully updating the TimescaleDB extension, it is recommended to restart the PostgreSQL server to ensure that all changes are fully applied and the new component versions are loaded correctly.

Be sure to replace the version number in the command with your current PostgreSQL server version:

sudo systemctl restart postgresql-<version>.service

For example, for PostgreSQL 16, use:

sudo systemctl restart postgresql-16.service

This step will ensure that any changes that were made during the update will be fully effective and the database server will be ready for further operation.

Automatic update of TimescaleDB extension in all databases (optional)

If you have multiple databases that use the TimescaleDB extension and you want to automate the process of updating them, you can use the following command. This command will update the TimescaleDB extension automatically on all databases where the extension is currently installed.

Automatic update of TimescaleDB extension

The following command scans all databases on the server, checks to see if the TimescaleDB extension is installed, and if so, updates it:

sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" | 
while read dbname; do 
  if sudo -u postgres psql -d "$dbname" -tAc "SELECT 1 FROM pg_extension WHERE extname = 'timescaledb'" | grep -q 1; then 
    sudo -u postgres psql -d "$dbname" -c "ALTER EXTENSION timescaledb UPDATE;"; 
  Fi; 
done

What this command does:

  • Database selection: selects all databases except template databases (template0 and template1).
  • Extension check: for each database, it first checks whether the TimescaleDB extension is installed.
  • Extension Update: If the TimescaleDB extension is installed, it will update it to the latest version.

Copy this complete command and run it in the terminal. This command will ensure that the update only occurs in databases where the TimescaleDB extension is currently installed.

If you see the following error when you try to update the TimescaleDB extension:

ERROR: loader version out-of-date
HINT: Please restart the database to upgrade the loader version.

It is necessary to restart the PostgreSQL server and repeat the action. Be sure to replace the version number in the command with your current version of the PostgreSQL server:

sudo systemctl restart postgresql-<version>.service

For example, for PostgreSQL 15, use:

sudo systemctl restart postgresql-15.service

After restarting the PostgreSQL server and running the update command again, you should see a confirmation:

ALTER EXTENSION

This will confirm that the TimescaleDB extension update was successful.

Restart applications (optional)

After the update, it is recommended to restart applications that connect to TimescaleDB databases. This will ensure that the applications load the new extension version correctly and avoid potential compatibility issues.

sudo systemctl restart your_application

Functionality verification and optimization

After updating the extension and restarting the applications, check that everything is working as expected. We recommend running basic queries and checking that the application works without any problems.

In addition, you can perform other optimizations, such as reindexing, if there have been significant changes in data structures or indexes.

Other Recommendations

  • Testing on a test environment: Before updating on a production environment, we recommend that you perform a test update on a test server to minimize the risk of problems in production.
  • Performance monitoring: After the update, monitor TimescaleDB performance to see if any changes have occurred and adjust performance settings as needed.
  • Backup before update: Even if it is a minor update, we recommend backing up your databases before making changes so that you can roll back in case of problems.
  • Browsing the changelog: Learn about the new features and changes in the new version of TimescaleDB so you know what new features you can take advantage of and what needs to be adjusted.

This will ensure that the TimescaleDB upgrade goes smoothly and that your databases remain fully functional and optimized.

×Shopping Cart

Your cart is empty.