How to Monitor a PostgreSQL Database & Check Connection Health

How to Monitor a PostgreSQL Database

PostgreSQL is a robust relational database engine that powers many modern web applications. If PostgreSQL goes offline, runs out of memory, or exhausts its connection pool (max_connections), your entire application crashes.

Because databases sit securely behind private networks and firewalls, you cannot ping them directly from the public internet. This guide explains how to monitor PostgreSQL health securely and alert your development team before query bottlenecks trigger full outages.

Method 1: Set Up a Database Health Endpoint (Recommended)

The most secure way to monitor a private PostgreSQL instance is to expose a lightweight, secure health check endpoint inside your application API. This endpoint runs a simple database verification query and returns an HTTP status code.

Node.js (Express) Health Check Route Example

Define a health route in your application that queries the database:

const express = require('express');
const { Pool } = require('pg');
const app = express();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  connectionTimeoutMillis: 2000, // Timeout fast if DB hangs
});

app.get('/api/healthz', async (req, res) => {
  try {
    // Run a cheap select query to confirm database connection health
    await pool.query('SELECT 1');
    res.status(200).json({ status: 'OK', database: 'connected' });
  } catch (err) {
    console.error('Database health check failed:', err);
    res.status(500).json({ status: 'ERROR', message: 'Database connection failed' });
  }
});

app.listen(3000);

Configure Pingzo to Probe the Health Route

1. Log in to your Pingzo Dashboard.
2. Create a new HTTP/HTTPS Monitor pointing to your endpoint (e.g. https://your-api.com/api/healthz).
3. Set checking intervals to 1 minute and set up WhatsApp Alerts to page you instantly if the endpoint returns a 500 Internal Server Error.

Method 2: Push Monitoring (Heartbeat) for Cron SQL Jobs

If you run cron scripts, database migrations, or pg_dump backups, monitor them using Heartbeat (Push) Monitoring in Pingzo:

• Configure a Heartbeat Monitor on Pingzo.
• Add a curl ping at the end of your database backup script:

#!/bin/bash
pg_dump -U dbuser -h localhost dbname > backup.sql && \
curl -fsS https://ping.pingzoapp.com/ping/your-unique-heartbeat-id

If the database is locked or down, the backup command fails, the curl callback is skipped, and Pingzo alerts you.

Key PostgreSQL Metrics to Monitor

Expose these internal metrics to your logging dashboards:

• Active Connection Count: Track connection volume to prevent reaching max_connections.
• Transaction Lock Waits: Alert if query transaction locks lock table updates for more than a few seconds.
• Replication Lag: If running replica databases, monitor replicator byte delay metrics to ensure read nodes remain synced.