Как перейти с партицирования на Timescale?

Question

[Антон Теремшонок]

Не смог разобраться как перейти с партицирования таблиц на TimescaleDB, который описал автор в статье Высокая производительность и нативное партициониро..., получаю сообщение что:

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.

Прошу подсказать/указать на дальнейшие действия.
Спасибо!

Answer 1

[Антон Теремшонок]

Наколхозил примерно такой вариант

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

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;

Comments

[Антон Теремшонок]

продолжение 1

#3 - переносим данные из перемещённых таблиц в бывшую корневую таблицу

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


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


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

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


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

Если проблемы всё же нашлись (получали данные из будущего, к сожалению не нашёл другого решения, кроме как удалить эти данные):

Пример сообщения об ошибке

zabbix=# INSERT INTO trends select * from trends_p2020_06;
ОШИБКА:  повторяющееся значение ключа нарушает ограничение уникальности "trends_pkey"
ПОДРОБНОСТИ:  Ключ "(itemid, clock)=(215864, 1592535600)" уже существует.

посмотреть, если ли эти данные не в партицированной таблице (itemid и clock будут написаны в сообщении ошибки):

DELETE FROM trends WHERE itemid='215864' AND clock='1592535600';

удаляем ВСЕ эти данные, за указанный час (это unix time время равное одному часу), возможно, скорее всего, удалятся и другие необходимые данные за этот период времени

DELETE FROM trends WHERE clock='1592535600';

DELETE FROM trends WHERE clock='1603522800';
DELETE FROM trends WHERE clock='1603476000';
DELETE FROM trends WHERE clock='1603515600';
DELETE FROM trends WHERE clock='1603512000';


DELETE FROM trends_uint WHERE clock='1592535600';

DELETE FROM trends_uint WHERE clock='1603522800';
DELETE FROM trends_uint WHERE clock='1603476000';
DELETE FROM trends_uint WHERE clock='1603515600';
DELETE FROM trends_uint WHERE clock='1603512000';

[Антон Теремшонок]

продолжение 2

после того, как всё сделали, надо мигрировать в чанки (надо много свободного места, примерно *1,5 раза от текущего):

SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('trends', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
UPDATE config SET db_extension='timescaledb', hk_history_global=1, hk_trends_global=1;
UPDATE config SET compression_status=1, compress_older='7d';

после окончания запускаем сервисы:

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

и проверяем в базе, например, таблицу history_uint:

SELECT chunk_table,total_bytes FROM chunk_relation_size('history_uint');

Миграция завершена.