Azure Database for PostgreSQL flexible server upgrade from 14 to 15 with timescaledb extension

Question

Using Azure Database for PostgreSQL flexible server, version 14 with the TimescaleDB extension version 2.10.0. We are planning to perform an in-place upgrade to PostgreSQL version 15. According to Azure documentation, TimescaleDB version 2.10.0 is compatible with PostgreSQL 15, but not with PostgreSQL 16.

Our problem is that when we attempt to upgrade to version 15, we receive an error indicating that the TimescaleDB extension does not support the upgrade. We found on the Microsoft page that in-place upgrades are not supported (https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-major-version-upgrade).

Is there any other solution to perform upgrade without a lengthy pg_dump and pg_restore process? Our database is over 100 GB, and using pg_dump and pg_restore would take approximately a week and is not acceptable because of our SLA to our customers.

Answer 1

Please check this link:

https://docs.timescale.com/self-hosted/latest/upgrades/upgrade-pg/

https://docs.timescale.com/timescaledb/latest/how-to-guides/upgrades/upgrade-pg/

Because of the TimescaleDB upgradibility and its dependency on PG version, migration is tricky We have two solutions as of now:

Solution#1

1. On the source server, before migration convert all hypertables to normal tables and disable TimescaleDB extension.
2. Use Flex Migration Service Tool to migrate to target (Offline)
3. After migration to target server, Enable TimescaleDB extension on targer and convert these tables back to Hypertables

Solution#1 in detailed steps

( Need to take customer approval )**

Customer side they need to have atleast a buffer storage space of the size of largest hypertable in source server database they want to migrate. If not, they need to increase the storage on source server. For this need customer approval

Download and install PostgreSQL, pgAdmin for your OS

https://www.postgresql.org/download/ https://www.pgadmin.org/download/pgadmin-4-windows/

Creation of a PITR of the source server and once it is completed goto server parameters blade and search for shared_preload_libraries and then select timescaledb there and click on save. Once save got successful go to overview blade and click on restart. Once server restart is successful then proceed to next step

Note: Creation of PITR and/or allocation of additional storage will incur a cost

Open PgAdmin in your machine and connect to PITR of source server database and open query tool

Execute this query

select string_agg(CONCAT('SELECT create_hypertable( ', '''',a.schema_name,'.',a.table_name,'''',', ' , '''',b.column_name,'''',', migrate_data => true, ','chunk_time_interval => INTERVAL ','''',b.interval_length/3600000000,' hours','''',');'),chr(10)) from _timescaledb_catalog.hypertable a join _timescaledb_catalog.dimension b on b.hypertable_id = a.id;

Post execution you will get output like this copy this into notepad

It will be like this. Eg: "SELECT create_hypertable( 'public.data', 'timestamp', migrate_data => true, chunk_time_interval => INTERVAL '168 hours'); SELECT create_hypertable( 'public.conditions1', 'time', migrate_data => true, chunk_time_interval => INTERVAL '168 hours')"

Remove quotation marks from starting and ending of the above string and save it in notepad

Go to PgAdmin query tool and copy below contents into query tool and execute on PITR of source server database

CREATE OR REPLACE FUNCTION timescaleprescript() RETURNS void AS $$ DECLARE schemaname_list TEXT[]; tablename_list TEXT[]; BEGIN SELECT array_agg(a.table_name) INTO tablename_list FROM _timescaledb_catalog.hypertable a; SELECT array_agg(a.schema_name) INTO schemaname_list FROM _timescaledb_catalog.hypertable a; FOR i IN 1 .. array_upper(tablename_list, 1) LOOP execute format ('CREATE TABLE %s."timescalepretable" (LIKE %s."%s" INCLUDING ALL);', schemaname_list[i], schemaname_list[i],tablename_list[i]); BEGIN execute 'INSERT INTO ' || schemaname_list[i] || '."timescalepretable" (SELECT * FROM "'||tablename_list[i] ||'");'; END; BEGIN execute 'DROP TABLE IF EXISTS "'|| tablename_list[i]|| '"'; END; execute format ('ALTER TABLE %s."timescalepretable" RENAME TO "%s";', schemaname_list[i], tablename_list[i]); END LOOP; END; $$ LANGUAGE plpgsql;

SELECT timescaleprescript();

This will help in converting hypertables to normal tables on source server and we can proceed with the migration.

Once Step 5 completed execute drop extension timescaledb cascade; on PITR of source server database

Go to portal and trigger FMS migration from Migration blade on target server

Once Step 7 got completed and migration is successful, add timescaledb to azure.extensions, shared_preload_libraries in server parameters blade of target server and click on save and restart. Once restart is successful and target server is available, Open PgAdmin in your machine and connect to target server database, open query tool and execute create extension timescaledb; and also paste the output you saved in notepad from Step 4 and execute

This will help in converting normal tables to hypertables on target server and timescaledb migration is completed.

On successful completion of Step 8 go to PgAdmin tool and run below query on PITR of source server database

drop function if exists timescaleprescript();

This will help in dropping the function that we created in Step 5 on PITR of source server database

Delete PITR server instance

Note: If above solution is not okay(hyper tables -> normal tables -> hyper tables), then explore solution2

Solution#2

1. Make sure the TimescaleDB version on source server is 1.7.4.
2. If target is FSPG 11 or 12, perform migration directly to the target server using Dump & Restore
3. If target is FSPG 13 or 14, (Not supported currently) 3.1 First migrate to FSPG12 3.2 Perform TimescaleDB extension upgrade 3.3 Perform major version upgrade of PG

Solution#2 in detailed steps.

Note: Best recommended way is upgrade to pg version 12 flex

1. Check the current version of timescaledb you are using on SSPG 9.6, 10, 11 Run this to get the version on source database select * from pg_extension where extname = 'timescaledb';
2. Contact engineering team to get timescaledb extension version upgraded to 1.7.4
3. Engineering team need to JIT to SSPG node and then login as super user and connect to postgres using –x flag run these commands psql.exe "host=127.0.0.1 port=<port> dbname=postgres user=azure_superuser sslmode=verify-ca sslcert=D:/x509/super.crt sslkey=D:/x509/super.key sslrootcert=D:/x509/root.crt" -x ALTER EXTENSION timescaledb UPDATE; select * from pg_extension where extname = 'timescaledb'; You need to see 1.7.4 as upgraded version
4. Create target database on target server. Perform allowlisting of timescaledb from portal by adding it to shared_preload_libraries, azure.extensions server parameter and restart target server
5. Download and install PostgreSQL for your OS https://www.postgresql.org/download/
6. Open cmd in administrator mode and execute cd C:\Program Files\PostgreSQL\14\bin
7. Execute pg_dump for SSPG 11 pg_dump -v -h <sourceservername> -p <port> -U <username> -Fc -f <filename>.bak <databasename>
8. Create timescaledb extension on target database create extension timescaledb with version '1.7.4'; Execute select timescaledb_pre_restore(); on target database
9. Execute pg_restore for FSPG 12 pg_restore -v -h <targetservername> -p <port> -U <username> -Fc -d <databasename> <filename>.bak
10. Execute select timescaledb_post_restore(); on target database and contact engineering team to get timescaledb extension version upgraded to 2.5.1
11. Engineering team need to get JIT to target server VM and JIT to target server subscription and then login as super user and connect to postgres using –x flag run these commands docker exec -it PostgreSQL psql -U azuresu -d postgres -x ALTER EXTENSION timescaledb UPDATE; select * from pg_extension where extname = 'timescaledb'; You need to see 2.5.1 as upgraded version and timescaledb migration is completed to FSPG 12

Comparison	Solution#1	Solution#2	Notes
Downtime	Till the tables are converted to normal tables.	Entire migration process is offline
Resources	Additional storage to temporarily clone the hypertable content into normal table	Additional storage for placement of Dump file
Compatibility	Migration from any SSPG version to any FSPG version is possible	Supported until Flex Pg 12	CX need to make sure their applications are compatible with TimescaleDB version on target
Complexity	Simple. Only one pre-requisite	Very complex with multiple downtimes

OR

follow:

1. Creation of a PITR of the source
2. How much extra storage they have to allocate on the PITR source if any?
3. Finally deleting PITR instance after the migration

Note: Creation of PITR and/or allocation of additional storage will incur a cost Thanks