Configuring the official PostgreSQL template on Zabbix 4.4

Hello.



The official PostgreSQL Template DB has appeared in Zabbix. 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 available on this link.

Template preparation

1. Go to the 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 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 the zabbix / directory in / var / lib /, 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 edit the pg_hba.conf file to allow connection to Zabbix. More information about the pg_hba.conf file: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html .



Open the 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.



As an option:

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

* - since I have pgsql 12 version, 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 believe that those who need to remove metrics from PostgreSQL know how to add templates. Therefore, I will describe the process briefly.

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

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





Enjoy!