Skip to content

PostgreSQL Database Monitoring

Video Lecture

PostgreSQL Database Monitoring in Zabbix

Description

I show how to set up monitoring for a PostgreSQL database server in Zabbix.

Install PostgreSQL

To set up a new PostgreSQL database server on Ubuntu 22.04 LTS, then use these commands.

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo service postgresql start

Check the status.

sudo service postgresql status

Press CTRL C to exit.

If the status shows active running, then you can continue.

Install and Configure Zabbix Agent

You will also need a Zabbix agent running on the server.

My Zabbix server is Zabbix 6.0 LTS, and my server that will be hosting the agent is an Ubuntu 20.04. So I will use these commands.

sudo wget https://repo.zabbix.com/zabbix/6.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_6.0-4%2Bubuntu22.04_all.deb
sudo dpkg -i zabbix-release_6.0-4+ubuntu22.04_all.deb
sudo apt update
sudo apt install zabbix-agent

Visit https://www.zabbix.com/download to know which commands to run for your combination of Zabbix server version and operating system where you will install the agent.

Open the Zabbix agent configuration file, find and set the parameters appropriately.

sudo nano /etc/zabbix/zabbix_agentd.conf

For my setup, I used,

Server=zabbix.sbcode.net
ServerActive=zabbix.sbcode.net
Hostname=postgresql

The values that you enter for the above keys will be different.

CTRL X and then Y to save changes.

Add PostgreSQL Read-Only User

We now need to enter the PostgreSQL (psql) prompt and add a read-only user named zbx_monitor.

sudo -u postgres psql

Add the user.

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Confirm user exists

select * from pg_catalog.pg_user;

Exit back to the bash prompt.

\q

Set Up Zabbix PostgreSQL Template Dependencies

Now to download the Zabbix repository from GitHub. This contains the PostgreSQL database queries used by the template that we will import into Zabbix.

git clone https://github.com/zabbix/zabbix.git

It is a very large repository, so it will take a long time to download. Possibly 10 mins.

After download, you should see a new directory named zabbix.

We now need to copy the ./zabbix/templates/db/postgresql/ folder and contents to the /var/lib/zabbix/ folder.

You may need to first create the folder /var/lib/zabbix

mkdir /var/lib/zabbix

Now run the copy command.

cp -r ./zabbix/templates/db/postgresql/. /var/lib/zabbix/

CD into the /var/lib/zabbix to check that the folders and contents were copied ok.

cd /var/lib/zabbix
ls

We also need to copy just the template_db_postgresql.conf file to /etc/zabbix/zabbix_agentd.d/

cp /var/lib/zabbix/template_db_postgresql.conf /etc/zabbix/zabbix_agentd.d/

Check it copied ok.

cd /etc/zabbix/zabbix_agentd.d/
ls

Configure PostgreSQL pg_hba.conf File

Now we need to edit the pg_hba.conf to allow the zbx_monitor user to connect.

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add these lines to the end

host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5

CTRL X and then Y to save changes.

Configure Host and Template in Zabbix

Restart Zabbix Agent and check status.

sudo service zabbix-agent restart
sudo service zabbix-agent status

Go into Zabbix and create a new host named 'postgresql'.

Assign the template PostgeSQL by Zabbix agent.

Add to any group you desire. Templates/Databases is a good option.

Add an Agent interface, and set the IP address of your PostgreSQL server.

Select the Macros tab and check/set each macro key. (Most keys will be already set to the correct value)

Macro key Value
{$PG.HOST} 127.0.0.1
{$PG.PORT} 5432
{$PG.USER} zbx_monitor
{$PG.PASSWORD} the same password you used above when creating zbx_monitor the user
{$PG.DB} postgres

Press Update and after some time, you will see new values populating in MonitoringLatest Data

Trouble Shooting

Special characters "\, ', ", \*, ?, [, ], {, }, ~, $, !, &, ;, (, ), <, >, |, #, @, 0x0a" are not allowed in the parameters.

If you used the password <PASSWORD> as I did in the video, you will have an error indicating you have special characters in the macro. To fix this, I updated the password I used for my zbx_monitor user in PostgreSQL using these commands.

sudo -u postgres psql
ALTER USER zbx_monitor WITH PASSWORD 'new_password';
\q

Next go into the macros tab of your host and update the {$PG.PASSWORD} macro to match the new password.

Recommendation

The messages relayed between the Zabbix server and Zabbix agent running on the PostgreSQL Database server, are not encrypted. I recommend configuring PSK encryption for agents that communicate with the Zabbix server directly across a public network.

See Enable PSK Encryption for Zabbix Agents

Comments