# Rds Postgresql Diagnostics > Use this skill to investigate and troubleshoot Amazon RDS for PostgreSQL problems by analyzing instance configurations, performance metrics, connectivity, parameter groups, and following structured runbooks. Activate when: launch failures, vacuum/bloat issues, connection pooling, pg_stat analysis, connectivity failures, parameter group tuning, replication lag, logical replication, backup/PITR, extensions, RDS Proxy, encryption, upgrades, or the user says something is wrong with RDS PostgreSQL. - Skill: `aws-samples/rds-postgresql-diagnostics` (Agent Skill, multi-file: 18 files) - Install (CLI): `npx skillmds@latest add aws-samples/rds-postgresql-diagnostics` - Raw SKILL.md: https://api.skillmd.com/api/skills/aws-samples/rds-postgresql-diagnostics/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: aws-samples (https://skillmd.com/u/aws-samples) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/aws-samples/rds-postgresql-diagnostics --- # RDS PostgreSQL Diagnostics ## When to use Any RDS PostgreSQL investigation where the console alone is insufficient — instance launch failures, vacuum/autovacuum issues, table bloat, connection pooling, pg_stat analysis, connectivity, parameter tuning, physical and logical replication, backup/recovery, extensions, RDS Proxy, encryption, or upgrade troubleshooting. ## Investigation workflow ### Step 1 — Collect and triage ``` aws rds describe-db-instances --db-instance-identifier aws rds describe-events --source-identifier --source-type db-instance --duration 1440 aws rds describe-db-log-files --db-instance-identifier aws rds download-db-log-file-portion --db-instance-identifier --log-file-name error/postgresql.log aws rds describe-db-parameters --db-parameter-group-name ``` ### Step 2 — Performance deep dive ``` aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name CPUUtilization \ --dimensions Name=DBInstanceIdentifier,Value= \ --start-time --end-time --period 300 --statistics Average aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name FreeableMemory ... aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReadIOPS ... aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name WriteIOPS ... aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name DatabaseConnections ... aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name TransactionLogsDiskUsage ... aws pi get-resource-metrics --service-type RDS \ --identifier db- \ --metric-queries '[{"Metric":"db.load.avg"}]' \ --start-time --end-time --period-in-seconds 300 ``` ### Step 3 — PostgreSQL-specific diagnostics (via psql) ```sql -- Active queries and wait events SELECT pid, usename, state, wait_event_type, wait_event, query_start, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start; -- Table bloat and dead tuples SELECT schemaname, relname, n_live_tup, n_dead_tup, ROUND(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 2) AS dead_pct, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20; -- Connection usage SELECT count(*) AS total, state FROM pg_stat_activity GROUP BY state; SHOW max_connections; -- Replication status (on primary) SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes FROM pg_stat_replication; ``` Read `references/guardrails.md` before concluding on any RDS PostgreSQL issue. ## Tool quick reference | Tool / API | When to use | |------------|-------------| | `describe-db-instances` | Instance config, engine version, storage | | `describe-events` | Recent RDS events | | `describe-db-parameters` | Parameter group settings | | `download-db-log-file-portion` | PostgreSQL error log | | `CloudWatch CPUUtilization` | CPU usage | | `CloudWatch FreeableMemory` | Available memory (shared_buffers pressure) | | `CloudWatch TransactionLogsDiskUsage` | WAL disk usage | | `Performance Insights` | DB load, wait events, top SQL | | `pg_stat_activity` | Active queries and connections | | `pg_stat_user_tables` | Table stats, vacuum status, dead tuples | | `pg_stat_replication` | Replication lag | | `pg_stat_bgwriter` | Checkpoint and bgwriter stats | ## Gotchas: RDS PostgreSQL - `shared_buffers` defaults to `{DBInstanceClassMemory/32768}` (≈25% of memory). PostgreSQL relies on OS cache for the rest. Do not set shared_buffers > 40% of instance memory. - No `rds_superuser` is not the same as PostgreSQL `superuser`. Some operations (e.g., `CREATE EXTENSION`, `pg_terminate_backend`) work, but OS-level access and some superuser-only functions are restricted. - Autovacuum is critical. Disabling or under-tuning autovacuum leads to table bloat, transaction ID wraparound, and performance degradation. Monitor `n_dead_tup` and `last_autovacuum`. - Transaction ID wraparound: PostgreSQL uses 32-bit transaction IDs. If autovacuum cannot keep up, the database enters read-only mode at 2 billion transactions. Monitor `age(datfrozenxid)`. - `max_connections` default is `LEAST({DBInstanceClassMemory/9531392}, 5000)`. Each connection uses ~10MB. Use connection pooling (RDS Proxy or PgBouncer) for high-connection workloads. - Logical replication requires `rds.logical_replication=1` and `wal_level=logical`. This increases WAL generation. Not all data types and DDL are replicated. - Extensions must be allowlisted by RDS. Use `SHOW rds.allowed_extensions` or check `aws rds describe-db-engine-versions --engine postgres --engine-version --query 'DBEngineVersions[0].SupportedFeatureNames'`. - PITR restores to a new instance. WAL-based, so restore time depends on WAL volume since last snapshot. - Major version upgrades (e.g., 14→16) use pg_upgrade internally. Extensions must be compatible with the target version. ## Anti-hallucination rules 1. Always cite specific AWS CLI output, CloudWatch metrics, Performance Insights data, or PostgreSQL query results as evidence. 2. No OS-level access. Never suggest editing `postgresql.conf` or `pg_hba.conf` directly. 3. `rds_superuser` is not PostgreSQL `superuser`. Some superuser-only functions are restricted. 4. PITR creates a new instance. Never suggest in-place restore. 5. Autovacuum should never be disabled. Never suggest turning off autovacuum globally. 6. Spend no more than 2 minutes on any single hypothesis. Pivot if inconclusive. ## Runbooks | Category | IDs | Covers | |----------|-----|--------| | A — Instance | A1 | Launch failures | | B — Performance | B1-B3 | Vacuum/bloat, connection pooling, pg_stat analysis | | C — Connectivity | C1 | Connection failures | | D — Parameters | D1 | Parameter group issues | | E — Replication | E1-E2 | Physical replication, logical replication | | F — Backup | F1 | Backup and PITR | | G — Extensions | G1 | Extension management | | H — Upgrades | H1 | Version upgrades | | I — Proxy | I1 | RDS Proxy | | J — Security | J1 | Encryption | | Z — Catch-All | Z1 | General troubleshooting |