Would you like to learn how to monitor a PostgreSQL server using Zabbix? In this tutorial, we are going to show you how to use Zabbix to monitor a PostgreSQL server.
Zabbix Related Tutorial:
On this page, we offer quick access to a list of tutorials related to Zabbix installation.
Configure a PostgreSQL user to Zabbix
• IP – 192.168.15.11
• Operational System – Ubuntu 19.10
• Hostname – POSTGRESQL
Zabbix requires an account to access the PostgreSQL server and perform the database monitoring.
On the PostgreSQL database server, create an account to Zabbix.
Give monitoring privilege over all PostgreSQL databases to the Zabbix user.
CREATE USER zbx_monitor WITH PASSWORD 'kamisama123' INHERIT;
GRANT pg_monitor TO zbx_monitor;
Take note of the PostgreSQL username and password created.
Locate and edit the pg_hba.conf configuration file.
updatedb
locate pg_hba.conf
vi /etc/postgresql/11/main/pg_hba.conf
Add the following lines at the beginning of this file.
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
Here is our pg_hba.conf file content:
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
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
Restart the PostgreSQL service.
service postgresql restart
Test if you are able to connect to the local PostgreSQL server using the zbx_monitor account.
The system should not ask for a password.
psql -h 127.0.0.1 --username=zbx_monitor postgres
You have finished the PostgreSQL service configuration.
Install the Zabbix Agent on the PostgreSQL Server
• IP – 192.168.15.11
• Operational System – Ubuntu 19.10
• Hostname – POSTGRESQL
Now, we need to install the Zabbix agent on the computer running the PostgreSQL service.
On the Linux console, use the following commands to install the required packages.
groupadd zabbix
useradd -g zabbix -s /bin/bash zabbix
apt-get update
apt-get install build-essential libpcre3-dev
Download the Zabbix installation package.
mkdir /downloads
cd /downloads
wget https://ufpr.dl.sourceforge.net/project/zabbix/ZABBIX%20Latest%20Stable/4.4.1/zabbix-4.4.1.tar.gz
Extract the Zabbix installation package, compile and install the Zabbix agent.
tar -zxvf zabbix-4.4.1.tar.gz
cd zabbix-4.4.1
./configure --enable-agent
make
make install
Find the location of the zabbix_agentd.conf file on your system.
Edit the zabbix_agentd.conf file.
updatedb
locate zabbix_agentd.conf
vi /usr/local/etc/zabbix_agentd.conf
Here is the original file, before our configuration.
LogFile=/tmp/zabbix_agentd.log
Server=127.0.0.1
ServerActive=127.0.0.1
Hostname=Zabbix server
Here is the new file with our configuration.
LogFile=/tmp/zabbix_agentd.log
Server=127.0.0.1,192.168.15.10
ServerActive=192.168.15.10
Include=/usr/local/etc/zabbix_agentd.conf.d/
In our example, the Zabbix agent is configured to allow the connection from the Zabbix server 192.168.15.10.
The Zabbix server with the IP address 192.168.15.10 is allowed to request and receive information from this agent.
The Localhost, 127.0.0.1, is allowed to request and receive information from the agent.
Create the following required directories.
mkdir /usr/local/etc/zabbix_agentd.conf.d -p
mkdir /var/lib/zabbix -p
Download the Zabbix source code from Github.
cd /downloads
git clone https://github.com/zabbix/zabbix
Copy the required files to monitor the PostgreSQL server from the Zabbix repository.
cp /downloads/zabbix/templates/db/postgresql/template_db_postgresql.conf /usr/local/etc/zabbix_agentd.conf.d/
cp /downloads/zabbix/templates/db/postgresql/postgresql /var/lib/zabbix -R
The Zabbix installation package comes with a service startup script.
Copy the startup script using the commands below.
cd /downloads/zabbix-4.4.1/
cp misc/init.d/debian/zabbix-agent /etc/init.d/
You can now use the following commands to start the Zabbix agent service.
/etc/init.d/zabbix-agent start
In our example, the Zabbix agent will monitor the PostgreSQL service installed on the localhost.
Restart the Zabbix Agent.
/etc/init.d/zabbix-agent restart
You have finished the Zabbix agent installation.
You can now use the Zabbix server dashboard to add this computer to the network monitoring service.
Zabbix Monitor PostgreSQL
Access the Zabbix server dashboard and add the Linux computer running PostgreSQL as a Host.
Open your browser and enter the IP address of your web server plus /zabbix.
In our example, the following URL was entered in the Browser:
• http://192.168.15.10/zabbix
On the login screen, use the default username and default password.
• Default Username: Admin
• Default Password: zabbix
After a successful login, you will be sent to the Zabbix Dashboard.
On the dashboard screen, access the Configuration menu and select the Host option.
On the top right of the screen, click on the Create host button.
Enter the following information:
• Host Name – Enter a Hostname to identify the Linux server running PostgreSQL.
• Visible Hostname – Repeat the hostname.
• Group – Select the name of a group to identify similar devices.
• Agent Interface – Enter the IP address of the Linux server.
Here is the original image, before our configuration.
Here is the new image with our configuration.
Next, we need to associate the host with a specific network monitor template.
By default, Zabbix comes with a large variety of monitoring templates.
Access the Templates tab on the top of the screen.
Click on the Select button and locate the template named: Template DB PostgreSQL
Click on the Add option.
Click on the Add button.
After a few minutes, you will be able to see the initial result on the Zabbix Dashboard.
In order to test your configuration, access the Monitoring menu and click on the Graphs option.
On the top right of the screen, select the group named ALL.
Select your PostgreSQL computer hostname.
Select the graph named: PostgreSQL connections
You should be able to see the graphic named PostgreSQL connections
Congratulations! You have configured the Zabbix server to monitor PostgreSQL.
Desde https://techexpert.tips/zabbix/monitor-postgresql-using-zabbix/