> ## Documentation Index
> Fetch the complete documentation index at: https://opensre.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Connect PostgreSQL so OpenSRE can diagnose database issues during investigations

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

## 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

```bash theme={null}
opensre integrations setup
```

Select **PostgreSQL** when prompted and provide your host, database, and credentials.

### Option 2: Environment variables

Add to your `.env`:

```bash theme={null}
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`   | SSL mode: `prefer`, `require`, or `disable` |

### Option 3: Persistent store

Integrations are automatically persisted to `~/.opensre/integrations.json`:

```json theme={null}
{
  "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"
      }
    }
  ]
}
```

## Creating a read-only user

```sql theme={null}
-- Create the user
CREATE USER opensre_readonly WITH PASSWORD 'secure-password';

-- Grant access to system views
GRANT pg_monitor TO opensre_readonly;

-- Grant access to the target database
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;
```

<Info>
  `pg_monitor` (available in PostgreSQL 10+) grants read access to all monitoring views including `pg_stat_activity`, `pg_stat_replication`, and `pg_stat_statements` without superuser privileges.
</Info>

## Enabling slow query tracking

Slow query analysis requires the `pg_stat_statements` extension. Add to `postgresql.conf`:

```ini theme={null}
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
```

Then restart PostgreSQL and run:

```sql theme={null}
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

## Investigation tools

When OpenSRE investigates a PostgreSQL-related alert, five diagnostic tools are available:

### Server status

Retrieves version, uptime, connection counts (total, active, idle, max), transaction commit/rollback rates, and buffer cache hit ratio per database. Useful for spotting connection saturation or cache efficiency drops.

### Current queries

Lists active queries running longer than a configurable threshold (default 1 s), excluding the monitoring connection itself. Includes PID, user, client address, duration, wait event, and a truncated query string.

### Replication status

Uses `pg_is_in_recovery()` to reliably detect replica vs primary. On a primary, reports WAL position and per-replica lag (write, flush, replay). Returns a note if the server is a replica or if no replicas are connected.

### Slow queries

Reads `pg_stat_statements` to surface queries with the highest mean execution time. Results include call count, total/mean/min/max execution time (in ms with sub-millisecond precision), rows returned, and buffer cache hit percentage.

<Info>
  Slow query data requires the `pg_stat_statements` extension to be installed and loaded via `shared_preload_libraries`. OpenSRE returns an informative message if the extension is not available.
</Info>

### Table statistics

Reads `pg_stat_user_tables` and `pg_class` for a given schema (default `public`). Returns insert/update/delete/live/dead tuple counts, sequential vs index scan ratios, last vacuum/analyze timestamps, and table sizes in bytes and MB.

## Verify

```bash theme={null}
opensre integrations verify postgresql
```

Expected output:

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

## Troubleshooting

| Symptom                                     | Fix                                                                                                                                      |
| ------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| **Connection refused**                      | Verify host, port, and firewall rules. Check `listen_addresses` in `postgresql.conf` and `pg_hba.conf` for the connecting host.          |
| **Authentication failed**                   | Confirm username and password. Check `pg_hba.conf` for the correct auth method (`md5`, `scram-sha-256`).                                 |
| **SSL error**                               | Set `POSTGRESQL_SSL_MODE=disable` to test without SSL, or `require` to enforce it.                                                       |
| **Permission denied on pg\_stat\_activity** | Grant `pg_monitor` role to the OpenSRE user.                                                                                             |
| **pg\_stat\_statements not found**          | Add `pg_stat_statements` to `shared_preload_libraries`, restart PostgreSQL, then run `CREATE EXTENSION pg_stat_statements;`.             |
| **Replication shows replica, not primary**  | Expected — the tool correctly identifies the server as a replica and returns a note. Connect to the primary for replication lag details. |

## Security best practices

* Use a **dedicated read-only user** with `pg_monitor` — avoid superuser credentials for monitoring.
* Enable **SSL** (`POSTGRESQL_SSL_MODE=require`) in production environments.
* Use `scram-sha-256` authentication in `pg_hba.conf` rather than `md5`.
* Store credentials in `.env`, never in source code.
* Rotate credentials periodically.