Restoring with concurrency - error

Pawel_S · December 2, 2024, 8:10am

I was testing restore backup with concurrency option from Timescale Documentation | FAQ and troubleshooting. Unfortunatelly during pg_restore phase shows lots of errors (from first command and from second one). I was wondering if this procedure is correct or I am doing somethink wrong.

I am dumping db with following options:
pg_dump … --jobs=6 --format=d --compress=3 - --file=/my/path/to/my_db my_db
I succesfully restore db with one concurrency
pg_restore --format=directory --dbname=“my_db” “/my/path/to/my_db”
Before this command I need to create db, create extension timescaledb and execute in my_db: SELECT timescaledb_pre_restore();
and after pg_restore is done: SELECT timescaledb_post_restore();
first, serially load JUST the _timescaledb_catalog

pg_restore -d ‘some_my_db’ --format=directory --schema=‘_timescaledb_catalog’ --no-tablespaces --no-owner --no-privileges --verbose some_my_db 2>&1 | tee ./some_my_db-1-$(date ‘+%Y%m%d%H%M%S’).log

pg_restore: connecting to database for restore
pg_restore: implied data-only restore
pg_restore: processing data for table “_timescaledb_catalog.hypertable”
pg_restore: processing data for table “_timescaledb_catalog.chunk”
pg_restore: processing data for table “_timescaledb_catalog.dimension”
pg_restore: processing data for table “_timescaledb_catalog.dimension_slice”
pg_restore: processing data for table “_timescaledb_catalog.chunk_constraint”
pg_restore: processing data for table “_timescaledb_catalog.chunk_index”
pg_restore: processing data for table “_timescaledb_catalog.compression_chunk_size”
pg_restore: processing data for table “_timescaledb_catalog.compression_settings”
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 98844; 0 161613308 TABLE DATA compression_settings postgres
pg_restore: error: COPY failed for table “compression_settings”: ERROR: schema “some_my_schema” does not exist
CONTEXT: COPY compression_settings, line 1, column relid: “some_my_schema.pmdata_kpis_hour”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg_migrate_plan”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg_migrate_plan_step”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_bucket_function”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_hypertable_invalidation_log”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_invalidation_threshold”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_materialization_invalidation_log”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_watermark”
pg_restore: processing data for table “_timescaledb_catalog.metadata”
pg_restore: processing data for table “_timescaledb_catalog.tablespace”
pg_restore: executing SEQUENCE SET chunk_constraint_name
pg_restore: executing SEQUENCE SET chunk_id_seq
pg_restore: executing SEQUENCE SET continuous_agg_migrate_plan_step_step_id_seq
pg_restore: executing SEQUENCE SET dimension_id_seq
pg_restore: executing SEQUENCE SET dimension_slice_id_seq
pg_restore: executing SEQUENCE SET hypertable_id_seq
pg_restore: warning: errors ignored on restore: 1

jonatasdp · December 2, 2024, 3:59pm

Hello @Pawel_S , the error you’re seeing (ERROR: schema "some_my_schema" does not exist) suggests that the restore is failing because the required schema doesn’t exist in the target database.

Have you tried to just create this schema manually?

Let’s break down here the steps in a way that we can also isolate schema, timescale catalog and the data:

SELECT timescaledb_pre_restore();

You can try to also restore in this specific order:

# First, restore the schema only (this creates all required schemas)
pg_restore --dbname="my_db" --schema-only "/my/path/to/my_schema_only"

# Then restore the TimescaleDB catalog (as you were doing)
pg_restore --dbname="my_db" --format=directory \
  --schema='_timescaledb_catalog' --no-tablespaces \
  --no-owner --no-privileges "/my/path/to/my_data_catalog_only"

# Finally, restore the rest with parallelism
pg_restore --dbname="my_db" --format=directory \
  --jobs=6 --no-tablespaces --no-owner --no-privileges \
  --exclude-schema='_timescaledb_catalog' "/my/path/to/my_full_db"

Run the post-restore function:

SELECT timescaledb_post_restore();

If you also is focused on speed, I’d try the timescaledb-parallel-copy and just recreate the hypertable structure.

Pawel_S · December 4, 2024, 9:59am

I tried, new tables keep appearing with errors. I don’t see this solution working

psql -c “create database my_db;”;
psql -d my_db -c “CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;”;
psql -d my_db -c “SELECT timescaledb_pre_restore();”;
psql -d my_db -c “create schema my_custom_schema;”;
psql -d my_db -c “create table my_custom_schema.table01();”;
psql -d my_db -c “create table my_custom_schema.table02();”;
psql -d my_db -c “create table my_custom_schema.table03();”;
psql -d my_db -c “create table my_custom_schema.table04();”;
/usr/lib/postgresql/16/bin/pg_restore -d ‘my_db’ --format=directory --schema=‘_timescaledb_catalog’ --no-tablespaces --no-owner --no-privileges --verbose my_db 2>&1 | tee ./my_db-1-$(date ‘+%Y%m%d%H%M%S’).log

pg_restore: from TOC entry 15044; 0 17068 TABLE DATA compression_settings npi
pg_restore: error: COPY failed for table “compression_settings”: ERROR: schema “my_custom_schema” does not exist
CONTEXT: COPY compression_settings, line 4, column relid: “my_custom_schema.table05”

Moreover I tried with our zabbix backup
timescaledb-2-loader-postgresql-16, timescaledb-2-postgresql-16 - 2.15.3~debian11
postgresql-client-16 - 16.4-1.pgdg110+1

psql -c “create database zabbix;”;
psql -d zabbix -c “CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;”;
psql -d zabbix -c “SELECT timescaledb_pre_restore();”;
/usr/lib/postgresql/16/bin/pg_restore -d ‘zabbix’ --format=directory --schema=‘_timescaledb_catalog’ --no-tablespaces --no-owner --no-privileges --verbose zabbix 2>&1 | tee ./zabbix-1-$(date ‘+%Y%m%d%H%M%S’).log

pg_restore: connecting to database for restore
pg_restore: implied data-only restore
pg_restore: processing data for table “_timescaledb_catalog.hypertable”
pg_restore: processing data for table “_timescaledb_catalog.chunk”
pg_restore: processing data for table “_timescaledb_catalog.dimension”
pg_restore: processing data for table “_timescaledb_catalog.dimension_slice”
pg_restore: processing data for table “_timescaledb_catalog.chunk_constraint”
pg_restore: processing data for table “_timescaledb_catalog.chunk_index”
pg_restore: processing data for table “_timescaledb_catalog.compression_chunk_size”
pg_restore: processing data for table “_timescaledb_catalog.compression_settings”
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5820; 0 15901092 TABLE DATA compression_settings postgres
pg_restore: error: COPY failed for table “compression_settings”: ERROR: relation “_timescaledb_internal.compress_hyper_16_12042_chunk” does not exist
CONTEXT: COPY compression_settings, line 1, column relid: “_timescaledb_internal.compress_hyper_16_12042_chunk”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg_migrate_plan”
pg_restore: processing data for table “_timescaledb_catalog.continuous_agg_migrate_plan_step”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_bucket_function”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_hypertable_invalidation_log”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_invalidation_threshold”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_materialization_invalidation_log”
pg_restore: processing data for table “_timescaledb_catalog.continuous_aggs_watermark”
pg_restore: processing data for table “_timescaledb_catalog.metadata”
pg_restore: processing data for table “_timescaledb_catalog.tablespace”
pg_restore: executing SEQUENCE SET chunk_constraint_name
pg_restore: executing SEQUENCE SET chunk_id_seq
pg_restore: executing SEQUENCE SET continuous_agg_migrate_plan_step_step_id_seq
pg_restore: executing SEQUENCE SET dimension_id_seq
pg_restore: executing SEQUENCE SET dimension_slice_id_seq
pg_restore: executing SEQUENCE SET hypertable_id_seq

jonatasdp · December 6, 2024, 2:23pm

Thanks for the details @Pawel_S . Let’s see if @sven has any insights on how to restore it properly.

Restoring with concurrency - error

first, serially load JUST the _timescaledb_catalog