Failover procedure on multinode env with pg_restore

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
1

Hi
I am trying figure how to restore database completely from scratch but with no success (have error after restore database).
Have 3 VM with successfully setup multinode. Some dummy data split acros 2 nodes.

After completly destoryed clusters via pg_dropcluster and pg_createcluster using pg_restore to make backup on all VMs. Created database by hand, turn on timescaledb and turn on SELECT timescaledb_pre_restore();. There is no error during restore procedure

postgres@worker49:/tmp$ pg_restore -Fd -v -d test test
pg_restore: connecting to database for restore
pg_restore: creating EXTENSION “timescaledb”
pg_restore: creating COMMENT “EXTENSION timescaledb”
pg_restore: creating SERVER “worker50”
pg_restore: creating USER MAPPING “USER MAPPING tsdbadmin SERVER worker50”
pg_restore: creating SERVER “worker51”
pg_restore: creating USER MAPPING “USER MAPPING tsdbadmin SERVER worker51”
pg_restore: creating TABLE “public.sensor_data”
pg_restore: creating FOREIGN TABLE “_timescaledb_internal._dist_hyper_1_100_chunk”
…
pg_restore: creating FOREIGN TABLE “_timescaledb_internal._dist_hyper_1_9_chunk”
pg_restore: processing data for table “_timescaledb_cache.cache_inval_bgw_job”
pg_restore: processing data for table “_timescaledb_cache.cache_inval_extension”
pg_restore: processing data for table “_timescaledb_cache.cache_inval_hypertable”
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_data_node”
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.continuous_agg”
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.hypertable_compression”
pg_restore: processing data for table “_timescaledb_catalog.hypertable_data_node”
pg_restore: processing data for table “_timescaledb_catalog.metadata”
pg_restore: processing data for table “_timescaledb_catalog.remote_txn”
pg_restore: processing data for table “_timescaledb_catalog.tablespace”
pg_restore: processing data for table “_timescaledb_config.bgw_job”
pg_restore: processing data for table “public.sensor_data”
pg_restore: executing SEQUENCE SET chunk_constraint_name
pg_restore: executing SEQUENCE SET chunk_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: executing SEQUENCE SET bgw_job_id_seq
pg_restore: creating INDEX “public.sensor_data_sensor_id_time_idx”
pg_restore: creating INDEX “public.sensor_data_time_idx”
pg_restore: creating TRIGGER “public.sensor_data ts_insert_blocker”

But trying to perform query I had error. I tried both backup only on master and on all nodes.

Someting doing wrong, question is what?
Maybe Multinode don;'t accept pg_restore and must change to pg_basebackup?

2

Hello @Pawel_S

Quick question: is TimescaleDB extension installed on every node? Confirming too, that you would need to create dump/restore each access/data node.

What versions of TimescaleDB, Postgres, and OS are you using, please? And what error are you seeing on trying to perform the query? (Please also check the logs for errors.)

Is this a test/development system?

Sorry, so many questions! :upside_down_face: But if you can record some of these details there’s a better chance I can get you some help.

3

@Pawel_S Can you post the actual error you get when you try to query?

Regarding backup options, we do not recommend logical backups with pg_dump for multi-node because it doesn’t guarantee consistent backups across nodes in the cluster. Instead, we recommend physical backups using a distributed restore point: https://docs.timescale.com/timescaledb/latest/overview/core-concepts/backup-restore/#backup-and-restore

1 Like
4
  • PostgreSQL 14.3 (Ubuntu 14.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0 3ubuntu1~18.04) 7.5.0, 64-bit
  • Ubuntu 18.04.5 LTS

archive_command = ‘test ! -f /var/lib/postgresql/14/main/archive/%f && cp %p /var/lib/postgresql/14/main/archive/%f’
archive_mode = ‘on’
archive_timeout = ‘600’
data_directory = ‘/var/lib/postgresql/14/main’
datestyle = ‘iso, mdy’
default_text_search_config = ‘pg_catalog.english’
enable_partitionwise_aggregate = ‘on’
external_pid_file = ‘/var/run/postgresql/14-main.pid’
hba_file = ‘/etc/postgresql/14/main/pg_hba.conf’
ident_file = ‘/etc/postgresql/14/main/pg_ident.conf’
jit = ‘off’
listen_addresses = ‘*’
log_line_prefix = '%t ’
max_connections = 100
max_prepared_transactions = ‘150’
port = ‘5432’
shared_buffers = ‘24MB’
shared_preload_libraries = ‘pg_stat_statements,timescaledb’
ssl = ‘off’
unix_socket_directories = ‘/var/run/postgresql’
wal_level = ‘replica’

On each node import users with globals.sql

  • create database manual
  • install timescaledb - CREATE EXTENSION IF NOT EXISTS timescaledb;
  • perform: SELECT timescaledb_pre_restore();
  • import backup, with pg_restore -Fd -v -d test test
  • perform: SELECT timescaledb_post_restore();

trying query:
test=# select count(*) from sensor_data;
ERROR: tried calling catalog_get when extension isn’t loaded

in log
2022-07-01 12:30:26 GMT CONTEXT: PL/pgSQL function inline_code_block line 16 at RAISE
2022-07-01 12:30:30 GMT FATAL: terminating background worker “TimescaleDB Background Worker Scheduler” due to administrator command
2022-07-01 12:30:30 GMT WARNING: telemetry error: HTTP connection read error
2022-07-01 12:30:30 GMT FATAL: terminating background worker “Telemetry Reporter [1]” due to administrator command
2022-07-01 12:30:30 GMT LOG: background worker “TimescaleDB Background Worker Scheduler” (PID 903) exited with exit code 1
2022-07-01 12:30:30 GMT LOG: background worker “Telemetry Reporter [1]” (PID 904) exited with exit code 1
2022-07-01 12:32:22 GMT ERROR: tried calling catalog_get when extension isn’t loaded
2022-07-01 12:32:22 GMT LOG: background worker “TimescaleDB Background Worker Scheduler” (PID 971) exited with exit code 1
2022-07-01 12:33:42 GMT ERROR: tried calling catalog_get when extension isn’t loaded
2022-07-01 12:33:42 GMT STATEMENT: select count(*) from sensor_data;

what I am doing wrong?

5

funny, i made tunning postgres via timescaldb-tune and query perform without error.

6

Sounds like you got it working @Pawel_S. I don’t recognize the issue you were having, but if you have a good way to reproduce it, feel free to file a bug report in our GitHub repository.