Setting up the official PostgreSQL template on Zabbix 4.4

Hello.

Zabbix has an official Template DB PostgreSQL. In this article, we will configure it in Zabbix 4.4.

NOTE

If everything is fine with English, then I recommend installing the template according to the official manual

github.com/zabbix/zabbix/tree/master/templates/db/postgresql

Nevertheless, my article takes into account the nuances that are not on this link.

Template preparation

1. Go to your home directory.

cd ~

2. Download the git utility and clone the official Zabbix repository, which is located on GitHub.

yum -y install git
git clone https://github.com/zabbix/zabbix.git

3. Go to the directory with the PostgreSQL template.

cd zabbix/templates/db/postgresql/

Setting up a template for the Zabbix agent

1. Connect to PostgreSQL.

psql -U postgres

2. Create a read-only user zbx_monitor with access to the PostgreSQL server.

For PostgreSQL version 10 and higher:

CREATE USER zbx_monitor WITH PASSWORD '<ВАШ_ПАРОЛЬ>' INHERIT; GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<ВАШ_ПАРОЛЬ>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

--Для сбора метрик WAL пользователь должен быть superuser.
ALTER USER zbx_monitor WITH SUPERUSER;

3. Copy the postgresql/ directory to the /var/lib/zabbix/ directory. If you do not have a zabbix/ directory in /var/lib/, then create it. The postgresql / directory contains the files needed to get metrics from PostgreSQL.

cp -r postgresql/ /var/lib/zabbix/

4. Then copy the template_db_postgresql.conf file to the Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart the Zabbix agent.

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

5. Now let's edit the pg_hba.conf file to allow connection to Zabbix. More about the pg_hba.conf file: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Open file:

vi /var/lib/pgsql/12/data/pg_hba.conf

Add one of the lines (If you do not understand why this is necessary, then add only the first line.):

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

NOTE

If PostgreSQL is installed from the PGDG repository, add the path to pg_isready to the PATH environment variable for the zabbix user.

Alternatively:

ln -s /usr/pgsql-12/bin/pg_isready /usr/bin/pg_isready

* - since I have pgsql version 12, you will have a different path instead of pgsql-12.

If this is not done, then Status: Ping will always be in the Down state.

Adding a template on the Zabbix frontend

I think that those who need to take metrics from PostgreSQL already know how to add templates. Therefore, I will describe the process briefly.

1. Go to the Zabbix page;
2. Go to page "Configuration"=>"Host“;
3. Click on the button "Create host» or select an existing host;
4. On the page for creating/editing a host, select the tab "templates"and and click on the link"Add“;
5. In "Group" from the list, select "Templates / Databases", select the template "Template DB PostgreSQL", press the button"Select"And press the button"Update“;

We wait a while and finally, go to "Monitoring"=>"Latest data"=>"Hosts» select server with PostgreSQL => click «Apply«.

Enjoy!

Source: habr.com