Updating TimescaleDB to 2.0

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

    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, , 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 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.

    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.

    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

    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 setting will automatically be disabled during the upgrade and a notice provided.

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

    As discussed in the 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

    Continuous Aggregate Stats

      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 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:

      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
      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:

      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.

      2.   WHERE job_id = 1001;
      3. -[ RECORD 1 ]----------+------------------------------
      4. hypertable_schema      | _timescaledb_internal
      5. hypertable_name        | _materialized_hypertable_2
      6. job_id                 | 1001
      7. last_run_started_at    | 2020-10-02 09:38:06.871953-04
      8. last_successful_finish | 2020-10-02 09:38:06.932675-04
      9. last_run_status        | Success
      10. job_status             | Scheduled
      11. last_run_duration      | 00:00:00.060722
      12. next_scheduled_run     | 2020-10-02 10:38:06.932675-04
      13. total_runs             | 1