How to Configure Nagios to Monitor a PostgreSQL 16 Database with Dashboards on Ubuntu 24.04 LTS #postgresql #postgres #nagios

inchirags@gmail.com Chirag PostgreSQL DBA Tutorial https://www.chirags.in

*************************************************************************************

How to Configure Nagios to Monitor a PostgreSQL 16 Database with Dashboards on Ubuntu 24.04 LTS

*************************************************************************************

Here's a step-by-step guide to configure Nagios to monitor a PostgreSQL 16 database with dashboard access on Ubuntu 24.04 LTS.

🧭 Architecture
--------------------------------------------------------------------------------------
Role            IP Address        Description
--------------------------------------------------------------------------------------
Nagios Server        192.168.136.129        Hosts Nagios Core + Dashboards
PostgreSQL Server    192.168.136.130        PostgreSQL 16 server being monitored
--------------------------------------------------------------------------------------

Default password everywhere: admin@123

Step 1: Install PostgreSQL 16 (on 192.168.136.130)

If PostgreSQL is already installed and running, skip this step.

sudo apt update
sudo apt install postgresql-16 postgresql-contrib -y
sudo systemctl enable --now postgresql

Set password for the postgres user:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'admin@123';"

Step 2: Enable PostgreSQL Remote Access (on 192.168.136.130)

Edit postgresql.conf:

sudo nano /etc/postgresql/16/main/postgresql.conf

Find and change:

listen_addresses = '*'

Edit pg_hba.conf:

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

Add at the end:

host    all             all             192.168.136.129/32         md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 3: Create Monitoring User in PostgreSQL (on 192.168.136.130)

sudo -u postgres psql

Inside psql:

CREATE USER nagios WITH PASSWORD 'admin@123';
GRANT CONNECT ON DATABASE postgres TO nagios;
\q

Step 4: Install Nagios Core on Server1 (192.168.136.129)

Step 4.1: Install Dependencies

sudo apt update

sudo apt install nagios-plugins-basic nagios-plugins-standard -y

sudo apt install -y apache2 php php-gd unzip build-essential libgd-dev libapache2-mod-php \
                    libperl-dev libssl-dev daemon wget libnet-snmp-perl gettext

Step 4.2: Add Nagios User

sudo useradd nagios
sudo groupadd nagcmd
sudo usermod -a -G nagcmd nagios
sudo usermod -a -G nagcmd www-data

Step 4.3: Download and Compile Nagios

cd /tmp

wget https://assets.nagios.com/downloads/nagioscore/releases/nagios-4.5.1.tar.gz

tar xvf nagios-4.5.1.tar.gz

cd nagios-4.5.1

./configure --with-command-group=nagcmd

make all

sudo make install

sudo make install-commandmode

sudo make install-init

sudo make install-config

sudo make install-webconf

Step 4.4: Create Nagios Web User

sudo htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

# Enter: admin@123

Enable Apache modules:

sudo a2enmod rewrite cgi
sudo systemctl restart apache2

Start Nagios:

sudo systemctl enable --now nagios

Step 5: Install Nagios Plugins and NRPE

On Server2 (PostgreSQL server: 192.168.136.130):

sudo apt install nagios-nrpe-server nagios-plugins -y

Allow Server1 IP in /etc/nagios/nrpe.cfg:

sudo nano /etc/nagios/nrpe.cfg

Edit:

allowed_hosts=127.0.0.1,192.168.136.129

Restart NRPE:

sudo systemctl restart nagios-nrpe-server

Step 6: Install PostgreSQL Plugin for Nagios (192.168.136.129)

On Server1 (Nagios):

cd /usr/local/nagios/libexec

wget https://raw.githubusercontent.com/bucardo/check_postgres/master/check_postgres.pl

chmod +x check_postgres.pl

Plugins get installed to /usr/local/nagios/libexec

Step 7: Configure Nagios to Monitor PostgreSQL

Edit command file:

sudo nano /usr/local/nagios/etc/objects/commands.cfg

Add:

define command {
    command_name check_postgres_connection
    command_line /usr/local/nagios/libexec/check_postgres.pl --action=connection --dbhost=192.168.136.130 --dbuser=nagios --dbpass=admin@123
}

define command {
    command_name    check_postgres_backends
    command_line    /usr/bin/perl /usr/local/nagios/libexec/check_postgres.pl --action=backends --dbhost=$ARG1$ --dbuser=$ARG2$ --dbpass=$ARG3$ --warning=50 --critical=100
}

define command {
    command_name    check_postgres_locks
    command_line    /usr/bin/perl /usr/local/nagios/libexec/check_postgres.pl --action=locks --dbhost=$ARG1$ --dbuser=$ARG2$ --dbpass=$ARG3$ --warning=10 --critical=20
}

define command {
    command_name    check_postgres_replication_lag
    command_line    /usr/bin/perl /usr/local/nagios/libexec/check_postgres.pl --action=replication_lag --dbhost=$ARG1$ --dbuser=$ARG2$ --dbpass=$ARG3$ --warning=5 --critical=10
}

Edit Nagios configuration to add host/service:

mkdir -p /usr/local/nagios/etc/servers/

sudo nano /usr/local/nagios/etc/servers/postgres.cfg

Add:

define host {
    use             linux-server
    host_name       server2
    alias           PostgreSQL Server
    address         192.168.136.130
}

define service {
    use                     generic-service
    host_name               server2
    service_description     PostgreSQL Connection
    check_command           check_postgres_connection
}

define service {
    use                     generic-service
    host_name               server2
    service_description     PostgreSQL Backends
    check_command           check_postgres_backends!192.168.136.130!nagios!admin@123
}

define service {
    use                     generic-service
    host_name               server2
    service_description     PostgreSQL Locks
    check_command           check_postgres_locks!192.168.136.130!nagios!admin@123
}

define service {
    use                     generic-service
    host_name               server2
    service_description     PostgreSQL Replication Lag
    check_command           check_postgres_replication_lag!192.168.136.130!nagios!admin@123
}

Include this config in nagios.cfg:

sudo nano /usr/local/nagios/etc/nagios.cfg

Add:

cfg_dir=/usr/local/nagios/etc/servers

Validate Configuration:

/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

Output:

Total Warnings: 0

Total Errors: 0

--Create directory:

---sudo mkdir -p /usr/local/nagios/etc/servers

---sudo mv /usr/local/nagios/etc/servers/postgres.cfg /usr/local/nagios/etc/servers/

Step 8: Restart Nagios

sudo systemctl restart nagios

Step 9: Access Nagios Dashboard

sudo ufw allow 80/tcp

sudo ufw reload

Visit:

🔗 http://192.168.136.129/nagios

Login:

Username: nagiosadmin

Password: admin@123

Step 10: Monitor Additional Metrics (Optional)

Go to Current Status → Services

You should now see:

PostgreSQL Connection

PostgreSQL Backends

PostgreSQL Locks

PostgreSQL Replication Lag

You can monitor more metrics like:

--action=locks

--action=bloat

--action=backends

--action=connection

(Adjust check_command accordingly)

Step 11: Test the connection in Server1

/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

If you see "Total Warnings=0, Total Errors=0", config is fine.

❌ If not, fix the path (cfg_dir) in nagios.cfg

Log file Path:

sudo tail -f /usr/local/nagios/var/nagios.log

Remove the bad one

sudo rm -f /usr/local/nagios/libexec/check_ping

Recreate the symlink properly

sudo ln -s /usr/lib/nagios/plugins/check_ping /usr/local/nagios/libexec/check_ping

Test the plugin manually

/usr/local/nagios/libexec/check_ping -H 127.0.0.1 -w 100.0,20% -c 500.0,60%

Test the plugin manually

/usr/local/nagios/libexec/check_ping -H 192.168.136.130 -w 100.0,20% -c 500.0,60%

You should get something like:

PING OK - Packet loss = 0%, RTA = 0.12 ms

For any doubts and query, please write on YouTube video 📽️ comments section.

Note : Flow the Process shown in video 📽️.

😉Please Subscribe for more videos:

https://www.youtube.com/@chiragtutorial

💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

____________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

EndFragment