Migrating Database to new Postgresql Server

[HaveDill]

Hello,

I'm using Zabbix 5.0.3, Postgresql 11, on CentOS 7. I do use Timescaledb


I want to move my zabbix DB out of it's current postgres cluster to it's own individual one. I spun up a new server with postgres11, and got that all going. However when i do a pg_dumpall (because i want the roles, etc maintained - i'll drop the databases i dont want after the restore), and then attempt to restore it to my new cluster, i get errors. Are these errors ignorable? Or am i restoring wrong??

As the postgres user:
-bash-4.2$ psql -f /mnt/temp_storage/dave/prod_cluster.dump postgres


Here are some snippets of the errors

psql:/mnt/temp_storage/dave/prod_cluster.dump:392418: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:392425: ERROR: syntax error at or near "1"
LINE 1: 1 1 clock integer t
^
psql:/mnt/temp_storage/dave/prod_cluster.dump:392757: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:392764: ERROR: syntax error at or near "2490"
LINE 1: 2490 2 1607990400 1608076800
^
psql:/mnt/temp_storage/dave/prod_cluster.dump:392765: invalid command \N
psql:/mnt/temp_storage/dave/prod_cluster.dump:392766: invalid command \N
psql:/mnt/temp_storage/dave/prod_cluster.dump:392767: invalid command \N
psql:/mnt/temp_storage/dave/prod_cluster.dump:392768: invalid command \N
psql:/mnt/temp_storage/dave/prod_cluster.dump:392769: invalid command \N

psql:/mnt/temp_storage/dave/prod_cluster.dump:393129: ERROR: syntax error at or near "2856"
LINE 1: 2856 2510 constraint_2510
^
psql:/mnt/temp_storage/dave/prod_cluster.dump:393792: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393799: ERROR: syntax error at or near "2856"
LINE 1: 2856 _hyper_1_2856_chunk_history_1 1 history_1
^
psql:/mnt/temp_storage/dave/prod_cluster.dump:393800: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393807: ERROR: column "job_type" of relation "bgw_job" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393808: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393815: ERROR: column "job_id" of relation "continuous_agg" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393816: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393823: ERROR: relation "_timescaledb_catalog.continuous_aggs_completed_th reshold" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393824: invalid command \.
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
psql:/mnt/temp_storage/dave/prod_cluster.dump:393879: ERROR: relation "_timescaledb_config.bgw_policy_compress_chunk s" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393880: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393887: ERROR: relation "_timescaledb_config.bgw_policy_drop_chunks" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393888: invalid command \.
psql:/mnt/temp_storage/dave/prod_cluster.dump:393895: ERROR: relation "_timescaledb_config.bgw_policy_reorder" does not exist
psql:/mnt/temp_storage/dave/prod_cluster.dump:393896: invalid command \.

[Hamardaban]

Have you installed all the extensions in the new cluster as in the old one?

[HaveDill]

Yeah i installed the timescale DB extension and made sure it was enabled in the postgresql.conf. Is there any other ones i could be missing?

[Hamardaban]

With what command and with what parameters did you create the dump? Perhaps you need to specify any additional options when restoring?

[HaveDill]

So i'm not an expert by any means, but i did a

pg_dumpall -f /mnt/temp_storage/dave/prod_cluster.dump

then to restore, on the new postgres host (as the postgres user)

psql -f /mnt/temp_storage/dave/prod_cluster.dump postgres

Are there any flags i should be additionally providing?

[Hamardaban]

This command will upload all the databases (not just the zabix database) to a text file with sql commands. Look at this file and try to understand exactly which commands are causing errors. You need to run the upload and download from the superuser with the rights to create all objects in the database.I highly recommend reading the documentation on the commands used before using them.

[HaveDill]

I am running these as the super user and i am aware it was dumping the whole cluster (hence my explanation in my first post) I was doing a dumpall so i could make sure all my roles, etc transferred over properly. I've also looked through the dump like you mentioned, and not seeing anything helpful. Again, this is a fresh database...so i'd expect it to just create any relations, tables, etc as needed

Example:
ERROR: column "job_type" of relation "bgw_job" does not exist

In my dump, i see it is expecting that column, oddly it's expecting it from stdin, but i mean pg_dumpall created that, so it must be the right syntax (plus all my data is there, i'm only getting these few errors). I'm just concerned my timescaledb may not be fully functional if i cutover, and if i do - how long will it be until i actually notice that impact

COPY _timescaledb_config.bgw_job (id, application_name, job_type, schedule_interval, max_runtime, max_retries, retry_period) FROM stdin;

Here's another example:

ERROR: syntax error at or near "2856"
LINE 1: 2856 _hyper_1_2856_chunk_history_1 1 history_1
^

From the dump:

$ grep "_hyper_1_2856_chunk_history_1" /mnt/temp_storage/dave/zabbix.dump
2856 _hyper_1_2856_chunk_history_1 1 history_1

---

Following this timescale db document https://docs.timescale.com/latest/us...scaledb/backup

I still get the same errors (although a little more verbose)

pg_restore: [archiver (db)] Error from TOC entry 6033; 0 19886 TABLE DATA hypertable postgres
pg_restore: [archiver (db)] 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: [archiver (db)] Error from TOC entry 6040; 0 19960 TABLE DATA chunk svc_zabbix
pg_restore: [archiver (db)] COPY failed for table "chunk": ERROR: insert or update on table "chunk" violates foreign key constraint "chunk_hypertable_id_fkey"
DETAIL: Key (hypertable_id)=(2) is not present in table "hypertable".
pg_restore: [archiver (db)] Error from TOC entry 6036; 0 19925 TABLE DATA dimension postgres
pg_restore: [archiver (db)] 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: [archiver (db)] Error from TOC entry 6038; 0 19944 TABLE DATA dimension_slice postgres
pg_restore: [archiver (db)] 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)=(2) is not present in table "dimension".
pg_restore: [archiver (db)] Error from TOC entry 6042; 0 19980 TABLE DATA chunk_constraint postgres
pg_restore: [archiver (db)] 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)=(2856) is not present in table "chunk".
pg_restore: [archiver (db)] Error from TOC entry 6044; 0 19998 TABLE DATA chunk_index postgres
pg_restore: [archiver (db)] 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)=(2856) is not present in table "chunk".
pg_restore: [archiver (db)] Error from TOC entry 6046; 0 20016 TABLE DATA bgw_job postgres
pg_restore: [archiver (db)] 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: [archiver (db)] Error from TOC entry 6050; 0 20095 TABLE DATA continuous_agg postgres
pg_restore: [archiver (db)] 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) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 6052; 0 20131 TABLE DATA continuous_aggs_completed_threshold postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "_timescaledb_catalog.continuous_aggs_completed_th reshold" does not exist
Command was: COPY _timescaledb_catalog.continuous_aggs_completed_thr eshold (materialization_id, watermark) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 6057; 0 20196 TABLE DATA bgw_policy_compress_chunks postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "_timescaledb_config.bgw_policy_compress_chunk s" does not exist
Command was: COPY _timescaledb_config.bgw_policy_compress_chunks (job_id, hypertable_id, older_than) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 6048; 0 20051 TABLE DATA bgw_policy_drop_chunks postgres
pg_restore: [archiver (db)] 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: [archiver (db)] Error from TOC entry 6047; 0 20034 TABLE DATA bgw_policy_reorder postgres
pg_restore: [archiver (db)] 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;

[fansari]

Do you have the same timescaledb extension version on old and new postgres installation? You can check with "\dx" for extension version numbers.