Major TimescaleDB upgrades

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.

Plan your upgrade

You can upgrade your on-premise TimescaleDB 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 TimescaleDB 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 TimescaleDB upgrade.
Perform a backup of your database. While TimescaleDB 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.

Breaking changes

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.

Upgrade TimescaleDB to the next major version

To perform this major upgrade:

Export your TimescaleDB 1 policy settings
Upgrade the TimescaleDB extension
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.

Exporting TimescaleDB 1 policy settings

At the psql prompt, use this command to save the current settings for your policy statistics to a .csv file:
```
COPY (SELECT * FROM timescaledb_information.policy_stats)
TO policy_stats.csv csv header
```

Use this command to save the current settings for your continuous aggregates to a .csv file:

COPY (SELECT * FROM timescaledb_information.continuous_aggregate_stats)
TO continuous_aggregate_stats.csv csv header

Use this command to save the current settings for your drop chunk policies to a .csv file:

COPY (SELECT * FROM timescaledb_information.drop_chunks_policies)
TO drop_chunk_policies.csv csv header

Use this command to save the current settings for your reorder policies to a .csv file:

COPY (SELECT * FROM timescaledb_information.reorder_policies)
TO reorder_policies.csv csv header

Upgrading the TimescaleDB extension

Connect to psql using the -X flag. This prevents any .psqlrc commands from accidentally triggering the load of a previous TimescaleDB version on session startup.
At the psql prompt, upgrade the TimescaleDB extension. This must be the first command you execute in the current session:
```
ALTER EXTENSION timescaledb UPDATE;
```
Check that you have upgraded to the latest version of the extension with the \dx command. The output should show the upgraded version number.
```
\dx timescaledb
```

Verifying updated policy settings and jobs

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

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


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