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 <instance-id>
aws rds describe-events --source-identifier <instance-id> --source-type db-instance --duration 1440
aws rds describe-db-log-files --db-instance-identifier <instance-id>
aws rds download-db-log-file-portion --db-instance-identifier <instance-id> --log-file-name error/postgresql.log
aws rds describe-db-parameters --db-parameter-group-name <param-group>

Step 2 — Performance deep dive

aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name CPUUtilization \
  --dimensions Name=DBInstanceIdentifier,Value=<instance-id> \
  --start-time <start> --end-time <end> --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-<resource-id> \
  --metric-queries '[{"Metric":"db.load.avg"}]' \
  --start-time <start> --end-time <end> --period-in-seconds 300

Step 3 — PostgreSQL-specific diagnostics (via psql)

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

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 <ver> --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