PostgreSQL

Connect PostgreSQL to the Pagerly SRE Agent to check active queries, locks, and replication lag.

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:

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

1. Ensure the database is accessible from the internet (or via SSH tunnel)

2. 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

We add the jdbc:postgresql:// prefix automatically. Just enter host:port/dbname.

Use the pg_monitor role — it grants read-only access to monitoring views (pg_stat_activity, pg_stat_replication, etc.) without allowing data modification.

Context Prompt Tips

"Main database is api_production. Tables in public schema. Long-running queries usually involve the orders table."

Docs

• PostgreSQL Monitoring Statistics