How to switch from partitioning to Timescale?

Question

PostgreSQL

I couldn’t figure out how to switch from table partitioning to TimescaleDB, which the author described in the article High performance and native partitioning , I get a message that:

zabbix=# SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
ОШИБКА:  table "history" is already partitioned
ПОДРОБНОСТИ:  It is not possible to turn tables that use inheritance into hypertables.

Please suggest / point out the next steps.
Thank you!

Answer 1

I made up about this option

останавливаем:

systemctl stop zabbix-server.service && \
service rh-php72-php-fpm.service stop && \
service rh-nginx116-nginx.service stop

Заходим в sql:

sudo -u postgres psql
\c zabbix

пробуем удалить схему partitions (\dn для просмотра текущих схем) с партицированием и получаем список таблиц, которые имеют зависимости, и берём этот список
DROP SCHEMA IF EXISTS partitions
удаляем/отключаем триггеры

DROP TRIGGER partition_trg ON history;
DROP TRIGGER partition_trg ON history_sync;
DROP TRIGGER partition_trg ON history_uint;
DROP TRIGGER partition_trg ON history_str_sync;
DROP TRIGGER partition_trg ON history_log;
DROP TRIGGER partition_trg ON trends;
DROP TRIGGER partition_trg ON trends_uint;

удаляем функцию:

DROP FUNCTION delete_partitions(interval, text) CASCADE;

#1 меняем таблицам размещение схемы (возможно это лишнее) (сюда вставляем полученный ранее список зависимых таблиц):

alter table partitions.history_log_p2020_11_21 set schema public;
alter table partitions.history_log_p2020_11_22 set schema public;
alter table partitions.history_log_p2020_11_23 set schema public;
alter table partitions.history_log_p2020_11_24 set schema public;
alter table partitions.history_log_p2020_11_25 set schema public;
alter table partitions.history_log_p2020_11_26 set schema public;
alter table partitions.history_log_p2020_11_27 set schema public;

alter table partitions.history_p2020_11_21 set schema public;
alter table partitions.history_p2020_11_22 set schema public;
alter table partitions.history_p2020_11_23 set schema public;
alter table partitions.history_p2020_11_24 set schema public;
alter table partitions.history_p2020_11_25 set schema public;
alter table partitions.history_p2020_11_26 set schema public;
alter table partitions.history_p2020_11_27 set schema public;

alter table partitions.history_uint_p2020_11_21 set schema public;
alter table partitions.history_uint_p2020_11_22 set schema public;
alter table partitions.history_uint_p2020_11_23 set schema public;
alter table partitions.history_uint_p2020_11_24 set schema public;
alter table partitions.history_uint_p2020_11_25 set schema public;
alter table partitions.history_uint_p2020_11_26 set schema public;
alter table partitions.history_uint_p2020_11_27 set schema public;

alter table partitions.trends_p2019_12 set schema public;
alter table partitions.trends_p2020_01 set schema public;
alter table partitions.trends_p2020_02 set schema public;
alter table partitions.trends_p2020_03 set schema public;
alter table partitions.trends_p2020_04 set schema public;
alter table partitions.trends_p2020_05 set schema public;
alter table partitions.trends_p2020_06 set schema public;
alter table partitions.trends_p2020_07 set schema public;
alter table partitions.trends_p2020_08 set schema public;
alter table partitions.trends_p2020_09 set schema public;
alter table partitions.trends_p2020_10 set schema public;
alter table partitions.trends_p2020_11 set schema public;

alter table partitions.trends_uint_p2019_12 set schema public;
alter table partitions.trends_uint_p2020_01 set schema public;
alter table partitions.trends_uint_p2020_02 set schema public;
alter table partitions.trends_uint_p2020_03 set schema public;
alter table partitions.trends_uint_p2020_04 set schema public;
alter table partitions.trends_uint_p2020_05 set schema public;
alter table partitions.trends_uint_p2020_06 set schema public;
alter table partitions.trends_uint_p2020_07 set schema public;
alter table partitions.trends_uint_p2020_08 set schema public;
alter table partitions.trends_uint_p2020_09 set schema public;
alter table partitions.trends_uint_p2020_10 set schema public;
alter table partitions.trends_uint_p2020_11 set schema public;

теперь можно удалить схему, если схему не меняли (не делали #1), то выполняем #2, иначе переходиим к #3
DROP SCHEMA IF EXISTS partitions;
#2 - переносим данные из партицированной таблицы в корневую таблицу

INSERT INTO history_log select * from partitions.history_log_p2020_11_21; DROP table partitions.history_log_p2020_11_21;
INSERT INTO history_log select * from partitions.history_log_p2020_11_22; DROP table partitions.history_log_p2020_11_22;
INSERT INTO history_log select * from partitions.history_log_p2020_11_23; DROP table partitions.history_log_p2020_11_23;
INSERT INTO history_log select * from partitions.history_log_p2020_11_24; DROP table partitions.history_log_p2020_11_24;
INSERT INTO history_log select * from partitions.history_log_p2020_11_25; DROP table partitions.history_log_p2020_11_25;
INSERT INTO history_log select * from partitions.history_log_p2020_11_26; DROP table partitions.history_log_p2020_11_26;
INSERT INTO history_log select * from partitions.history_log_p2020_11_27; DROP table partitions.history_log_p2020_11_27;


INSERT INTO history select * from partitions.history_p2020_11_21; DROP table partitions.history_p2020_11_21;
INSERT INTO history select * from partitions.history_p2020_11_22; DROP table partitions.history_p2020_11_22;
INSERT INTO history select * from partitions.history_p2020_11_23; DROP table partitions.history_p2020_11_23;
INSERT INTO history select * from partitions.history_p2020_11_24; DROP table partitions.history_p2020_11_24;
INSERT INTO history select * from partitions.history_p2020_11_25; DROP table partitions.history_p2020_11_25;
INSERT INTO history select * from partitions.history_p2020_11_26; DROP table partitions.history_p2020_11_26;
INSERT INTO history select * from partitions.history_p2020_11_27; DROP table partitions.history_p2020_11_27;


INSERT INTO history_uint select * from partitions.history_uint_p2020_11_21; DROP table partitions.history_uint_p2020_11_21;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_22; DROP table partitions.history_uint_p2020_11_22;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_23; DROP table partitions.history_uint_p2020_11_23;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_24; DROP table partitions.history_uint_p2020_11_24;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_25; DROP table partitions.history_uint_p2020_11_25;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_26; DROP table partitions.history_uint_p2020_11_26;
INSERT INTO history_uint select * from partitions.history_uint_p2020_11_27; DROP table partitions.history_uint_p2020_11_27;

-- ЗДЕСЬ могут быть ПРОБЛЕМЫ!
INSERT INTO trends select * from partitions.trends_p2019_12; DROP table partitions.trends_p2019_12;
INSERT INTO trends select * from partitions.trends_p2020_01; DROP table partitions.trends_p2020_01;
INSERT INTO trends select * from partitions.trends_p2020_02; DROP table partitions.trends_p2020_02;
INSERT INTO trends select * from partitions.trends_p2020_03; DROP table partitions.trends_p2020_03;
INSERT INTO trends select * from partitions.trends_p2020_04; DROP table partitions.trends_p2020_04;
INSERT INTO trends select * from partitions.trends_p2020_05; DROP table partitions.trends_p2020_05;
INSERT INTO trends select * from partitions.trends_p2020_06; DROP table partitions.trends_p2020_06;
INSERT INTO trends select * from partitions.trends_p2020_07; DROP table partitions.trends_p2020_07;
INSERT INTO trends select * from partitions.trends_p2020_08; DROP table partitions.trends_p2020_08;
INSERT INTO trends select * from partitions.trends_p2020_09; DROP table partitions.trends_p2020_09;
INSERT INTO trends select * from partitions.trends_p2020_10; DROP table partitions.trends_p2020_10;
INSERT INTO trends select * from partitions.trends_p2020_11; DROP table partitions.trends_p2020_11;


INSERT INTO trends_uint select * from partitions.trends_uint_p2019_12; DROP table partitions.trends_uint_p2019_12;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_01; DROP table partitions.trends_uint_p2020_01;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_02; DROP table partitions.trends_uint_p2020_02;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_03; DROP table partitions.trends_uint_p2020_03;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_04; DROP table partitions.trends_uint_p2020_04;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_05; DROP table partitions.trends_uint_p2020_05;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_06; DROP table partitions.trends_uint_p2020_06;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_07; DROP table partitions.trends_uint_p2020_07;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_08; DROP table partitions.trends_uint_p2020_08;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_09; DROP table partitions.trends_uint_p2020_09;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_10; DROP table partitions.trends_uint_p2020_10;
INSERT INTO trends_uint select * from partitions.trends_uint_p2020_11; DROP table partitions.trends_uint_p2020_11;