> For the complete documentation index, see [llms.txt](https://docs.pagerly.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pagerly.io/sre-ai-agent/data-sources/postgresql.md).

# PostgreSQL

## What the Agent Can Do

* Show active queries — find long-running queries, blocked sessions, idle-in-transaction
* Show lock contention — which queries are blocking others
* Show replication status — lag, state, sync status for each replica
* Show table statistics — dead tuples (needs VACUUM), seq scans, table sizes

## Setup Instructions

1. Create a **read-only PostgreSQL user** with monitoring permissions:

```sql
CREATE USER pagerly WITH PASSWORD 'your-password';
GRANT pg_monitor TO pagerly;
```

2. Ensure the database is accessible from the internet (or via SSH tunnel)
3. Enter the connection details in the Pagerly dashboard

## Required Credentials

| Field                 | Description                               | Example                        |
| --------------------- | ----------------------------------------- | ------------------------------ |
| **Connection String** | host:port/database                        | `your-db-host.com:5432/dbname` |
| **Username**          | PostgreSQL username with pg\_monitor role | `pagerly`                      |
| **Password**          | PostgreSQL password                       | `your-password`                |

{% hint style="info" %}
**We add the `jdbc:postgresql://` prefix automatically.** Just enter `host:port/dbname`.
{% endhint %}

{% hint style="warning" %}
**Use the `pg_monitor` role** — it grants read-only access to monitoring views (`pg_stat_activity`, `pg_stat_replication`, etc.) without allowing data modification.
{% endhint %}

## Context Prompt Tips

> *"Main database is api\_production. Tables in public schema. Long-running queries usually involve the orders table."*

## Docs

* [PostgreSQL Monitoring Statistics](https://www.postgresql.org/docs/current/monitoring-stats.html)