Issue with pg_restore

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
1

In brief:
I am running into an issue when I call pg_restore on my timescaledb pg_dump

TimescaleDB version: docker image: timescaledb:latest-pg14
PostgreSQL version: pg14
Other software:
OS: Amazon Linux for pg_dump, windows for pg_restore
Platform:
Install method: Docker
Environment: Development

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 14187; 0 16495 TABLE DATA chunk postgres
pg_restore: error: could not execute query: ERROR:  column "osm_chunk" of relation "chunk" does not exist
Command was: COPY _timescaledb_catalog.chunk (id, hypertable_id, schema_name, table_name, compressed_chunk_id, dropped, status, osm_chunk) FROM stdin;
pg_restore: from TOC entry 14189; 0 16519 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 14191; 0 16536 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 14202; 0 16697 TABLE DATA compression_chunk_size postgres
pg_restore: error: COPY failed for table "compression_chunk_size": ERROR:  insert or update on table "compression_chunk_size" violates foreign key constraint "compression_chunk_size_chunk_id_fkey"
DETAIL:  Key (chunk_id)=(1) is not present in table "chunk".
pg_restore: from TOC entry 14196; 0 16614 TABLE DATA continuous_agg postgres
pg_restore: error: could not execute query: ERROR:  column "finalized" 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, direct_view_schema, direct_view_name, materialized_only, finalized) FROM stdin;
pg_restore: from TOC entry 14204; 0 16733 TABLE DATA continuous_agg_migrate_plan postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.continuous_agg_migrate_plan" does not exist
Command was: COPY _timescaledb_catalog.continuous_agg_migrate_plan (mat_hypertable_id, start_ts, end_ts) FROM stdin;
pg_restore: from TOC entry 14205; 0 16745 TABLE DATA continuous_agg_migrate_plan_step postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.continuous_agg_migrate_plan_step" does not exist
Command was: COPY _timescaledb_catalog.continuous_agg_migrate_plan_step (mat_hypertable_id, step_id, status, start_ts, end_ts, type, config) FROM stdin;
pg_restore: from TOC entry 14197; 0 16636 TABLE DATA continuous_aggs_bucket_function postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.continuous_aggs_bucket_function" does not exist
Command was: COPY _timescaledb_catalog.continuous_aggs_bucket_function (mat_hypertable_id, experimental, name, bucket_width, origin, timezone) FROM stdin;
pg_restore: from TOC entry 14184; 0 16467 TABLE DATA dimension_partition postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.dimension_partition" does not exist
Command was: COPY _timescaledb_catalog.dimension_partition (dimension_id, range_start, data_nodes) FROM stdin;
pg_restore: from TOC entry 14194; 0 16565 TABLE DATA bgw_job postgres
pg_restore: error: could not execute query: ERROR:  column "check_schema" of relation "bgw_job" does not exist
Command was: COPY _timescaledb_config.bgw_job (id, application_name, schedule_interval, max_runtime, max_retries, retry_period, proc_schema, proc_name, owner, scheduled, hypertable_id, config, check_schema, check_name) FROM stdin;
pg_restore: from TOC entry 24598; 0 0 SEQUENCE SET continuous_agg_migrate_plan_step_step_id_seq postgres
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('_timescaledb_catalog.continuous_ag...
                                 ^
Command was: SELECT pg_catalog.setval('_timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq', 1, false);


pg_restore: warning: errors ignored on restore: 11
2

Welcome to the Timescale Forum! :partying_face:

Sounds like you might have different TimescaleDB versions on the source and destination machines?

The process you should use is described here:

3

That is odd because I checked the docker image versions & hashes and they are the exact same.

On my local machine (M1 Mac): timescale/timescaledb latest-pg14 771f885e78cf 6 weeks ago 342MB

On AWS (t4g.small): timescale/timescaledb latest-pg14 771f885e78cf 6 weeks ago 342MB

I did follow the process in the timescale documentation as well.

4

Well that is certainly odd - just to make sure what does \dx show from psql (or SELECT extversion FROM pg_extension where extname = 'timescaledb'; if you’re not using psql) show you?

I’m sure they will be the same - but just in case!

5

I ran into this, even though I was “sure” that both the source and destination were on the same version.

Turns out that during my last TSDB upgrade, I ran ALTER EXTENSION timescaledb UPDATE; on all of my databases on both the source and destination server, but I neglected to run it on the template1 database. This meant that any time a new database was created, it would still be using an old version of TSDB.

The fix for me was:

psql -U postgres -X
postgres=# \c template1
template1=# ALTER EXTENSION timescaledb UPDATE;