Move system to AWS RDS, get rid of timescaledb

[StormScorpion]

Hello,

I want to move our current Zabbix Installation to AWS (ECS / RDS). Today we use timescaledb, which is not supported by RDS.
Is it possible to transfer all data without history? I tried with pg_dump and pg_restore and skipped the timescaledb tables. But there were a lot of references to the timescaledb tables, so Zabbix did not start.

Or is it possible to re convert history from timescaledb to plain postgres tables?

[StormScorpion]

I managed to migrate the data, cyber thanks for pointing me at the right direction. Here the steps used in case this is helpful to someone else.

Dump database without history/trends tables
pg_dump -T history* -T trend* -N *timescaledb* -f zabbix.dump -d zabbix_prod_db

Edit dump with vi and remove two timescaledb lines at the beginning

Export history/trends as CSV
psql -c "\COPY (SELECT * FROM history_log) TO history_log.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_str) TO history_str.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_text) TO history_text.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_uint) TO history_uint.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history) TO history.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM trends_uint) TO trends_uint.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM trends) TO trends.dump.csv DELIMITER ',' CSV" zabbix_prod_db

Compress and transfer to target system

createdb zabbix
createuser zabbix
Create history/trends Tables from sql script in install dir
Import data
psql -f zabbix_prod_db.dump zabbix
Import history/trends
psql -d zabbix -c "\COPY history_log from history_log.dump.csv CSV"
psql -d zabbix -c "\COPY history_str from history_str.dump.csv CSV"
psql -d zabbix -c "\COPY history_text from history_text.dump.csv CSV"
psql -d zabbix -c "\COPY history_uint from history_uint.dump.csv CSV"
psql -d zabbix -c "\COPY history from history.dump.csv CSV"
psql -d zabbix -c "\COPY trends_uint from trends_uint.dump.csv CSV"
psql -d zabbix -c "\COPY trends from trends.dump.csv CSV"
Alter housekeeping
UPDATE config SET db_extension='',hk_history_global=0,hk_trends_glob al=0;
UPDATE config SET compression_status=0,compress_older='7d';


Importing CSV data took 3 hours for 25GB data. You could drop primary keys on the tables to speed up this process. Dont forget to create them later :-)

[Brambo]

I can't help you with the actual problem.
However if you would post the command's used to export your data and how you did try to import I expect people to be more helpful to point you in the right direction.

[cyber]

Hello,

I want to move our current Zabbix Installation to AWS (ECS / RDS). Today we use timescaledb, which is not supported by RDS.
Is it possible to transfer all data without history? I tried with pg_dump and pg_restore and skipped the timescaledb tables. But there were a lot of references to the timescaledb tables, so Zabbix did not start.

Or is it possible to re convert history from timescaledb to plain postgres tables?

Export data to CSV and import from it... (replace ${table} as needed. It will take time ...
psql -c "\COPY (SELECT * FROM ${table}) TO ${table}.dump.csv DELIMITER ',' CSV" zabbix

later import
psql -d zabbix -c "\COPY ${table} FROM $(table}.dump.csv CSV"

[StormScorpion]

I managed to migrate the data, cyber thanks for pointing me at the right direction. Here the steps used in case this is helpful to someone else.

Dump database without history/trends tables
pg_dump -T history* -T trend* -N *timescaledb* -f zabbix.dump -d zabbix_prod_db

Edit dump with vi and remove two timescaledb lines at the beginning

Export history/trends as CSV
psql -c "\COPY (SELECT * FROM history_log) TO history_log.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_str) TO history_str.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_text) TO history_text.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history_uint) TO history_uint.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM history) TO history.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM trends_uint) TO trends_uint.dump.csv DELIMITER ',' CSV" zabbix_prod_db
psql -c "\COPY (SELECT * FROM trends) TO trends.dump.csv DELIMITER ',' CSV" zabbix_prod_db

Compress and transfer to target system

createdb zabbix
createuser zabbix
Create history/trends Tables from sql script in install dir
Import data
psql -f zabbix_prod_db.dump zabbix
Import history/trends
psql -d zabbix -c "\COPY history_log from history_log.dump.csv CSV"
psql -d zabbix -c "\COPY history_str from history_str.dump.csv CSV"
psql -d zabbix -c "\COPY history_text from history_text.dump.csv CSV"
psql -d zabbix -c "\COPY history_uint from history_uint.dump.csv CSV"
psql -d zabbix -c "\COPY history from history.dump.csv CSV"
psql -d zabbix -c "\COPY trends_uint from trends_uint.dump.csv CSV"
psql -d zabbix -c "\COPY trends from trends.dump.csv CSV"
Alter housekeeping
UPDATE config SET db_extension='',hk_history_global=0,hk_trends_glob al=0;
UPDATE config SET compression_status=0,compress_older='7d';


Importing CSV data took 3 hours for 25GB data. You could drop primary keys on the tables to speed up this process. Dont forget to create them later :-)