Skip to content

providers.md

Latest commit

 

History

History
104 lines (82 loc) · 9.08 KB

providers.md

File metadata and controls

104 lines (82 loc) · 9.08 KB
Raw

Provider compatibility matrix

How pgbot behaves on each managed-Postgres platform: what it can see, what it can't, and the exact steps to unblock the degraded paths.

Status. The capability details below are documented (📄) — from pgbot's provider detection/remediation logic and each provider's published behaviour — and the connection guidance is in sync with the README. They are not the same as live-verified (✅): the ⏳ cells still need a real connection to confirm. pgbot itself is a client, and the "local/remote client → managed Postgres over the wire" model is verified live (against a pgrun-hosted PG 18.4 prod DB), which is the same shape as every row here. An honest matrix that marks documented-vs-verified beats six unearned green checks.

Summary

Provider pg_monitor grantable pg_stat_statements Default DSN pooled Stats survive idle Status
Amazon RDS 📄 yes, via rds_superuser 📄 preload + reboot 📄 no (direct) 📄 yes 📄 documented
Amazon Aurora 📄 yes, via rds_superuser 📄 preload + reboot 📄 no (direct) 📄 yes ⏳ WAL/IO differ
Google Cloud SQL 📄 yes, via cloudsqlsuperuser 📄 instance flag 📄 no (direct) 📄 yes ⏳ unverified
Azure Flexible Server 📄 yes, via azure_pg_admin 📄 two server params + restart 📄 no (direct) 📄 yes ⏳ unverified
Supabase ⏳ limited (no superuser) 📄 preloaded 📄 yes (:6543) 📄 yes 📄 documented
Neon ⏳ limited (no superuser) 📄 preloaded 📄 yes (-pooler host) ❌ no (scale-to-zero) 📄 documented

Legend: ✅ confirmed live · 📄 documented, not yet live-tested · ⏳ unknown, needs a live check · ❌ not supported / notable limitation.

pgbot detects the provider from the host, version() text, and provider-specific pg_settings markers (rds.*, cloudsql.*, azure.*, aurora_version()), so detection works even when the host is a bare IP or sits behind a proxy.

Amazon RDS / Aurora

  • Connecting: you can't install on the RDS/Aurora instance (managed, no OS access) — run pgbot from a client that can reach it. For a private instance (typical prod) there are two ways in: run pgbot from a small EC2 in the same VPC — it reaches the private endpoint over AWS's internal network, so the DB never needs public access, no SSH tunnel, no IP allow-listing, and the only rule is the RDS security group allowing 5432 from the EC2's security group — or keep pgbot on your laptop and reach the endpoint through a bastion with --ssh-tunnel (see Reaching a private database), which still validates sslmode=verify-full against the real endpoint name. For a publicly accessible instance, allow your IP in the security group and connect from your laptop. 
    pgbot inspect "postgres://pgbot_ro@mydb.abc123.us-east-1.rds.amazonaws.com:5432/appdb?sslmode=require"
  • Host metrics: CPU / memory / disk IOPS live in CloudWatch, not Postgres — out of reach over a connection string. Everything pgbot computes over SQL works.
  • pg_monitor: grant as the master user (rds_superuser): GRANT pg_monitor TO <role>;. RDS does not expose OS-superuser, but pg_monitor is fully grantable, which is all pgbot needs.
  • pg_stat_statements: add pg_stat_statements to shared_preload_libraries in the DB parameter group, reboot the instance, then CREATE EXTENSION pg_stat_statements;. This is exactly the string pgbot prints on the degraded path.
  • Aurora clusters (experimental): an Aurora endpoint stands for several instances, and pg_stat_* on one says nothing about the others. pgbot inspect "$CLUSTER_URL" --all-instances asks aurora_replica_status() for the members, derives each instance endpoint from the cluster endpoint's DNS name (<instance>.<cluster-id>.<region>.rds.amazonaws.com; a custom domain is followed through its CNAME), verifies with aurora_db_instance_identifier() that each derived endpoint reached the member it names, and inspects every one — writer first, then readers — using the same credentials and TLS settings. It needs only SQL and DNS: no AWS credentials, CLI, SDK, or API. It refuses an RDS Proxy endpoint (the members are hidden behind it) and any endpoint it cannot derive from, and any member it cannot reach makes the run report partial coverage and exit 3. Add --all-databases to cross members with databases. Each instance is its own server for cluster-wide findings, since parameter groups differ per instance.
  • Pooler: the default endpoint is a direct connection. RDS Proxy is opt-in; when used it is a transaction pooler and pgbot will note it (rates stay correct).
  • Idle/stats: always-on instance; cumulative stats persist normally.
  • Capability-gated: pg_stat_io requires PG 16+; pg_stat_wal requires PG 14+. Aurora reports storage differently from community Postgres — WAL/IO sections may read differently and need live confirmation.

Google Cloud SQL

  • pg_monitor: grant via the cloudsqlsuperuser role: GRANT pg_monitor TO <role>;.
  • pg_stat_statements: set the cloudsql.enable_pg_stat_statements instance flag, then CREATE EXTENSION pg_stat_statements;.
  • Pooler: default endpoint is direct; the Cloud SQL Auth Proxy is a TCP proxy, not a statement pooler.
  • Idle/stats: always-on; stats persist.

Azure Database for PostgreSQL — Flexible Server

  • pg_monitor: grant as a member of azure_pg_admin.
  • pg_stat_statements: add pg_stat_statements to both the azure.extensions and shared_preload_libraries server parameters (a restart applies the latter), then CREATE EXTENSION pg_stat_statements;.
  • Pooler: built-in PgBouncer is opt-in on a separate port; the default endpoint is direct.
  • Idle/stats: always-on; stats persist.

Supabase

  • Connecting: prefer the direct endpoint (session-scoped) for pgbot; the default pooled endpoint also works (pgbot notes it). 
    # direct (session):
pgbot inspect "postgres://postgres:pass@db.<ref>.supabase.co:5432/postgres?sslmode=require"
# pooled (Supavisor, transaction mode, :6543):
pgbot inspect "postgres://postgres.<ref>:pass@aws-0-<region>.pooler.supabase.com:6543/postgres?sslmode=require"
  • pg_monitor: Supabase does not grant superuser; pg_monitor availability to a custom role needs live confirmation. The default postgres role has broad but not unlimited access.
  • pg_stat_statements: preloaded — just CREATE EXTENSION pg_stat_statements;.
  • Pooler: the default pooled connection string uses port :6543 (Supavisor / PgBouncer transaction mode). pgbot detects this endpoint and proceeds with a note; rates stay correct because each counter is sampled in its own transaction against cluster-wide shared memory. Use the direct :5432 endpoint (--strict-pooler will insist on it) if you want session-scoped certainty.
  • Idle/stats: paid tiers are always-on; free tier pauses after inactivity — treat the first run after a pause like a cold window (pgbot's T2 handling applies).

Neon

  • Connecting: the pooled endpoint has a -pooler host suffix; use the direct host for session-scoped certainty. Neon's default string ships channel_binding=require — pgbot ignores it automatically (the driver can't do SCRAM channel binding; TLS from sslmode still applies) instead of erroring. 
    pgbot inspect "postgres://user:pass@ep-xxx.region.aws.neon.tech/dbname?sslmode=require"
  • pg_monitor: no superuser; grantability to a custom role needs live confirmation.
  • pg_stat_statements: preloaded — just CREATE EXTENSION pg_stat_statements;.
  • Pooler: the pooled endpoint uses a -pooler host suffix (PgBouncer transaction mode). pgbot detects it and proceeds with a note.
  • Idle/stats: scale-to-zero. After the compute suspends, cumulative statistics are discarded; the first run after a wake sees a near-zero stats window. This is the canonical case pgbot's cold-window detection (T2) exists for — counter-based findings are suppressed until the window exceeds 15 minutes, and the wait-event profile (ASH) may be the only usable signal.

Verification checklist

For each provider, connect a real instance and confirm — promoting the 📄 (documented) and ⏳ (unknown) marks above to ✅ or ❌, and recording the exact commands that worked:

  • pg_monitor can be granted to a non-admin role, and by which admin role
  • pg_stat_statements enable steps above are exact and sufficient
  • whether the default connection string routes through a pooler (and which port/host)
  • whether pgbot's pooler detection fires on that default endpoint
  • stats retention across an idle/suspend cycle (scale-to-zero behaviour)
  • which capability-gated sections (pg_stat_io PG16+, pg_stat_wal PG14+, replication) are unavailable and why
  • paste the working, copy-pasteable setup commands

Run pgbot inspect "<dsn>" --json | jq .server to capture the detected provider, version, and capabilities for the record.