PostgreSQL Database Monitoring
Video Lecture
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 Monitoring → Latest 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