Configure Grafana for PostgreSQL Monitoring

1. Purpose

This Standard Operating Procedure (SOP) explains how to configure a complete PostgreSQL monitoring stack using:

• Grafana – Visualization and dashboards
• Prometheus – Metrics collection and time-series storage
• Node Exporter – Linux/OS metrics
• Postgres Exporter – PostgreSQL database metrics
• Alertmanager – Alert routing and email notifications
• Loki – PostgreSQL log aggregation
• Promtail – PostgreSQL log collection

The procedure also configures PostgreSQL-specific alerts for:

• Database availability
• Connection utilization
• Long-running queries
• Replication lag
• CPU utilization
• Disk space
• XID wraparound
• Table bloat
• Lock waits

2. Architecture

                         ┌─────────────────────┐
                         │       Grafana       │
                         │      Port 3000      │
                         │ Dashboards + Logs   │
                         └──────────┬──────────┘
                                    │
                       ┌────────────┴────────────┐
                       │                         │
                       ▼                         ▼
              ┌────────────────┐       ┌────────────────┐
              │   Prometheus   │       │      Loki      │
              │   Port 9090    │       │   Port 3100    │
              └───────┬────────┘       └───────▲────────┘
                      │                         │
             ┌────────┴─────────┐               │
             │                  │               │
             ▼                  ▼               │
      ┌──────────────┐   ┌───────────────┐      │
      │ Node Exporter│   │Postgres Export│      │
      │   Port 9100  │   │   Port 9187   │      │
      └──────────────┘   └───────┬───────┘      │
             │                   │              │
             │                   ▼              │
             │            ┌──────────────┐      │
             │            │ PostgreSQL   │      │
             │            │    :5432     │      │
             │            └──────────────┘      │
             │                                  │
             └──────────────────────────────────┘
                         Promtail
                         Port 9080

                      Prometheus
                          │
                          ▼
                   ┌─────────────┐
                   │ Alertmanager│
                   │   :9093     │
                   └──────┬──────┘
                          │
                          ▼
                     Email Alert

3. Prerequisites

3.1 Operating System

The demonstration uses:

CentOS 9

3.2 PostgreSQL

PostgreSQL should already be installed and running.

Check PostgreSQL:

systemctl status postgresql

For PostgreSQL 18:

systemctl status postgresql-18

Verify database connectivity:

psql -U postgres -c "SELECT version();"

3.3 Root Privileges

The installation requires root privileges.

id -u

Expected output:

0

4. Monitoring Ports

The monitoring stack uses the following ports:

5. Install Node Exporter

Node Exporter collects operating-system metrics such as:

• CPU
• Memory
• Filesystem
• Disk
• Network
• Load

Download the latest Node Exporter release:

NODE_VER=$(curl -s https://api.github.com/repos/prometheus/node_exporter/releases/latest \
  | grep tag_name | cut -d '"' -f 4)

cd /tmp

wget -q \
https://github.com/prometheus/node_exporter/releases/download/${NODE_VER}/node_exporter-${NODE_VER#v}.linux-amd64.tar.gz

tar -xf node_exporter-${NODE_VER#v}.linux-amd64.tar.gz

cp node_exporter-${NODE_VER#v}.linux-amd64/node_exporter \
/usr/local/bin/

Create the systemd service:

cat >/etc/systemd/system/node_exporter.service <<EOF
[Unit]
Description=Node Exporter
After=network.target

[Service]
ExecStart=/usr/local/bin/node_exporter \
  --web.listen-address=":9100"

[Install]
WantedBy=multi-user.target
EOF

Start the service:

systemctl daemon-reload
systemctl enable --now node_exporter

Verify:

systemctl status node_exporter

Test the metrics endpoint:

curl http://localhost:9100/metrics

6. Install PostgreSQL Exporter

Postgres Exporter collects PostgreSQL metrics and exposes them to Prometheus.

Download the latest release:

PGE_URL=$(curl -s \
https://api.github.com/repos/prometheus-community/postgres_exporter/releases/latest \
| grep "browser_download_url" \
| grep linux-amd64 \
| cut -d '"' -f 4)

cd /tmp

wget -q "$PGE_URL" -O postgres_exporter.tar.gz

tar -xf postgres_exporter.tar.gz

EXPORTER_DIR=$(find . -maxdepth 1 -type d \
-name "postgres_exporter*" | head -1)

cp "$EXPORTER_DIR/postgres_exporter" /usr/local/bin/

7. Configure PostgreSQL Health Metrics

Create the configuration directory:

mkdir -p /etc/postgres_exporter

Create the custom query configuration:

vi /etc/postgres_exporter/health_queries.yml

The custom metrics should include:

XID Wraparound

Tracks transaction ID age and percentage toward wraparound.

Freeze Lag

Tracks the remaining transaction ID range before autovacuum_freeze_max_age.

Table Bloat

Identifies tables with high estimated bloat.

Lock Waits

Identifies sessions waiting for locks.

Replication Lag

Measures replication lag.

Example:

pg_health_lock_waits:
  query: |
    SELECT wait_event_type,
           wait_event,
           count(*) AS total_waiting
    FROM pg_stat_activity
    WHERE wait_event IS NOT NULL
    GROUP BY wait_event_type, wait_event;

  metrics:
    - wait_event_type:
        usage: "LABEL"

    - wait_event:
        usage: "LABEL"

    - total_waiting:
        usage: "GAUGE"

Set permissions:

chmod 644 /etc/postgres_exporter/health_queries.yml

8. Configure PostgreSQL Exporter Connection

Create the exporter systemd service:

cat >/etc/systemd/system/postgres_exporter.service <<EOF
[Unit]
Description=Postgres Exporter
After=network.target

[Service]
Environment="DATA_SOURCE_NAME=postgresql://postgres@127.0.0.1:5432/postgres?sslmode=disable"

ExecStart=/usr/local/bin/postgres_exporter \
  --web.listen-address=":9187" \
  --extend.query-path="/etc/postgres_exporter/health_queries.yml"

Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

Start the exporter:

systemctl daemon-reload
systemctl enable --now postgres_exporter

Verify:

systemctl status postgres_exporter

Test:

curl http://localhost:9187/metrics

Check custom metrics:

curl -s http://localhost:9187/metrics | grep pg_health

9. Configure PostgreSQL Authentication

The exporter must be able to connect to PostgreSQL.

Find the active pg_hba.conf:

SHOW hba_file;

Lab Environment

For a lab or teaching environment, local authentication can be simplified.

Warning: Do not blindly configure PostgreSQL authentication as trust in a production environment.

The original lab implementation changes the local IPv4 and IPv6 entries to trust to simplify exporter authentication.

Production Environment

For production, use:

• A dedicated monitoring user
• Strong authentication
• Minimum required privileges
• SSL/TLS where appropriate
• A secure password or secret-management mechanism

Example monitoring user:

CREATE USER postgres_exporter WITH PASSWORD 'REPLACE_WITH_SECURE_PASSWORD';

Grant only the privileges required by the exporter and custom monitoring queries.

10. Install Alertmanager

Alertmanager receives alerts from Prometheus and routes them to notification channels.

Download the latest release:

AM_VER=$(curl -s \
https://api.github.com/repos/prometheus/alertmanager/releases/latest \
| grep tag_name | cut -d '"' -f 4)

cd /tmp

wget -q \
https://github.com/prometheus/alertmanager/releases/download/${AM_VER}/alertmanager-${AM_VER#v}.linux-amd64.tar.gz

tar -xf alertmanager-${AM_VER#v}.linux-amd64.tar.gz

cp alertmanager-${AM_VER#v}.linux-amd64/alertmanager \
/usr/local/bin/

cp alertmanager-${AM_VER#v}.linux-amd64/amtool \
/usr/local/bin/

Create directories:

mkdir -p /etc/alertmanager /var/lib/alertmanager

11.1 Create Alertmanager systemd Service

Create the service file:

cat >/etc/systemd/system/alertmanager.service <<EOF
[Unit]
Description=Alertmanager
After=network.target

[Service]
ExecStart=/usr/local/bin/alertmanager \
--config.file=/etc/alertmanager/alertmanager.yml \
--storage.path=/var/lib/alertmanager \
--web.listen-address=":9093"

Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

Reload systemd:

systemctl daemon-reload

Enable and start Alertmanager:

systemctl enable <span class="ͼn">--now</span> alertmanager

Verify:

systemctl status alertmanager

Check the Alertmanager endpoint:

<span class="ͼl">curl</span> http://localhost:9093/-/healthy

Expected:

OK

11. Configure Email Notifications

Configure the SMTP parameters:

ALERT_EMAIL="
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
"
SMTP_HOST="smtp.gmail.com:587"
SMTP_FROM="
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
"
SMTP_USER="
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
"
SMTP_PASS="YOUR_16_CHAR_APP_PASSWORD"

Create:

vi /etc/alertmanager/alertmanager.yml

Example:

global:
  smtp_smarthost: 'smtp.gmail.com:587'
  smtp_from: '
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
'
  smtp_auth_username: '
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
'
  smtp_auth_password: 'YOUR_16_CHAR_APP_PASSWORD'
  smtp_require_tls: true

route:
  group_by: ['alertname', 'instance']
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 1h
  receiver: 'email-alert'

  routes:
    - match:
        severity: critical
      group_wait: 10s
      receiver: 'email-alert'

receivers:
  - name: 'email-alert'
    email_configs:
      - to: '
        
            yo********@gm***.com
            
                
                
                
            
            
                
                
                
            
        
'
        send_resolved: true
        headers:
          subject: '[{{ .Status | toUpper }}] {{ .GroupLabels.alertname }} - PostgreSQL Alert'

Start Alertmanager:

systemctl daemon-reload
systemctl enable --now alertmanager

Verify:

systemctl status alertmanager

12. Configure PostgreSQL Alert Rules

Create:

vi /etc/prometheus/alert_rules.yml

Edit below conent

Replace /etc/prometheus/alert_rules.yml with this structure:

groups:
  - name: postgresql_alerts
    rules:

      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: <span class="ͼk">"PostgreSQL DOWN on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"postgres_exporter cannot reach PostgreSQL. Immediate action required."</span>

      - alert: TooManyConnections
        expr: <span class="ͼk">></span>
          pg_stat_activity_count
          / pg_settings_max_connections * 100 > 80
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"Connections at {{ $value | printf \"%.0f\" }}% of max on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"Connection pool close to exhaustion. Check for connection leaks or increase max_connections."</span>

      - alert: LongRunningQuery
        expr: <span class="ͼk">></span>
          pg_stat_activity_max_tx_duration{state="active"} > 300
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"Query running > 5 minutes on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"A query has been running for over 5 minutes. May indicate a lock or missing index."</span>

      - alert: ReplicationLag
        expr: <span class="ͼk">></span>
          pg_replication_lag > 60
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: <span class="ͼk">"Replication lag {{ $value }}s on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"Standby is falling behind. Risk of data loss if primary fails."</span>

      - alert: HighCPU
        expr: <span class="ͼk">></span>
          100 - (avg by(instance)(rate(node_cpu_seconds_total{mode="idle"}[2m])) * 100) > 85
        for: 3m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"CPU at {{ $value | printf \"%.0f\" }}% on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"Sustained high CPU. May indicate heavy queries or vacuum storms."</span>

      - alert: LowDiskSpace
        expr: <span class="ͼk">></span>
          (node_filesystem_avail_bytes{mountpoint="/"}
          / node_filesystem_size_bytes{mountpoint="/"}) * 100 < 15
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: <span class="ͼk">"Disk < 15% free on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"PostgreSQL will crash when disk is full. Immediate action needed."</span>

      - alert: XIDWraparoundWarning
        expr: pg_health_wraparound_percent_pct_wraparound > 50
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"XID wraparound at {{ $value | printf \"%.1f\" }}% on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"Transaction ID approaching wraparound. Run VACUUM FREEZE."</span>

      - alert: XIDWraparoundCritical
        expr: pg_health_wraparound_percent_pct_wraparound > 80
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: <span class="ͼk">"XID wraparound CRITICAL {{ $value | printf \"%.1f\" }}% on {{ $labels.instance }}"</span>
          description: <span class="ͼk">"Immediate VACUUM FREEZE required or PostgreSQL will shut down!"</span>

      - alert: HighTableBloat
        expr: pg_health_bloat_bloat_pct > 50
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"Table {{ $labels.table }} bloat at {{ $value }}%"</span>
          description: <span class="ͼk">"High table bloat. Investigate table maintenance requirements."</span>

      - alert: HighLockWaits
        expr: sum(pg_health_lock_waits_total_waiting) > 10
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: <span class="ͼk">"{{ $value }} processes waiting on locks"</span>
          description: <span class="ͼk">"High lock contention. Check for long-running transactions."</span>

The monitoring stack contains the following alerts:

PostgreSQL Down

- alert: PostgresDown
  expr: pg_up == 0
  for: 1m

  labels:
    severity: critical

  annotations:
    summary: "PostgreSQL DOWN on {{ $labels.instance }}"
    description: "postgres_exporter cannot reach PostgreSQL. Immediate action required."

Connection Utilization

- alert: TooManyConnections
  expr: >
    pg_stat_activity_count
    / pg_settings_max_connections * 100 > 80

  for: 2m

  labels:
    severity: warning

  annotations:
    summary: "Connections at {{ $value | printf \"%.0f\" }}% of max"
    description: "Connection pool close to exhaustion."

Long-Running Query

- alert: LongRunningQuery
  expr: >
    pg_stat_activity_max_tx_duration{state="active"} > 300

  for: 0m

  labels:
    severity: warning

  annotations:
    summary: "Query running > 5 minutes"
    description: "A query has been running for over 5 minutes."

Replication Lag

- alert: ReplicationLag
  expr: >
    pg_replication_lag > 60

  for: 1m

  labels:
    severity: critical

  annotations:
    summary: "Replication lag {{ $value }}s"
    description: "Standby is falling behind."

High CPU

- alert: HighCPU
  expr: >
    100 - (avg by(instance)
    (rate(node_cpu_seconds_total{mode="idle"}[2m])) * 100) > 85

  for: 3m

  labels:
    severity: warning

Low Disk Space

- alert: LowDiskSpace
  expr: >
    (node_filesystem_avail_bytes{mountpoint="/"}
    / node_filesystem_size_bytes{mountpoint="/"}) * 100 < 15

  for: 1m

  labels:
    severity: critical

XID Wraparound Warning

- alert: XIDWraparoundWarning
  expr: pg_health_wraparound_percent_pct_wraparound > 50

  for: 5m

  labels:
    severity: warning

XID Wraparound Critical

- alert: XIDWraparoundCritical
  expr: pg_health_wraparound_percent_pct_wraparound > 80

  for: 1m

  labels:
    severity: critical

Table Bloat

- alert: HighTableBloat
  expr: pg_health_bloat_bloat_pct > 50

  for: 10m

  labels:
    severity: warning

Lock Waits

- alert: HighLockWaits
  expr: sum(pg_health_lock_waits_total_waiting) > 10

  for: 2m

  labels:
    severity: warning

13. Install Prometheus

Download the latest Prometheus release:

PROM_VER=$(curl -s \
https://api.github.com/repos/prometheus/prometheus/releases/latest \
| grep tag_name | cut -d '"' -f 4)

cd /tmp

wget -q \
https://github.com/prometheus/prometheus/releases/download/${PROM_VER}/prometheus-${PROM_VER#v}.linux-amd64.tar.gz

tar -xf prometheus-${PROM_VER#v}.linux-amd64.tar.gz

cp prometheus-${PROM_VER#v}.linux-amd64/prometheus \
/usr/local/bin/

cp prometheus-${PROM_VER#v}.linux-amd64/promtool \
/usr/local/bin/

Create directories:

mkdir -p /etc/prometheus /var/lib/prometheus

14. Configure Prometheus

Create:

vi /etc/prometheus/prometheus.yml

Configuration:

global:
  scrape_interval: 10s
  evaluation_interval: 10s

alerting:
  alertmanagers:
    - static_configs:
        - targets:
            - localhost:9093

rule_files:
  - /etc/prometheus/alert_rules.yml

scrape_configs:

  - job_name: node
    static_configs:
      - targets:
          - localhost:9100

  - job_name: postgres
    static_configs:
      - targets:
          - localhost:9187

15. Validate Prometheus Configuration

Validate the alert rules:

promtool check rules /etc/prometheus/alert_rules.yml

Validate Prometheus configuration:

promtool check config /etc/prometheus/prometheus.yml

Expected:

SUCCESS

Create the Prometheus service:

cat >/etc/systemd/system/prometheus.service <<EOF
[Unit]
Description=Prometheus
After=network.target

[Service]
ExecStart=/usr/local/bin/prometheus \
  --config.file=/etc/prometheus/prometheus.yml \
  --storage.tsdb.path=/var/lib/prometheus

Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

Start:

systemctl daemon-reload
systemctl enable --now prometheus

Verify:

systemctl status prometheus

16. Verify Prometheus Targets

Open:

http://<SERVER-IP>:9090/targets

Expected targets:

node       UP
postgres   UP

If either target is DOWN, troubleshoot the corresponding exporter before proceeding.

17. Install Grafana

Create the Grafana repository:

cat >/etc/yum.repos.d/grafana.repo <<EOF
[grafana]
name=Grafana
baseurl=https://rpm.grafana.com
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://rpm.grafana.com/gpg.key
EOF

Install Grafana:

dnf install -y grafana

Start Grafana:

systemctl enable --now grafana-server

Verify:

systemctl status grafana-server

18. Access Grafana

Open:

http://<SERVER-IP>:3000

Log in using the configured Grafana administrator credentials.

Change the default administrator password before using Grafana in a production environment.

19. Configure Prometheus Data Source

In Grafana:

Connections
    ↓
Data sources
    ↓
Add data source
    ↓
Prometheus

Set the URL:

http://localhost:9090

Click:

Save & Test

Expected:

Data source is working

20. Import PostgreSQL Dashboard

Use PostgreSQL dashboard:

Dashboard ID: 9628

In Grafana:

Dashboards
    ↓
New
    ↓
Import

Enter:

9628

Select the Prometheus data source.

Click:

Import

The dashboard should now display PostgreSQL metrics.

21. Configure Loki

Loki provides log storage for PostgreSQL and other system logs.

Download the latest Loki release:

LOKI_VER=$(curl -s \
https://api.github.com/repos/grafana/loki/releases/latest \
| grep tag_name | cut -d '"' -f 4)

cd /tmp

wget -q \
https://github.com/grafana/loki/releases/download/${LOKI_VER}/loki-linux-amd64.zip

Create directories:

mkdir -p /etc/loki /var/lib/loki

Loki is configured to listen on:

3100

Start Loki:

systemctl enable --now loki

Check status:

systemctl status loki

Verify:

curl http://localhost:3100/ready

Expected:

ready

22. Install Promtail

Promtail collects PostgreSQL log files and sends them to Loki.

Note: Promtail was removed from newer Loki releases. The supplied lab implementation pins Promtail to version 2.9.10. For a new production deployment, evaluate Grafana Alloy as the replacement.

Install:

PROMTAIL_VER="v2.9.10"

wget -q \
https://github.com/grafana/loki/releases/download/${PROMTAIL_VER}/promtail-linux-amd64.zip \
-O promtail-linux-amd64.zip

Create directories:

mkdir -p /etc/promtail /var/lib/promtail

23. Identify PostgreSQL Log Directory

Common PostgreSQL log directories include:

/var/lib/pgsql/18/data/log
/var/lib/pgsql/data/log
/var/log/postgresql

Check:

ls -ld /var/lib/pgsql/18/data/log
ls -ld /var/lib/pgsql/data/log
ls -ld /var/log/postgresql

Set the correct PostgreSQL log directory in the Promtail configuration.

24. Configure Promtail

Example:

server:
  http_listen_port: 9080
  grpc_listen_port: 0

positions:
  filename: /var/lib/promtail/positions.yaml

clients:
  - url: http://localhost:3100/loki/api/v1/push

scrape_configs:

  - job_name: postgresql

    static_configs:
      - targets:
          - localhost

        labels:
          job: postgresql
          host: localhost
          __path__: /var/lib/pgsql/18/data/log/*.log

Create the systemd service:

cat >/etc/systemd/system/promtail.service <<EOF
[Unit]
Description=Promtail Log Collector
After=network.target

[Service]
ExecStart=/usr/local/bin/promtail \
  -config.file=/etc/promtail/promtail-config.yml

Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

Start:

systemctl daemon-reload
systemctl enable --now promtail

Verify:

systemctl status promtail

25. Add Loki Data Source to Grafana

In Grafana:

Connections
    ↓
Data sources
    ↓
Add data source
    ↓
Loki

URL:

http://localhost:3100

Click:

Save & Test

Expected:

Data source is working

26. View PostgreSQL Logs in Grafana

Open:

Grafana
    ↓
Explore

Select:

Loki

Use the PostgreSQL label:

job="postgresql"

PostgreSQL log entries should now be available in Grafana.

27. Alert Testing

27.1 Test PostgreSQL Down Alert

Stop PostgreSQL:

systemctl stop postgresql-18

Wait approximately one minute.

Open:

http://<SERVER-IP>:9090/alerts

Expected:

PostgresDown

Start PostgreSQL:

systemctl start postgresql-18

Verify that the alert eventually returns to the resolved state.

28. Verify Custom PostgreSQL Metrics

Run:

curl -s http://localhost:9187/metrics | grep pg_health

You should see metrics related to:

pg_health_wraparound
pg_health_freeze
pg_health_bloat
pg_health_lock_waits
pg_health_replication_lag

29. Verify All Services

Run:

for svc in \
    node_exporter \
    postgres_exporter \
    alertmanager \
    prometheus \
    grafana-server \
    loki \
    promtail
do
    echo "Checking $svc..."
    systemctl is-active "$svc"
done

Expected:

active
active
active
active
active
active
active

If a service is not active:

systemctl status <service>

Check logs:

journalctl -u <service>

30. Troubleshooting

30.1 Grafana Not Starting

systemctl status grafana-server

Check logs:

journalctl -u grafana-server

30.2 Prometheus Not Starting

systemctl status prometheus

Check logs:

journalctl -u prometheus

Validate configuration:

promtool check config /etc/prometheus/prometheus.yml

30.3 PostgreSQL Exporter Not Working

Check service:

systemctl status postgres_exporter

Check logs:

journalctl -u postgres_exporter

Test the endpoint:

curl http://localhost:9187/metrics

30.4 PostgreSQL Metrics Missing in Grafana

Open:

http://<SERVER-IP>:9090/targets

Verify:

postgres   UP

If the target is DOWN, check:

systemctl status postgres_exporter

and:

journalctl -u postgres_exporter

30.5 Loki Not Working

Check:

systemctl status loki

Check readiness:

curl http://localhost:3100/ready

Expected:

ready

30.6 PostgreSQL Logs Missing

Check Promtail:

systemctl status promtail

Check logs:

journalctl -u promtail

Verify the PostgreSQL log directory:

ls -l /var/lib/pgsql/18/data/log/

Verify Loki:

curl http://localhost:3100/ready

Then open:

Grafana
    ↓
Explore
    ↓
Loki
    ↓
job="postgresql"

31. Final Validation Checklist

32. Access URLs

Replace <SERVER-IP> with the monitoring server IP address.

33. Production Security Considerations

Before using this architecture in production:

1. Do not use PostgreSQL trust authentication for the exporter.
2. Create a dedicated PostgreSQL monitoring user.
3. Grant only the privileges required for monitoring.
4. Use secure credentials and avoid hard-coding passwords in scripts.
5. Protect Grafana with strong administrator credentials.
6. Restrict monitoring ports using firewall/security-group rules.
7. Do not expose Prometheus, Alertmanager, Loki, or exporters directly to the public internet.
8. Use TLS where appropriate.
9. Store SMTP credentials securely.
10. Evaluate Grafana Alloy instead of Promtail for new deployments.
11. Review alert thresholds against the workload before using them operationally.
12. Test every alert in a non-production environment before enabling production notifications.

34. Summary

The completed PostgreSQL monitoring stack provides:

PostgreSQL
    │
    ├── PostgreSQL Exporter
    │        │
    │        └── Prometheus
    │               │
    │               ├── Grafana
    │               │
    │               └── Alertmanager
    │                       │
    │                       └── Email
    │
    └── PostgreSQL Logs
             │
             └── Promtail
                    │
                    └── Loki
                           │
                           └── Grafana Explore

This setup provides both metrics-based monitoring and centralized PostgreSQL log monitoring, allowing a PostgreSQL DBA to monitor database health, operating-system resources, performance indicators, replication, locks, storage, and important PostgreSQL health conditions from Grafana.

Quick Verification Commands

# PostgreSQL
psql -U postgres -c "SELECT version();"

# Node Exporter
curl -s http://localhost:9100/metrics | head

# PostgreSQL Exporter
curl -s http://localhost:9187/metrics | head

# Custom PostgreSQL metrics
curl -s http://localhost:9187/metrics | grep pg_health

# Prometheus
curl -s http://localhost:9090/-/healthy

# Alertmanager
curl -s http://localhost:9093/-/healthy

# Loki
curl -s http://localhost:3100/ready

# Grafana
curl -s http://localhost:3000/api/health

# Services
systemctl status \
node_exporter \
postgres_exporter \
alertmanager \
prometheus \
grafana-server \
loki \
promtail

DBRE Monitoring Stack

PostgreSQL • Prometheus • Grafana • Alertmanager • Loki