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.
That’s a possibility, @giaza. Just find the old release tags that worked for the old versions and try to build them. If you’re familiar with building it, that’s a great option.
Another option is isolating the parts of the dump that are not working, like detecting what tables are okay and slowly migrating them to a new server. You may also migrate parts of it with different methods.
We also have a tool that is blind on the PostgreSQL version and just trying to migrate it: Timescale Documentation | Live migration