Skip to main content
View rawEdit

Cloud installation guide

Configure PostgresAI monitoring for managed PostgreSQL services.

Overview​

Managed PostgreSQL services (RDS, CloudSQL, Supabase) require specific configuration due to:

  • Limited superuser access
  • Pre-configured extensions
  • Network restrictions
  • Different permission models

Amazon RDS for PostgreSQL​

Prerequisites​

  • RDS PostgreSQL 14+
  • Parameter group with pg_stat_statements enabled
  • Security group allowing monitoring access
  • Node.js 18+ (or Bun 1.0+) on the host running the postgresai CLI — older versions fail fast

Step 1: Enable pg_stat_statements​

Create or modify a parameter group:

shared_preload_libraries = pg_stat_statements
pg_stat_statements.track = top
pg_stat_statements.max = 5000

Apply to your RDS instance and reboot if required.

Step 2: Create monitoring user​

Connect as the master user:

-- Create monitoring user
create user postgres_ai_mon with password '<STRONG_RANDOM_PASSWORD>';

-- Grant required permissions (the product requires the built-in pg_monitor role,
-- not pg_read_all_stats)
grant pg_monitor to postgres_ai_mon;

-- Enable extension (if not already)
create extension if not exists pg_stat_statements;

-- For each database to monitor, connect and grant connect (pg_monitor already
-- covers reading statistics — monitoring needs metadata only, NOT table data,
-- so do NOT `grant select on all tables`).
\c your_database
grant connect on database your_database to postgres_ai_mon;
Use prepare-db for the exact grants

Instead of granting by hand, run npx [email protected] prepare-db --print-sql (or run prepare-db against the master/admin user) to apply the exact, minimal read-only grants the product uses. See Permissions.

Step 3: Configure security group​

Allow inbound traffic from your monitoring stack:

TypeProtocolPortSource
PostgreSQLTCP5432Monitoring VPC/IP

Step 4: Start monitoring​

npx [email protected] mon local-install \
  --db-url "postgresql://postgres_ai_mon:[email protected]:5432/your_db"
Labeling clusters and nodes

The 0.15 local-install command does not accept --cluster-name/--node-name. To tag metrics by cluster or node, set custom_tags.cluster and custom_tags.node_name in instances.yml (see Docker Compose → Adding multiple databases).

RDS-specific considerations​

Enhanced Monitoring: RDS Enhanced Monitoring provides OS-level metrics. PostgresAI focuses on PostgreSQL-internal metrics, so both complement each other.

Performance Insights: If using RDS Performance Insights, PostgresAI provides similar wait event analysis with more customization options.

Multi-AZ: Monitor the primary endpoint. For read replicas, add separate monitoring targets.

Google Cloud SQL​

Prerequisites​

  • Cloud SQL PostgreSQL 14+
  • Private IP or authorized network
  • cloudsql.enable_pg_stat_statements flag enabled

Step 1: Enable extensions​

In Cloud Console or via gcloud:

gcloud sql instances patch INSTANCE_NAME \
  --database-flags=cloudsql.enable_pg_stat_statements=on

This requires instance restart.

Step 2: Create monitoring user​

Using Cloud SQL admin user:

-- Create monitoring user
create user postgres_ai_mon with password '<STRONG_RANDOM_PASSWORD>';

-- Grant permissions (the product requires the built-in pg_monitor role,
-- not pg_read_all_stats)
grant pg_monitor to postgres_ai_mon;

-- On each database to monitor (pg_monitor already covers reading statistics —
-- monitoring needs metadata only, NOT table data, so do NOT `grant select on
-- all tables`):
grant connect on database your_database to postgres_ai_mon;

Step 3: Configure network access​

Option A: Private IP (Recommended)

Enable Private IP on your Cloud SQL instance and connect from your monitoring VPC.

Option B: Authorized Networks

Add your monitoring stack's IP to authorized networks:

  • Cloud Console — SQL — Instance — Connections — Authorized networks

Step 4: Connection string​

For private IP:

npx [email protected] mon local-install \
  --db-url "postgresql://postgres_ai_mon:[email protected]:5432/your_db"

For Cloud SQL Auth Proxy:

# Start proxy
cloud_sql_proxy -instances=PROJECT:REGION:INSTANCE=tcp:5432

# Connect
npx [email protected] mon local-install \
  --db-url "postgresql://postgres_ai_mon:password@localhost:5432/your_db"
Labeling clusters and nodes

The 0.15 local-install command does not accept --cluster-name/--node-name. To tag metrics by cluster or node, set custom_tags.cluster and custom_tags.node_name in instances.yml (see Docker Compose → Adding multiple databases).

Cloud SQL-specific considerations​

Insights: Cloud SQL Query Insights provides similar functionality. PostgresAI offers more detailed dashboards and historical analysis.

Read Replicas: Each replica needs separate monitoring configuration with distinct node_name.

Supabase​

Prerequisites​

  • Supabase project with PostgreSQL
  • Direct database connection enabled
  • Database password from Supabase dashboard

Step 1: Get connection details​

In Supabase Dashboard:

  1. Go to Settings → Database
  2. Find Connection string under "Direct connection"
  3. Note the host, port, and password

Step 2: Enable pg_stat_statements​

Supabase has pg_stat_statements enabled by default. Verify:

select * from pg_extension where extname = 'pg_stat_statements';

Step 3: Create monitoring user​

Connect to your Supabase database and run:

-- Create monitoring user
create user postgres_ai_mon with password '<STRONG_RANDOM_PASSWORD>';

-- Grant permissions (the product requires the built-in pg_monitor role,
-- not pg_read_all_stats)
grant pg_monitor to postgres_ai_mon;

-- pg_monitor already covers reading statistics — monitoring needs metadata
-- only, NOT table data, so do NOT `grant select on all tables`:
grant connect on database postgres to postgres_ai_mon;

Step 4: Start monitoring​

npx [email protected] mon local-install \
  --db-url "postgresql://postgres_ai_mon:[email protected]:5432/postgres?sslmode=require"
Labeling clusters and nodes

The 0.15 local-install command does not accept --cluster-name/--node-name. To tag metrics by cluster or node, set custom_tags.cluster and custom_tags.node_name in instances.yml (see Docker Compose → Adding multiple databases).

Connection pooling

Use the "Direct connection" string, not the pooled connection (port 6543). Monitoring requires direct PostgreSQL protocol access.

Common cloud considerations​

SSL/TLS connections​

Most cloud providers require SSL:

# Require SSL
--db-url "postgresql://...?sslmode=require"

# Verify certificate (recommended for production)
--db-url "postgresql://...?sslmode=verify-full&sslrootcert=/path/to/ca.crt"

Permission limitations​

Cloud databases don't allow true superuser access. The monitoring user can access:

  • ✅ pg_stat_statements
  • ✅ pg_stat_* views
  • ✅ System catalogs (pg_class, pg_index, etc.)
  • ❌ File system functions
  • ❌ Some pg_stat_kcache features

Connection limits​

Cloud instances have connection limits. Ensure monitoring doesn't exhaust connections:

  • pgwatch uses ~2-5 connections per target
  • Consider connection pooling if near limits

Network latency​

For optimal metric collection:

  • Deploy monitoring in same region as database
  • Use private networking when available
  • Account for ~1-2 second scrape intervals

Troubleshooting​

"permission denied for function"​

Grant the required role (the product requires pg_monitor, not pg_read_all_stats):

grant pg_monitor to postgres_ai_mon;

"pg_stat_statements must be loaded"​

Ensure extension is in preload libraries and instance was restarted.

Connection timeout​

Check:

  1. Security group / firewall rules
  2. VPC peering / private link configuration
  3. Correct endpoint (primary vs replica)

SSL errors​

Verify SSL mode and certificates:

psql "postgresql://...?sslmode=verify-full" -c "SELECT 1"

Next steps​