In brief:
I have a system running postgres 17.2 with timescale 2.14, using pgadmin a backup was created with pre-data, post-data, and data selected. We are attempting to import this into a postgres 17.7 installation with Timescale 2.24 but getting error messages on things like relation already exists, copy fails due to duplicate key violating constraints, relation “_timescaledb_catalog.chunk_index” does not exist, column “bucket_width” of relation “continuous_agg” does not exist, and column “experimental” of relation “continuous_aggs_bucket_function” does not exist. Is the import successful or am I missing something in how I’m supposed to do a restore?
TimescaleDB version: 2.14 on the source, 2.24 on the destination
PostgreSQL version: 17.2 on the source, 17.7 on the destination
Other software:
OS: Windows 11 Pro on both
Platform:
Install method: On physical system from setup.exe files.
Environment: source is production, destination is development
The system is in a rough state so we’re trying to get the data onto a backup where we can test methods to clean things up without destroying their whole system. The over arching issue is that they’ve had a glitch where they were inserting row after row after row of duplicated data. such that one 7 day chunk is 66 GBs when it was 100 mb at max. Dropping down to daily partitions has granted some relief down to 6 GB chunks. We need to be able to run a command to delete duplicated row values to get partition sizes shrunk back down, but before we do that we need the data on a backup system.
Unfortunately, we’re having issues getting data into the backup system. We’d like to be on the latest version of postgres and tigerdata when we’re done as well. Other potential issues is I’m seeing Postgres 14 and 16 were installed on here as well for some reason. I can easily get approval to remove them, however.