using pg_partman to help partitioning postgresql for zabbix

WARNINGS:

• PostgreSQL partitioning is not supported by Zabbix SIA technical support
• Changing partition configuration on existing datasets requires a great deal of care and is not recommended for newbies.
• The Zabbix server and web server should be stopped when making changes to DB.

There are some solutions in Internet, but I have used my own with help of pg_partman - pg_partman is an extension to create and manage both time-based and serial-based table partition sets.

These scripts cater for partitioning any Zabbix tables that include the clock column.
Partitioning is recommended for large tables such as history*, trends* and events where the data size might exceed total physical RAM. When selecting a partitioning interval, try find a balance between minimising the number of partitions (e.g. use yearly or monthly), preventing empty partitions and preventing partition size from exceeding total physical RAM.

For current version of pg_partman (>= 3.0.0):
-- Create monthly partitions based on the clock

SELECT partman.create_parent(p_parent_table := 'public.acknowledges', p_control := 'clock',  p_type := 'partman', p_interval := 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.alerts', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.auditlog', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.events', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.service_alarms', 'clock', 'partman', 'monthly', p_epoch := 'seconds');

SELECT partman.create_parent('public.history', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.history_log', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.history_str', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.history_text', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.history_uint', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.trends', 'clock', 'partman', 'monthly', p_epoch := 'seconds');
SELECT partman.create_parent('public.trends_uint', 'clock', 'partman', 'monthly', p_epoch := 'seconds');


For older:
-- Create monthly partitions based on the clock
SELECT partman.create_parent('public.acknowledges', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.alerts', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.auditlog', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.events', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.service_alarms', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);

SELECT partman.create_parent('public.history', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.history_log', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.history_str', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.history_text', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.history_uint', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.trends', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);
SELECT partman.create_parent('public.trends_uint', 'clock', 'time', 'monthly', NULL, 6, NULL, NULL, TRUE, TRUE);


CREATE SEQUENCE events_eventid_seq;
ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;
SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));