Hi, I should upgrade a production Windows server 2012R2 with Postgresql 11 and Timescale 1.6 to a newer version as the operating system is obsolete, but I cannot find an intermediate timescale installer compatible with PG 11 to perform the upgrade. What can I do? I am stuck.
Thanks in advance.
Hi @giaza, not sure about the complexity of your schema, but try to just dump the schema and reload on a new version, then dump the data into CSV and use the timescaledb-parallel-copy to reload it in a new database.
You may even dump the files using a “WHERE” clause to get a CSV per year or something easier to load in case the database is very large.
Hi Jonatasdp. I tried restoring the dump on the same postgres version and the same timescaledb version and it worked without errors. So the db dump is fine.
Then I tried recovering the same dump on a postgres vers. 14 with a timescaledb version 2.10.1 installed.
I installed the new extension on the new db and started the restore, but it said that the restore operation failed with this error:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3840; 0 16467 TABLE DATA hypertable postgres
pg_restore: error: could not execute query: ERROR: la column “compressed” of the relation “hypertable” doesn’t exist
Command was: COPY _timescaledb_catalog.hypertable (id, schema_name, table_name, associated_schema_name, associated_table_prefix, num_dimensions, chunk_sizing_func_schema, chunk_sizing_func_name, chunk_target_size, compressed, compressed_hypertable_id) FROM stdin;
Maybe I should Try without installing the timescaledb extension first?
But how could execute the schema updates then?
I do not know anything about the schema as It is created by an installed program and it seems very complex.
Thanks for your help
It seems incompatible extension versions. Indeed you should have exactly the same version of timescaledb to upgrade it.
You should restore pg using the latest available option for 1.6. Once PostgreSQL is updated, you can try to upgrade the timescaledb extension version.
This Is exactily the problem. The last Windows version that can be downloaded Is that for postgres 14, the very target platform I am trying tò upgrade to.
Former versions (including that for postgres 13) serm to be disappeared from the Planet.
The only (uncertain) option seems to be compiling an older version from source.