/rds-oss

RDS PostgreSQL Live Precheck Queries

Run these against the database to identify upgrade blockers and behavior changes.

Connection Methods

SSM Run Command

aws ssm send-command --instance-ids {instance_id} --document-name "AWS-RunShellScript" \
  --parameters 'commands=["export PGPASSWORD=$(aws secretsmanager get-secret-value --secret-id {secret_arn} --query SecretString --output text | jq -r .password); psql -h {endpoint} -U {username} -d {database} -c \"{query}\""]' \
  --region {region} --output json --query "Command.CommandId"

Preferred: IAM database authentication — where supported, use aws rds generate-db-auth-token to produce a short-lived token and connect with --no-password. This avoids any password in the environment or command history.

Fallback: Secrets Manager retrieval (shown above) — never pass plaintext passwords in SSM command parameters, as they are visible in SSM command history, CloudTrail logs, and process listings. Note that export PGPASSWORD=... still exposes the value in the shell process environment (/proc/<pid>/environ) for its lifetime; where IAM auth is unavailable, prefer a .pgpass file (chmod 600) over environment variables. If query results may contain sensitive data, enable KMS encryption on the SSM Run Command output. Use minimal-privilege credentials (a read-only user scoped to the precheck schemas) rather than the master user.

Note: RDS Data API is NOT available for standalone RDS instances.

Precheck Queries

1. Extensions and Versions

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Flag: Check target version supports each extension.

2. Hash Indexes

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE indexdef LIKE '%USING hash%';

Flag: 🟡 Must REINDEX after upgrade.

3. Unknown/Invalid Data Types

SELECT n.nspname, c.relname, a.attname, t.typname
FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
AND t.typname IN ('unknown');

Flag: 🔴 Unknown types block upgrade.

4. Logical Replication Slots

SELECT slot_name, plugin, slot_type, active FROM pg_replication_slots;

Flag: 🔴 Active logical replication slots BLOCK major upgrades.

5. Prepared Transactions

SELECT * FROM pg_prepared_xacts;

Flag: 🔴 Prepared transactions BLOCK the upgrade.

6. Objects Owned by System Roles

SELECT n.nspname, c.relname, r.rolname as owner
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_roles r ON c.relowner = r.oid
WHERE r.rolname IN ('rdsadmin','rds_superuser')
AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast');

Flag: 🟡 May block upgrades.

7. Database Encoding and Locale

SELECT datname, datcollate, datctype, encoding FROM pg_database
WHERE datname NOT IN ('template0','template1','rdsadmin');

8. Custom Data Types

SELECT n.nspname, t.typname, t.typtype FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
AND t.typtype IN ('c','e','d');

9. Critical Extensions

SELECT extname, extversion FROM pg_extension
WHERE extname IN ('postgis','postgis_topology','postgis_raster','pg_partman',
'pglogical','citus','pg_cron','pg_stat_statements');

Flag: Version-specific compatibility. Check target version supports them.

10. Table and Index Bloat

SELECT schemaname, relname, n_live_tup, n_dead_tup,
  CASE WHEN n_live_tup > 0 THEN round(n_dead_tup::numeric/n_live_tup::numeric * 100, 2) ELSE 0 END as dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC LIMIT 20;

11. reg* Type Columns

SELECT n.nspname, c.relname, a.attname, t.typname
FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE t.typname IN ('regproc','regprocedure','regoper','regoperator','regclass',
'regtype','regconfig','regdictionary')
AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast');

Flag: 🟡 reg* types store OIDs that may change after upgrade.

12. Stale Table Statistics

SELECT schemaname, relname, n_live_tup, n_mod_since_analyze,
  last_analyze, last_autoanalyze,
  GREATEST(last_analyze, last_autoanalyze) AS last_stats_update,
  EXTRACT(EPOCH FROM (now() - GREATEST(last_analyze, last_autoanalyze)))/86400 AS days_since_analyze
FROM pg_stat_user_tables
WHERE (last_analyze IS NULL AND last_autoanalyze IS NULL)
   OR GREATEST(last_analyze, last_autoanalyze) < now() - interval '7 days'
ORDER BY n_live_tup DESC;

Flag: 🟡 Use this to record which tables have stale statistics as a pre-upgrade baseline — it helps you spot post-upgrade plan regressions. A major version upgrade does not carry statistics across, so statistics are recalculated after the upgrade — see the post-upgrade checklist, which scopes ANALYZE to the affected tables in a low-traffic window.

Result Analysis

Generate:

1. Categorized findings (🔴/🟡/🟢)
2. For each finding: what was found, why it matters, action to take
3. Extension compatibility matrix for target version
4. Recommended post-upgrade REINDEX/ANALYZE plan