60 lines
3.0 KiB
SQL
60 lines
3.0 KiB
SQL
-- ============================================================================
|
|
-- Converts standard Zabbix tables to Partitioned tables.
|
|
-- WARNING: This renames existing tables to *_old.
|
|
-- ============================================================================
|
|
|
|
DO $$
|
|
DECLARE
|
|
v_row record;
|
|
v_table text;
|
|
v_old_table text;
|
|
v_pk_sql text;
|
|
v_schema text;
|
|
BEGIN
|
|
FOR v_row IN SELECT * FROM partitions.config LOOP
|
|
v_table := v_row.table_name;
|
|
v_old_table := v_table || '_old';
|
|
|
|
-- Determine schema
|
|
SELECT n.nspname INTO v_schema
|
|
FROM pg_class c
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE c.relname = v_table;
|
|
|
|
|
|
IF EXISTS (SELECT 1 FROM pg_class WHERE relname = v_table AND relkind = 'r') THEN
|
|
RAISE NOTICE 'Converting table % to partitioned table...', v_table;
|
|
|
|
-- 1. Rename existing table
|
|
EXECUTE format('ALTER TABLE %I.%I RENAME TO %I', v_schema, v_table, v_old_table);
|
|
|
|
-- 2. Create new partitioned table (handling auditlog PK uniquely)
|
|
IF v_table = 'auditlog' THEN
|
|
EXECUTE format('CREATE TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING COMMENTS) PARTITION BY RANGE (clock)', v_schema, v_table, v_schema, v_old_table);
|
|
EXECUTE format('ALTER TABLE %I.%I ADD PRIMARY KEY (auditid, clock)', v_schema, v_table);
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS auditlog_p_1 ON %I.%I (userid, clock)', v_schema, v_table);
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS auditlog_p_2 ON %I.%I (clock)', v_schema, v_table);
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS auditlog_p_3 ON %I.%I (resourcetype, resourceid)', v_schema, v_table);
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS auditlog_p_4 ON %I.%I (recordsetid)', v_schema, v_table);
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS auditlog_p_5 ON %I.%I (ip)', v_schema, v_table);
|
|
ELSE
|
|
EXECUTE format('CREATE TABLE %I.%I (LIKE %I.%I INCLUDING ALL) PARTITION BY RANGE (clock)', v_schema, v_table, v_schema, v_old_table);
|
|
END IF;
|
|
|
|
-- 3. Create initial partitions
|
|
RAISE NOTICE 'Creating initial partitions for %...', v_table;
|
|
CALL partitions.maintain_table(v_table, v_row.period, v_row.keep_history, v_row.future_partitions);
|
|
|
|
-- Optional: Migrate existing data
|
|
-- EXECUTE format('INSERT INTO %I.%I SELECT * FROM %I.%I', v_schema, v_table, v_schema, v_old_table);
|
|
|
|
ELSIF EXISTS (SELECT 1 FROM pg_class WHERE relname = v_table AND relkind = 'p') THEN
|
|
RAISE NOTICE 'Table % is already partitioned. Skipping conversion.', v_table;
|
|
-- Just run maintenance to ensure partitions exist
|
|
CALL partitions.run_maintenance();
|
|
ELSE
|
|
RAISE WARNING 'Table % not found!', v_table;
|
|
END IF;
|
|
END LOOP;
|
|
END $$;
|