Updating TimescaleDB to 2.0

Use these instructions to update TimescaleDB 1.x to TimescaleDB 2.0

WARNING:These instructions are only for upgrading TimescaleDB 1.x to TimescaleDB 2.0 If you need to upgrade your existing TimescaleDB 1.x to a newer version in the 1.x release line (eg. 1.7.2 to 1.7.4), please see Update TimescaleDB 1.x.

TimescaleDB Release Compatibility

TimescaleDB 2.0 currently supports the following PostgreSQL releases. If you are not currently running a compatible release, please upgrade before updating TimescaleDB.

TimescaleDB ReleaseSupported PostgreSQL Release
2.0-RC1+11, 12

TIP:If you need to upgrade PostgreSQL first, please see our documentation.

Notice of breaking changes from TimescaleDB 1.3+

TimescaleDB 2.0 supports in-place updates just like previous releases. During the update, scripts will automatically configure updated features to work as expected with TimescaleDB 2.0.

Because this is our first major version release in two years, however, we’re providing additional guidance to help you ensure the update completes successfully and everything is configured as expected (and optimized for your workload). In particular, settings related to Continuous Aggregates, compression, and data retention have been modified to provide greater configuration transparency and flexibility, therefore we highly recommend verifying that these settings were migrated correctly.

Before completing the upgrade, we encourage you to read Changes in TimescaleDB 2.0 for a more detailed look at the major changes in TimescaleDB 2.0 and how they impact the way your applications and scripts interact with the API.

Prerequisites

PostgreSQL Compatibility

TimescaleDB 2.0 is not compatible with PostgreSQL 9.6 or 10. If your current PostgreSQL installation is not at least version 11, please upgrade PostgreSQL first. Depending on your current PostgreSQL version and installed TimescaleDB release, you may have to perform multiple upgrades because of compatibility restrictions.

For example, if you are currently running PostgreSQL 10 and TimescaleDB 1.5, the recommended upgrade path to PostgreSQL 12 and TimescaleDB 2.0 would be:

  1. Update TimescaleDB 1.5 to TimescaleDB 1.7 on PostgreSQL 10
  2. Upgrade PostgreSQL 10 to PostgreSQL 12 with TimescaleDB 1.7 installed
  3. Update TimescaleDB 1.7 to TimescaleDB 2.0 on PostgreSQL with the instructions below

TIP:Whenever possible, prefer the most recent supported version, PostgreSQL 12. Please see our Upgrading PostgreSQL guide for help.

Fix Continuous Aggregate Errors Before Upgrading

Before starting the upgrade to TimescaleDB 2.0, we highly recommend checking the database log for errors related to failed retention policies that were occurring in TimescaleDB 1.x and then either remove them or update them to be compatible with existing continuous aggregates. Any remaining retention policies that are still incompatible with the ignore_invalidation_older_than setting will automatically be disabled during the upgrade and a notice provided.

TIP:Read more about changes to continuous aggregates and data retension policies here.

Update TimescaleDB

Step 1: Verify TimescaleDB 1.x Policy Settings (Optional)

As discussed in the Changes to TimescaleDB 2.0 document, the APIs and setting names that configure various policies are changing. The update process below will automatically configure new policies using your current configurations in TimescaleDB 1.x. If you would like to verify the policy settings after the update is complete, we suggest querying the informational views below and saving the output so that you can refer to it once the update is complete.

Execute the following SQL to save current settings for Continuous Aggregates and other policies to CSV using psql. If you use an IDE like pgAdmin or DBeaver, save the output to CSV or another appropriate format to inspect later.

Policy Stats

  1. \COPY timescaledb_information.policy_stats TO ‘policy_stats.csv’ csv header

Continuous Aggregate Stats

  1. \COPY timescaledb_information.continuous_aggregates_stats TO ‘continuous_aggregates_stats.csv’ csv header

Drop Chunk Policies

  1. \COPY timescaledb_information.drop_chunks_policies TO ‘drop_chunk_policies.csv’ csv header

Reorder Policy Stats

  1. \COPY timescaledb_information.reorder_policies TO ‘reorder_policies.csv’ csv header

Step 2: Install and Update TimescaleDB Extension

Software upgrades use PostgreSQL’s ALTER EXTENSION support to update to the latest version. TimescaleDB supports having different extension versions on different databases within the same PostgreSQL instance. This allows you to update extensions independently on different databases. The upgrade process involves three-steps:

  1. We recommend that you perform a backup of your database via pg_dump.
  2. Install the latest version of the TimescaleDB extension.
  3. Execute the following psql command inside any database that you want to update:
  1. ALTER EXTENSION timescaledb UPDATE;

WARNING:When executing ALTER EXTENSION, you should connect using psql with the -X flag to prevent any .psqlrc commands from accidentally triggering the load of a previous TimescaleDB version on session startup. It must also be the first command you execute in the session.

This will upgrade TimescaleDB to the latest installed version, even if you are several versions behind.

After executing the command, the psql \dx command should show the latest version:

  1. \dx timescaledb
  3.     Name     | Version |   Schema   |                             Description
  4. -------------+---------+------------+---------------------------------------------------------------------
  5.  timescaledb | 2.0.0   | public     | Enables scalable inserts and complex queries for time-series data
  6. (1 row)

Step 3: Verify Updated Policy Settings and Jobs

All settings and information previously accessed through separate stats informational views have now been centralized to a common jobs view for all types of policies. If you wish to verify that the settings were moved correctly, query the timescaledb_information.jobs view to verify that each policy was correctly moved and enabled based on your TimescaleDB 1.x setup.

In the example below, we query for all continuous aggregate policy jobs. Compare the names and settings to the values of the data exported from timescaledb_information.continuous_aggregates:

  1. SELECT * FROM timescaledb_information.jobs
  2.   WHERE application_name LIKE 'Refresh Continuous%';
  4. -[ RECORD 1 ]-----+--------------------------------------------------
  5. job_id            | 1001
  6. application_name  | Refresh Continuous Aggregate Policy [1001]
  7. schedule_interval | 01:00:00
  8. max_runtime       | 00:00:00
  9. max_retries       | -1
  10. retry_period      | 01:00:00
  11. proc_schema       | _timescaledb_internal
  12. proc_name         | policy_refresh_continuous_aggregate
  13. owner             | postgres
  14. scheduled         | t
  15. config            | {"start_offset": "20 days", "end_offset": "10 
  16. days", "mat_hypertable_id": 2}
  17. next_start        | 2020-10-02 12:38:07.014042-04
  18. hypertable_schema | _timescaledb_internal
  19. hypertable_name   | _materialized_hypertable_2

Verify the information for each policy type that was exported from TimescaleDB 1.x using the specific listing of jobs in this view. For continuous aggregates, take special note of the config information to verify that all settings were converted correctly given the notes in the Updating existing continuous aggregates section of our migration document.

Likewise, you can now verify that all jobs scheduled and running as expected with the new timescaledb_information.job_stats view. Given the continuous aggregate job above, querying the new job_stats view might return information similar to the following.

  1. SELECT * FROM timescaledb_information.job_stats
  2.   WHERE job_id = 1001;
  4. -[ RECORD 1 ]----------+------------------------------
  5. hypertable_schema      | _timescaledb_internal
  6. hypertable_name        | _materialized_hypertable_2
  7. job_id                 | 1001
  8. last_run_started_at    | 2020-10-02 09:38:06.871953-04
  9. last_successful_finish | 2020-10-02 09:38:06.932675-04
  10. last_run_status        | Success
  11. job_status             | Scheduled
  12. last_run_duration      | 00:00:00.060722
  13. next_scheduled_run     | 2020-10-02 10:38:06.932675-04
  14. total_runs             | 1
  15. total_successes        | 1
  16. total_failures         | 0