40 lines
2.7 KiB
SQL
40 lines
2.7 KiB
SQL
-- ############################################################################
|
|
-- SCRIPT DE CONFIGURAÇÃO TIMESCALEDB PARA ZABBIX 7.0 LTS
|
|
-- Execute como usuário postgres: psql -U postgres -d zabbix -f timescaledb_setup.sql
|
|
-- ############################################################################
|
|
|
|
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
|
|
|
|
-- History
|
|
SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
|
|
SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
|
|
SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
|
|
SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
|
|
SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, if_not_exists => TRUE, migrate_data => TRUE);
|
|
|
|
-- Trends
|
|
SELECT create_hypertable('trends', 'clock', chunk_time_interval => 2592000, if_not_exists => TRUE, migrate_data => TRUE);
|
|
SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, if_not_exists => TRUE, migrate_data => TRUE);
|
|
|
|
-- Compressão (History: 1 dia, Trends: 7 dias)
|
|
ALTER TABLE history SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('history', INTERVAL '1 day');
|
|
|
|
ALTER TABLE history_uint SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('history_uint', INTERVAL '1 day');
|
|
|
|
ALTER TABLE history_str SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('history_str', INTERVAL '1 day');
|
|
|
|
ALTER TABLE history_log SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('history_log', INTERVAL '1 day');
|
|
|
|
ALTER TABLE history_text SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('history_text', INTERVAL '1 day');
|
|
|
|
ALTER TABLE trends SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('trends', INTERVAL '7 days');
|
|
|
|
ALTER TABLE trends_uint SET (timescaledb.compress, timescaledb.compress_segmentby = 'itemid', timescaledb.compress_orderby = 'clock DESC');
|
|
SELECT add_compression_policy('trends_uint', INTERVAL '7 days');
|