Major TimescaleDB upgrades

A major upgrade is when you upgrade from one major version of TimescaleDB, to the next major version. For example, when you upgrade from TimescaleDB 1, to TimescaleDB 2.

For upgrading within your current major version, for example upgrading from TimescaleDB 2.5 to TimescaleDB 2.6, see the minor upgrades section.

You can upgrade your self-hosted Timescale installation in-place. This means that you do not need to dump and restore your data. However, it is still important that you plan for your upgrade ahead of time.

Before you upgrade:

• Read the release notes for the Timescale version you are upgrading to.
• Check which PostgreSQL version you are currently running. You might need to upgrade to the latest PostgreSQL version before you begin your Timescale upgrade.
• Perform a backup of your database. While Timescale upgrades are performed in-place, upgrading is an intrusive operation. Always make sure you have a backup on hand, and that the backup is readable in the case of disaster.

Additionally, before you begin this major upgrade, read the changes in TimescaleDB 2 section. This section provides a more detailed look at the major changes in TimescaleDB 2. It also includes information about how these major changes impact the way your applications and scripts interact with the TimescaleDB API.

When you upgrade from TimescaleDB 1, to TimescaleDB 2, scripts automatically configure updated features to work as expected with the new version. However, not everything works in exactly the same way as previously.

Before you begin this major upgrade, check the database log for errors related to failed retention policies that could have occurred in TimescaleDB 1. You can either remove the failing policies entirely, or update them to be compatible with your existing continuous aggregates.

If incompatible retention policies are present when you perform the upgrade, the ignore_invalidation_older_than setting is automatically turned off, and a notice is shown.

For more information about changes to continuous aggregates and data retention policies, see the release notes.

To perform this major upgrade:

1. Export your TimescaleDB 1 policy settings
2. Upgrade the TimescaleDB extension
3. Verify updated policy settings and jobs

When you perform the upgrade, new policies are automatically configured based on your current configuration. This upgrade process allows you to export your policy settings before performing the upgrade, so that you can verify them after the upgrade is complete.

This upgrade uses the PostgreSQL ALTER EXTENSION function to upgrade to the latest version of the TimescaleDB extension. TimescaleDB supports having different extension versions on different databases within the same PostgreSQL instance. This allows you to upgrade extensions independently on different databases. Run the ALTER EXTENSION function on each database to upgrade them individually.

Verifying updated policy settings and jobs

1. Use this query to verify the continuous aggregate policy jobs:

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

   Copy

2. Verify the information for each policy type that you exported before you upgraded. For continuous aggregates, take note of the config information to verify that all settings were converted correctly.

3. Verify that all jobs are scheduled and running as expected using the new timescaledb_information.job_stats view:

SELECT * FROM timescaledb_information.job_stats
  WHERE job_id = 1001;

Copy

The output looks like this:

-[ RECORD 1 ]----------+------------------------------
hypertable_schema      | _timescaledb_internal
hypertable_name        | _materialized_hypertable_2
job_id                 | 1001
last_run_started_at    | 2020-10-02 09:38:06.871953-04
last_successful_finish | 2020-10-02 09:38:06.932675-04
last_run_status        | Success
job_status             | Scheduled
last_run_duration      | 00:00:00.060722
next_scheduled_run     | 2020-10-02 10:38:06.932675-04
total_runs             | 1
total_successes        | 1
total_failures         | 0

Copy