Hero Background
Zabbix & PostgreSQL

How to Install Zabbix 7.0 LTS with PostgreSQL 14 & Automatic Partitioning via pg_partman

A production-grade guide to deploying enterprise monitoring with native database partitioning for zero-IO cleanup overhead.

Architectural Justification

1. Why Choose PostgreSQL over MySQL for Production?

In large-scale enterprise monitoring environments, the database faces a massive volume of continuous write transactions (High-Write Throughput). PostgreSQL is selected for the following technical reasons:

  • Superior Concurrency Management (MVCC): PostgreSQL’s Multiversion Concurrency Control prevents table locking during concurrent read/write operations, delivering significantly better performance under heavy Zabbix dashboard queries and API requests.
  • Native Partitioning Efficiency: Starting with PostgreSQL 10 and heavily optimized in version 14+, Native Partitioning minimizes system overhead compared to other storage engines.
  • Data Integrity and Crash Recovery: PostgreSQL’s Write-Ahead Logging (WAL) and crash recovery mechanisms provide superior stability and reliability for critical monitoring infrastructure.

2. Why Use pg_partman Instead of Zabbix Internal Housekeeper?

By default, the Zabbix internal Housekeeper process purges old data using standard DELETE FROM history WHERE ... queries. In a production environment, this approach creates two major performance bottlenecks:

  • Database Locking and IO Spikes: Running millions of row-by-row DELETE queries leads to severe CPU and IO saturation, often causing Zabbix Server to drop incoming metrics.
  • Database Bloat (Unreleased Disk Space): Standard DELETE operations only mark space as reusable without returning it to the operating system. The only way to reclaim disk space is by running a manual VACUUM FULL, which completely locks the table during execution.

The Solution with pg_partman:

By implementing daily range partitioning, historical data retention is handled via DROP TABLE operations on expired child partitions. Executing a DROP TABLE takes only a few milliseconds, places zero load on database CPU/IO, and instantly returns 100% of the freed disk space to the operating system.

Complete Installation & Configuration Runbook

Step 1: System Update & Dependencies

Update system repositories and install essential toolkits, including the required PHP PostgreSQL driver:

apt update && apt upgrade -y
apt install -y curl ca-certificates lsb-release gnupg wget php-pgsql php8.1-pgsql

Step 2: Add Zabbix 7.0 Repository

Download and install the official Zabbix 7.0 LTS repository package for Ubuntu 22.04:

wget https://repo.zabbix.com/zabbix/7.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_latest_7.0+ubuntu22.04_all.deb
dpkg -i zabbix-release_latest_7.0+ubuntu22.04_all.deb
apt update

Step 3: Install Zabbix, PostgreSQL, and pg_partman

Install PostgreSQL 14, the pg_partman extension, Zabbix Server, Frontend, Agent, and Nginx components:

apt install -y postgresql-14 postgresql-14-partman zabbix-server-pgsql zabbix-frontend-php zabbix-nginx-conf zabbix-sql-scripts zabbix-agent

Step 4: Database & Extension Provisioning

Initialize the database, create the zabbix database user, and enable the pg_partman extension under a dedicated schema.

sudo -u postgres psql

Execute the following SQL queries:

-- Create database user and schema
CREATE USER zabbix WITH PASSWORD 'Your_Strong_Password';
CREATE DATABASE zabbix OWNER zabbix;

\c zabbix

-- Initialize pg_partman schema and extension
CREATE SCHEMA IF NOT EXISTS partman AUTHORIZATION zabbix;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- Grant schema-level privileges
GRANT ALL ON SCHEMA partman TO zabbix;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO zabbix;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA partman TO zabbix;
GRANT ALL ON ALL PROCEDURES IN SCHEMA partman TO zabbix;

\q

Step 5: Import Base Zabbix Schema

Import initial tables and data provided by the Zabbix installer:

zcat /usr/share/zabbix-sql-scripts/postgresql/server.sql.gz | sudo -u zabbix psql zabbix

Step 6: Configure Native Table Partitioning

Convert standard Zabbix history* and trends* tables into PostgreSQL Native Range Partitioned tables.

Note: Zabbix stores clock times as Unix Timestamps (integers). Therefore, partition intervals must be defined in seconds (86400 = 1 day, 2592000 = 30 days). 
sudo -u postgres psql -d zabbix

Execute the following DDL and pg_partman procedures:

-- 1. Drop default non-partitioned tables
DROP TABLE IF EXISTS history, history_uint, history_str, history_text, history_log CASCADE;
DROP TABLE IF EXISTS trends, trends_uint CASCADE;

-- 2. Re-create tables with PARTITION BY RANGE (clock)
CREATE TABLE history (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value numeric(16,4) DEFAULT '0.0000' NOT NULL,
    ns integer DEFAULT 0 NOT NULL
) PARTITION BY RANGE (clock);

CREATE TABLE history_uint (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value numeric(20,0) DEFAULT '0' NOT NULL,
    ns integer DEFAULT 0 NOT NULL
) PARTITION BY RANGE (clock);

CREATE TABLE history_str (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value character varying(255) DEFAULT ''::character varying NOT NULL,
    ns integer DEFAULT 0 NOT NULL
) PARTITION BY RANGE (clock);

CREATE TABLE history_text (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value text DEFAULT ''::text NOT NULL,
    ns integer DEFAULT 0 NOT NULL
) PARTITION BY RANGE (clock);

CREATE TABLE history_log (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    timestamp integer DEFAULT 0 NOT NULL,
    source character varying(64) DEFAULT ''::character varying NOT NULL,
    severity integer DEFAULT 0 NOT NULL,
    value text DEFAULT ''::text NOT NULL,
    logeventid integer DEFAULT 0 NOT NULL,
    ns integer DEFAULT 0 NOT NULL
) PARTITION BY RANGE (clock);

CREATE TABLE trends (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    num integer DEFAULT 0 NOT NULL,
    value_min numeric(16,4) DEFAULT '0.0000' NOT NULL,
    value_avg numeric(16,4) DEFAULT '0.0000' NOT NULL,
    value_max numeric(16,4) DEFAULT '0.0000' NOT NULL,
    PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

CREATE TABLE trends_uint (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    num integer DEFAULT 0 NOT NULL,
    value_min numeric(20,0) DEFAULT '0' NOT NULL,
    value_avg numeric(20,0) DEFAULT '0' NOT NULL,
    value_max numeric(20,0) DEFAULT '0' NOT NULL,
    PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

-- 3. Create parent partitions via pg_partman
SELECT partman.create_parent('public.history', 'clock', 'native', '86400', p_premake => 7);
SELECT partman.create_parent('public.history_uint', 'clock', 'native', '86400', p_premake => 7);
SELECT partman.create_parent('public.history_str', 'clock', 'native', '86400', p_premake => 7);
SELECT partman.create_parent('public.history_text', 'clock', 'native', '86400', p_premake => 7);
SELECT partman.create_parent('public.history_log', 'clock', 'native', '86400', p_premake => 7);

SELECT partman.create_parent('public.trends', 'clock', 'native', '2592000', p_premake => 3);
SELECT partman.create_parent('public.trends_uint', 'clock', 'native', '2592000', p_premake => 3);

-- 4. Set Retention Policy (pg_partman v5 configuration table)
UPDATE partman.part_config SET retention = '30 days', retention_keep_table = false WHERE parent_table LIKE 'public.history%';
UPDATE partman.part_config SET retention = '365 days', retention_keep_table = false WHERE parent_table LIKE 'public.trends%';

-- 5. Grant Permissions on New Tables and Future Child Partitions (CRITICAL)
GRANT ALL ON SCHEMA public TO zabbix;
GRANT ALL ON SCHEMA partman TO zabbix;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO zabbix;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO zabbix;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO zabbix;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA partman TO zabbix;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO zabbix;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO zabbix;
ALTER DEFAULT PRIVILEGES IN SCHEMA partman GRANT ALL ON TABLES TO zabbix;

\q

Step 7: Configure Maintenance Cron Job

Schedule the daily partition maintenance task so pg_partman automatically creates future tables and drops expired partitions without requiring manual intervention.

Open root crontab:

crontab -e

Add the following entry (runs daily at 01:00 AM):

0 1 * * * sudo -u postgres psql -d zabbix -c "CALL partman.run_maintenance_proc();" > /dev/null 2>&1

Step 8: Configure Zabbix Server & Nginx

Update Zabbix Server Database Password:

nano /etc/zabbix/zabbix_server.conf

Set your database password:

DBPassword=Your_Strong_Password

Configure Nginx for Zabbix:

nano /etc/zabbix/nginx.conf

Uncomment and adjust the listening directives:

listen 80;
server_name _;

Enable Zabbix Configuration in Nginx:

rm /etc/nginx/sites-enabled/default
ln -s /etc/zabbix/nginx.conf /etc/nginx/conf.d/zabbix.conf
nginx -t

Step 9: Service Management

Start and enable all relevant services on system boot:

systemctl restart zabbix-server zabbix-agent nginx php8.1-fpm postgresql
systemctl enable zabbix-server zabbix-agent nginx php8.1-fpm postgresql

Step 10: Web Setup Wizard & Post-Installation Tasks

Navigate to https://SERVER_IP in your web browser.

Complete the setup wizard:

  • Select PostgreSQL as the Database Type.
  • Enter Database Host (localhost), Database Name (zabbix), User (zabbix), and Password (Your_Strong_Password).
  • Set Default Timezone to Asia/Tehran.

Log in with default credentials:

  • Username: Admin
  • Password: zabbix

CRITICAL: Disable Zabbix Internal Housekeeper

Since database retention is fully offloaded to pg_partman, disable internal housekeeping to avoid high CPU/IO database locks:

  1. Navigate to Administration ➔ General ➔ Housekeeping.
  2. Uncheck Enable internal housekeeping for both History and Trends.
  3. Click Update.
← Back to Home