Errors during restore using pg_restore

TimescaleDB and PostgreSQL
1

Hi there !

I am following the dump/restore process outlined here, and getting all sort of errors in the restore.

trying to do a full db dump/restore to a different server.

i am going from postgres12.3 / timescaledb 1.7.2 to postgres14/timescaledb 2.6.0

dump command: pg_dump -Fc -f exampledb.bak exampledb

restore command: pg_restore -Fc -d exampledb exampledb.bak

and I tried also with ----disable-triggers

errors:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3780; 0 16402 TABLE DATA hypertable postgres
pg_restore: error: could not execute query: ERROR:  column "compressed" of relation "hypertable" does not 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;
pg_restore: from TOC entry 3787; 0 16476 TABLE DATA chunk postgres
pg_restore: error: COPY failed for table "chunk": ERROR:  insert or update on table "chunk" violates foreign key constraint "chunk_hypertable_id_fkey"
DETAIL:  Key (hypertable_id)=(1) is not present in table "hypertable".
pg_restore: from TOC entry 3783; 0 16441 TABLE DATA dimension postgres
pg_restore: error: COPY failed for table "dimension": ERROR:  insert or update on table "dimension" violates foreign key constraint "dimension_hypertable_id_fkey"
DETAIL:  Key (hypertable_id)=(1) is not present in table "hypertable".
pg_restore: from TOC entry 3785; 0 16460 TABLE DATA dimension_slice postgres
pg_restore: error: COPY failed for table "dimension_slice": ERROR:  insert or update on table "dimension_slice" violates foreign key constraint "dimension_slice_dimension_id_fkey"
DETAIL:  Key (dimension_id)=(1) is not present in table "dimension".
pg_restore: from TOC entry 3789; 0 16497 TABLE DATA chunk_constraint postgres
pg_restore: error: COPY failed for table "chunk_constraint": ERROR:  insert or update on table "chunk_constraint" violates foreign key constraint "chunk_constraint_chunk_id_fkey"
DETAIL:  Key (chunk_id)=(1) is not present in table "chunk".
pg_restore: from TOC entry 3791; 0 16515 TABLE DATA chunk_index postgres
pg_restore: error: COPY failed for table "chunk_index": ERROR:  insert or update on table "chunk_index" violates foreign key constraint "chunk_index_chunk_id_fkey"
DETAIL:  Key (chunk_id)=(1) is not present in table "chunk".
pg_restore: from TOC entry 3793; 0 16533 TABLE DATA bgw_job postgres
pg_restore: error: could not execute query: ERROR:  column "job_type" of relation "bgw_job" does not exist
Command was: COPY _timescaledb_config.bgw_job (id, application_name, job_type, schedule_interval, max_runtime, max_retries, retry_period) FROM stdin;
pg_restore: from TOC entry 3797; 0 16612 TABLE DATA continuous_agg postgres
pg_restore: error: could not execute query: ERROR:  column "job_id" of relation "continuous_agg" does not exist
Command was: COPY _timescaledb_catalog.continuous_agg (mat_hypertable_id, raw_hypertable_id, user_view_schema, user_view_name, partial_view_schema, partial_view_name, bucket_width, job_id, refresh_lag, direct_view_schema, direct_view_name, max_interval_per_job, ignore_invalidation_older_than, materialized_only) FROM stdin;
pg_restore: from TOC entry 3799; 0 16651 TABLE DATA continuous_aggs_completed_threshold postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.continuous_aggs_completed_threshold" does not exist
Command was: COPY _timescaledb_catalog.continuous_aggs_completed_threshold (materialization_id, watermark) FROM stdin;
pg_restore: from TOC entry 3800; 0 16661 TABLE DATA continuous_aggs_hypertable_invalidation_log postgres
pg_restore: error: could not execute query: ERROR:  column "modification_time" of relation "continuous_aggs_hypertable_invalidation_log" does not exist
Command was: COPY _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (hypertable_id, modification_time, lowest_modified_value, greatest_modified_value) FROM stdin;
pg_restore: from TOC entry 3801; 0 16665 TABLE DATA continuous_aggs_materialization_invalidation_log postgres
pg_restore: error: could not execute query: ERROR:  column "modification_time" of relation "continuous_aggs_materialization_invalidation_log" does not exist
Command was: COPY _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (materialization_id, modification_time, lowest_modified_value, greatest_modified_value) FROM stdin;
pg_restore: from TOC entry 3804; 0 16716 TABLE DATA bgw_policy_compress_chunks postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_config.bgw_policy_compress_chunks" does not exist
Command was: COPY _timescaledb_config.bgw_policy_compress_chunks (job_id, hypertable_id, older_than) FROM stdin;
pg_restore: from TOC entry 3795; 0 16568 TABLE DATA bgw_policy_drop_chunks postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_config.bgw_policy_drop_chunks" does not exist
Command was: COPY _timescaledb_config.bgw_policy_drop_chunks (job_id, hypertable_id, older_than, cascade, cascade_to_materializations) FROM stdin;
pg_restore: from TOC entry 3794; 0 16551 TABLE DATA bgw_policy_reorder postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_config.bgw_policy_reorder" does not exist
Command was: COPY _timescaledb_config.bgw_policy_reorder (job_id, hypertable_id, hypertable_index_name) FROM stdin;
pg_restore: warning: errors ignored on restore: 14

anything to do about this?

2

and this is the log I get doing the dump:

Password: 
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
3

Hey @t0mZ4,

To upgrade TimescaleDB, you cannot just backup from one version and restore into a new version. The extension performs upgrade scripts on the TimescaleDB schema/catalog (and upgrades things like Continuous Aggregate jobs that changed from 1.x to 2.x).

The proper path for you is the following, which is outlined in the Update from TimescaleDB 1.x to 2.x docs page.

The main takeaway is that, currently, you have to backup/restore between the same versions. In your case, because you already have PostgreSQL 12, you can upgrade that machine to TimescaleDB 2.6, then do your dump, and then restore on the new PG14 server.

Give that page a good read and let us know if you have other questions!

4

Hello. If my old database has been erased and the only thing I have is a pg_dump backup, what should I do to recover my data?

5

hi
how were you able to solve your problem?
i’m having similiar problem to upgrade from 1.7 with hypertables into 2.X
all hyper tables ae gone, and also not able to create any new one:

ERROR: tried calling catalog_get when extension isn’t loaded
SQL state: XX000