RCFMARTIN

Why a Standalone Database

The day you outgrow the all-in-one Zabbix install is the day a server-side restart drops your monitoring history mid-query. Every Zabbix server, every proxy, every frontend talks to the same database; if that database is sharing CPU and disk with one of those services, the moment any of them spikes, every other read in the cluster slows down with it.

Three concrete reasons the database has to live on its own host before HA stops being a marketing word:

Failure domains are not the same. The Zabbix server process can crash, get OOM-killed, or run out of file descriptors during a poller surge. None of those events should require touching the database. Keeping them on separate hosts means a server-tier incident is one restart, not a recovery exercise.
Resource contention is invisible until it isn't. A single slow housekeeper query competing with pollers for I/O on the same SSD shows up as random latency in the frontend, not as a database alert. Splitting them gives the database its own iowait number to monitor.
Scaling at all becomes possible. Read replicas, connection pooling via pgbouncer, partitioning the history_* tables, faster disks for pg_xlog only those moves are tractable on a host that does nothing else.

This post is the standalone-database build. The rest of the Zabbix HA series (servers, frontend, proxy, agents) assumes the database lives at the address we configure here.

Server Setup

As I mentioned on my previous post, I'll be using PostgreSQL as the main database, and Ubuntu 24.04 as the base operating system.

This guide assumes that you are capable of deploying an ubuntu server on your own. If not, please follow this guide before proceeding.

1. Create a new user on the server

Although not mandatory, it is recommended that you create a dedicated user to access the database. We'll create a user named zabbix. The command is very simple:

sudo adduser zabbix

2. Install PostgreSQL on the server

After creating the new user, we need to install the database.

sudo apt install -y postgresql postgresql-contrib

3. Configure the database

Now that we have PostrgreSQL installed, we need configure the database.

Create new users

We'll be creating a few users on the database:

zabbix: This user will be used to connect the zabbix servers to the database
zabbixweb: This user will be used to connect the zabbix frontend to the database

In cluster mode, the frontend connects to the database to check the availability of each server in the cluster.

Run the command below to create the user zabbix

sudo -u postgres createuser --pwprompt zabbix

Run the command below to create the user zabbixweb

sudo -u postgres createuser --pwprompt zabbixweb

Create a new database

We'll create a database named zabbix

sudo -u postgres createdb -O zabbix zabbix

Grant permissions

Now that we have created the users, we need to grant access to the database.

1. Connect to the database with the user `postgres`

sudo -u postgres psql zabbix

2. Allow users to connect to the database

GRANT CONNECT ON DATABASE zabbix TO zabbix;
GRANT CONNECT ON DATABASE zabbix TO zabbixweb;

3. Grant read access to the database

GRANT pg_read_all_data TO zabbix;
GRANT pg_read_all_data TO zabbixweb;

4. Grant write access to the database

GRANT pg_write_all_data TO zabbix;
GRANT pg_write_all_data TO zabbixweb;

4. Make the database available on LAN

In order to make the database available for other devices on the LAN, we need to modify the PostgreSQL config file. To find the location of your config file run the command below:

sudo -u postgres psql -c "SHOW config_file;"

The output will look like the image below

postgresconfig

In my case the config file is located at /etc/postgresql/16/main/postgresql.conf. Use the text editor of your choice and open the config file.

sudo vim /etc/postgresql/16/main/postgresql.conf

Search for listen_addresses and add listen_addresses = '*' to the config file.

#listen_addresses = 'localhost'
listen_addresses = '*'

In the same directory as the postgresql.conf file, you'll find the pg_hba.conf file. Open that file and include the lines below

# TYPE  DATABASE      USER    ADDRESS     METHOD
host     all          all     0.0.0.0/0    scram-sha-256
host     all          all     ::/0         scram-sha-256
hostssl  zabbix       zabbix  0.0.0.0/0    scram-sha-256 # allow connection over ssl with user zabbix

To apply the configuration, reload the server

sudo systemctl restart postgresql

Make sure your server has a static IP address. Below is the /etc/netplan/*.yaml config I'm using

10.0.0.20: Zabbixdb address
10.0.0.1: Gateway
10.0.0.200: Internal DNS server

network:
  version: 2
  renderer: networkd
  ethernets:
    eth0:
      addresses:
        - 10.0.0.20/24
      routes:
        - to: default
          via: 10.0.0.1
      nameservers:
        addresses: [10.0.0.200]
        search: [ns01.example.com]

What You Have Now

A PostgreSQL 16 instance reachable on 10.0.0.20:5432, bound to all interfaces, with two scoped accounts (zabbix for the server-side daemons, zabbixweb for the frontend), pg_hba.conf permitting SCRAM-SHA-256 auth from the corp network, and SSL-only access enforced for the zabbix account. The zabbix database itself is empty, an upcoming step on the server post imports the schema from the official create.sql.gz.

Before You Move On

Three things to verify on this host before you build the Zabbix server tier on top of it. Each one is a 30-second check that prevents an hour of debugging later:

Confirm both accounts can authenticate from off-host. From any other LAN host, run psql -h 10.0.0.20 -U zabbixweb -d zabbix -c '\conninfo' and supply the password. Failures here mean pg_hba.conf or the firewall, not your Zabbix configuration.
Snapshot the empty schema. pg_dump -U zabbix zabbix > /var/backups/zabbix-pre-schema.sql gives you a tiny rollback artifact you can restore over a botched schema import.
Pin the PostgreSQL major version. Apply apt-mark hold postgresql postgresql-contrib so an unattended-upgrades run never silently moves you from 16 to 17 mid-quarter; a Zabbix database migration is a planned event.

Next in the Series

The Zabbix server post imports the schema, configures the active/passive HA pair, and points both nodes at the address we just configured. Read that post next, or jump ahead to the proxy post if your environment already has a working server pair and you are scaling out collection.

Standalone PostgreSQL for Zabbix HA