PostgreSQL

Connect PostgreSQL so OpenSRE can diagnose database issues during investigations

Overview

OpenSRE uses PostgreSQL diagnostics to investigate database-related alerts — checking server health, surfacing slow queries, monitoring replication status, and analyzing table statistics. All queries are read-only SELECTs.

Prerequisites

• PostgreSQL 10+ (12+ recommended for full pg_stat_statements support)
• Network access from the OpenSRE environment to your PostgreSQL instance
• A read-only user with access to system views

Setup

Option 1: Interactive CLI

opensre integrations setup postgresql

Provide your host, database, and credentials when prompted.

Option 2: Environment variables

POSTGRESQL_HOST=your-postgresql-host
POSTGRESQL_PORT=5432
POSTGRESQL_DATABASE=your-database
POSTGRESQL_USERNAME=opensre_readonly
POSTGRESQL_PASSWORD=your-password
POSTGRESQL_SSL_MODE=prefer   # prefer, require, or disable

Variable	Default	Description
POSTGRESQL_HOST	—	Required. PostgreSQL hostname or IP
POSTGRESQL_PORT	5432	PostgreSQL port
POSTGRESQL_DATABASE	—	Required. Target database
POSTGRESQL_USERNAME	postgres	Username
POSTGRESQL_PASSWORD	(empty)	Password
POSTGRESQL_SSL_MODE	prefer	libpq SSL mode: prefer, require, or disable

Option 3: Persistent store

{
  "version": 1,
  "integrations": [
    {
      "id": "postgresql-prod",
      "service": "postgresql",
      "status": "active",
      "credentials": {
        "host": "prod-primary.postgres.example.com",
        "port": 5432,
        "database": "application_db",
        "username": "opensre_readonly",
        "password": "your-password",
        "ssl_mode": "prefer"
      }
    }
  ]
}

Credentials

Creating a read-only user

CREATE USER opensre_readonly WITH PASSWORD 'secure-password';
GRANT pg_monitor TO opensre_readonly;
GRANT CONNECT ON DATABASE application_db TO opensre_readonly;
\c application_db
GRANT USAGE ON SCHEMA public TO opensre_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO opensre_readonly;

pg_monitor (PostgreSQL 10+) grants read access to monitoring views including pg_stat_activity, pg_stat_replication, and pg_stat_statements without superuser privileges.

Enabling slow query tracking

Add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart PostgreSQL, then:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Quick local test with Docker

docker run -d --name postgres-dev -p 127.0.0.1:5433:5432 \
  -e POSTGRES_PASSWORD=verifypass \
  -e POSTGRES_DB=app_db \
  postgres:16 -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all

i=0
until docker exec postgres-dev pg_isready -U postgres > /dev/null 2>&1; do
  i=$((i + 1))
  [ "$i" -ge 30 ] && { echo "ERROR: PostgreSQL never became ready" >&2; exit 1; }
  sleep 2
done

docker exec postgres-dev psql -U postgres -d app_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
docker exec postgres-dev psql -U postgres -d app_db -c "
CREATE TABLE orders (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 42.50), (2, 17.00);
"
docker exec postgres-dev psql -U postgres -d app_db -c "SELECT pg_sleep(2);" > /dev/null

export POSTGRESQL_HOST=127.0.0.1
export POSTGRESQL_PORT=5433
export POSTGRESQL_DATABASE=app_db
export POSTGRESQL_USERNAME=postgres
export POSTGRESQL_PASSWORD=verifypass
export POSTGRESQL_SSL_MODE=disable

Verify:

opensre integrations verify postgresql

SERVICE    │ SOURCE    │ STATUS   │ DETAIL
postgresql │ local env │ ✓ passed │ Connected to PostgreSQL 16.15
           │           │          │ target database: app_db.

Use a temporary integration-store path so saved integrations cannot override the demo environment variables:

export OPENSRE_DEMO_STORE_DIR="$(mktemp -d /tmp/opensre-postgresql-demo.XXXXXX)"
export OPENSRE_INTEGRATIONS_STORE_PATH="$OPENSRE_DEMO_STORE_DIR/integrations.json"

The file does not need to exist. OpenSRE treats the missing temporary store as empty and resolves PostgreSQL from the exported variables above.

Trigger a real investigation against the slow query — this is the actual supported entrypoint, not an internal import:

opensre investigate --input-json '{
  "alert_name": "PostgreSQLSlowQuery",
  "severity": "warning",
  "commonAnnotations": {
    "summary": "app_db PostgreSQL instance has a slow query"
  }
}'

Real output from a run against this exact local instance (edited for length):

Loading integrations: postgresql
↳ PostgreSQL · get postgresql slow queries
↳ PostgreSQL · get postgresql server status
↳ PostgreSQL · get postgresql current queries
↳ PostgreSQL · get postgresql lock status
↳ PostgreSQL · get postgresql table stats
↳ PostgreSQL · get postgresql replication status

Triage complete: Single flagged slow query is SELECT pg_sleep($1)
averaging 2016 ms -- an intentional sleep call; all other PostgreSQL
health indicators are normal (8/100 connections, 97.97% cache hit,
0 locks/waits, 0 long-running queries, primary with no replicas).

Hypothesis confirmed: pg_sleep() is intentional latency, not a
performance problem -- no other slow queries, no locks, no long
transactions, no bloat, healthy cache/connection counts.

All 6 registered tools were exercised in this single turn.

Teardown:

docker rm -f postgres-dev
rm -rf "$OPENSRE_DEMO_STORE_DIR"
unset OPENSRE_DEMO_STORE_DIR OPENSRE_INTEGRATIONS_STORE_PATH
unset POSTGRESQL_HOST POSTGRESQL_PORT POSTGRESQL_DATABASE
unset POSTGRESQL_USERNAME POSTGRESQL_PASSWORD POSTGRESQL_SSL_MODE

Investigation tools

Tool	What it does
get_postgresql_server_status	Version, uptime, connection counts, commit/rollback rates, buffer cache hit ratio
get_postgresql_current_queries	Active queries longer than a threshold (default 1s), with PID, user, wait event, truncated SQL
get_postgresql_slow_queries	Highest mean execution time from pg_stat_statements (needs the extension)
get_postgresql_table_stats	Per-table insert/update/delete/live/dead tuples, scan ratios, vacuum/analyze times, sizes
get_postgresql_lock_status	Lock / blocking information
get_postgresql_replication_status	Primary vs replica (pg_is_in_recovery()), WAL position, per-replica lag

Verify

opensre integrations verify postgresql

Alias: postgres. Expected output:

Service: postgresql
Status: passed
Detail: Connected to PostgreSQL 16.1; target database: application_db

Troubleshooting

Symptom	Fix
Connection refused	Check host, port, firewall, listen_addresses, and pg_hba.conf
Authentication failed	Confirm username/password and pg_hba.conf auth method (md5, scram-sha-256)
SSL error	Try POSTGRESQL_SSL_MODE=disable or require
Permission denied on pg_stat_activity	Grant pg_monitor to the OpenSRE user
pg_stat_statements not found	Add to shared_preload_libraries, restart, then CREATE EXTENSION
Replication shows replica, not primary	Expected — connect to the primary for lag details

Security

• Use a dedicated read-only user with pg_monitor — avoid superuser credentials.
• Enable SSL (POSTGRESQL_SSL_MODE=require) in production.
• Prefer scram-sha-256 in pg_hba.conf.
• Store credentials in .env or your secret manager — not in source control.
• Rotate credentials periodically.