| We recently completed the migration of a self-hosted 3 TB PostgreSQL database from version 12 to 16, transitioning from Ubuntu 18 to Ubuntu 22. Concurrently, we had to upgrade various extensions, most notably Timescale, for which a compatible version did not exist across all scenarios. We performed the upgrade by updating a replica in the following sequence: - Start: PG12, Ubuntu 18, TS2.9 - Step 1: Set up a read-only replica with PG12 on Ubuntu 22, maintaining TS2.9. - Step 1.5: Enter maintenance mode and halt all services. - Step 2: Detach the the read-only replica, upgrading from PG12 to PG15 on Ubuntu 22 with TS2.9. - Step 3: Upgrade from PG15 with TS2.9 to TS2.13 on Ubuntu 22. - Step 4: Upgrade from PG15 to PG16 on Ubuntu 22 with TS2.13. - Step 4.5 : Reconnect services to the new database server, resume all services, and exit maintenance mode. All the database upgrade steps were well-tested and automated using Ansible. Nonetheless, we did encounter an issue that had not arisen during testing. This extended our downtime to approximately half an hour, which, for our use case, was perfectly acceptable. Employing logical replication could have mitigated the last-minute surprise. So we will consider this approach for our next upgrade cycle. |
We did look into using logical replication to reduce the downtime of the upgrade, but because database schema and DDL commands aren't replicated, it seems that it isn't recommended with Timescale in the loop.. (I suppose the underlying schema changes that Timescale needs to make under the hood are mostly a function of your hypertable chunk sizing and what your incoming writes look like, so this could be planned around / timed well, but we felt it added too much potential complexity & risk compared to simply opting for a small maintenance window while pg_upgrade completed).