Zabbix/partitioning
0
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
partitioning/postgresql/procedures/MANUAL.md

7.3 KiB
Raw Permalink Blame History

Zabbix Partitioning Deployment Manual

This guide provides a step-by-step process for deploying the PostgreSQL partitioning solution for Zabbix.

🚨 DANGER: CRITICAL WARNING 🚨 BEFORE YOU PROCEED, YOU ABSOLUTELY MUST TAKE A FULL BACKUP OF YOUR ZABBIX DATABASE. DO NOT SKIP THIS STEP. Schema modifications are dangerous. If something goes wrong and you do not have a backup, your historical data will be lost permanently, and we take ZERO responsibility.

Step 1: Preparation & Safety

Because database migrations can take time (especially on large tables), never run these scripts directly in a standard SSH session that might disconnect.

  1. Open a safe terminal session using tmux or screen: 
    tmux new -s zabbix_partitioning
# OR
screen -S zabbix_partitioning
  2. Disable the Zabbix Housekeeper for History and Trends:
    • Go to your Zabbix Web UI -> Administration -> Housekeeping.
    • Uncheck "Enable internal housekeeping" for History and Trends.
    • Click Update.
  3. Stop your Zabbix Server to ensure no new data is being written during the schema migration: 
    sudo systemctl stop zabbix-server

Step 2: Database Connection & Schema Selection

Connect to your PostgreSQL server as an administrator (e.g., postgres or the database owner).

psql -U postgres -h localhost

Once inside psql, connect to your Zabbix database (usually named zabbix):

\c zabbix

Important

Custom Schemas: By default, Zabbix installs into the public schema. If you installed Zabbix into a custom schema (e.g., zabbix_schema), you must set your search_path now before running the scripts, otherwise they will fail to find your tables:

SET search_path TO zabbix_schema, public;

Step 3: Execute Installation Scripts

Run the scripts in the following exact order. You can execute them directly from your bash terminal, from within the interactive psql console, or by pasting their contents into a GUI tool (like pgAdmin).

Note

Zabbix 8.0+ Users: Zabbix 8.0 introduced a new history_json table. Before running the first script, open 00_schema_create.sql in a text editor and uncomment the lines specifically marked for Zabbix 8.0 at the end of the history tables block.

Option A: From bash terminal (Recommended)

If you are in your normal Linux terminal, run these commands one by one:

psql -U postgres -d zabbix -f 00_schema_create.sql
psql -U postgres -d zabbix -f 01_maintenance.sql
# MIGRATION STEP: The next script renames your existing large tables to `_old` 
# and instantly creates new partitioned tables. This might take a few moments.
psql -U postgres -d zabbix -f 02_enable_partitioning.sql
psql -U postgres -d zabbix -f 03_monitoring_view.sql

Option B: Inside interactive psql

If you already connected via psql (as in Step 2), use the \i command:

\i 00_schema_create.sql
\i 01_maintenance.sql
\i 02_enable_partitioning.sql
\i 03_monitoring_view.sql

Option C: GUI Tools (pgAdmin, DBeaver, etc.)

If you use a visual database manager, simply open each file in your query editor and execute them one by one in the exact order (00, 01, 02, 03).

Step 4: Schedule Automated Maintenance

Partitioning requires a daily job to create new partitions for tomorrow and drop old partitions from last month.

Warning

CRITICAL EXECUTION RULE: You MUST execute the maintenance procedure as a standalone command exactly as shown below. DO NOT wrap it in a transaction block (BEGIN; ... COMMIT;) or batch it with other SQL commands in a single string, or it will crash with an invalid transaction termination error.

If you are using AWS RDS or a managed database with pg_cron enabled, run this inside psql:

CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('zabbix_partition_maintenance', '30 5,23 * * *', 'CALL partitions.run_maintenance();');

If you are self-hosting and prefer standard system cron, simply add this to your crontab -e:

# Run Zabbix partition maintenance twice daily (5:30 AM and 11:30 PM)
30 5,23 * * * psql -U postgres -d zabbix -c "CALL partitions.run_maintenance();" >> /var/log/zabbix_maintenance.log 2>&1

Step 5: Start Zabbix Server

Now that the database is fully partitioned, you can safely start Zabbix Server again:

sudo systemctl start zabbix-server

(Note: Your old history data remains safely preserved in tables like history_old. It is no longer visible in the UI, but it is available in the database if you ever need to manually migrate it.)

Step 6: Configure Zabbix Agent Monitoring

To ensure your partitions don't run out, you must monitor them. We use Zabbix Agent 2 for this.

  1. On your database server (where Zabbix Agent 2 is installed), create the SQL query file using this simple one-liner. Copy and paste the entire block below into your terminal:
cat << 'EOF' | sudo tee /etc/zabbix/zabbix_agent2.d/partitions.get_all.sql > /dev/null
SELECT 
    table_name,
    period,
    keep_history::text AS keep_history,
    configured_future_partitions,
    actual_future_partitions,
    total_size_bytes,
    EXTRACT(EPOCH FROM (now() - last_updated)) AS age_seconds
FROM partitions.monitoring;
EOF

(Note: If your Zabbix Agent is configured to look for custom queries in a different directory like /etc/zabbix/postgresql/sql/, modify the destination path above).

  1. Configure the PostgreSQL Plugin by editing /etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf. Ensure you have defined a session (e.g., MY_DB) and enabled custom queries:
Plugins.PostgreSQL.CustomQueriesPath=/etc/zabbix/zabbix_agent2.d/
Plugins.PostgreSQL.CustomQueriesEnabled=true

# Example Session (replace with your actual credentials)
Plugins.PostgreSQL.Sessions.MY_DB.Uri=tcp://localhost:5432
Plugins.PostgreSQL.Sessions.MY_DB.User=zbx_monitor
Plugins.PostgreSQL.Sessions.MY_DB.Password=your_password
  1. Restart the Zabbix Agent 2:
sudo systemctl restart zabbix-agent2

Step 7: Import Template in Zabbix

  1. Log into your Zabbix Web UI.
  2. Go to Data collection -> Templates and click Import.
  3. Upload the template/zbx_pg_partitions_monitor_agent2.yaml file from this repository.
  4. Go to your Database Host in Zabbix, and link the newly imported template: PostgreSQL Partitioning by Zabbix Agent 2.
  5. On the Host configuration, go to the Macros tab.
  6. You will see a macro named {$PG.CONNSTRING.AGENT2} with the value <replace_me>.
  7. Change <replace_me> to the name of the session you configured in Step 6 (e.g., MY_DB).
  8. Click Update.

Congratulations! Your Zabbix database is now fully partitioned, optimized, and monitored.

Step 8: Upgrades & Updates

To apply future updates or fixes to the partitioning logic, the scripts are fully idempotent and safe to re-run on a live database. Simply connect to psql and re-execute the updated scripts in order:

psql -U postgres -d zabbix -f 00_schema_create.sql
psql -U postgres -d zabbix -f 01_maintenance.sql
psql -U postgres -d zabbix -f 02_enable_partitioning.sql
psql -U postgres -d zabbix -f 03_monitoring_view.sql

Warning

If you update the agent SQL query, always remember to restart the agent (sudo systemctl restart zabbix-agent2) so it flushes its cache!