Aurora PostgreSQL Live Precheck Queries

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

Connection Methods

SSM Run Command

Credentials MUST be retrieved from Secrets Manager at runtime inside the command, so passwords never appear in SSM parameters, CloudTrail logs, or the instance's process list.

aws ssm send-command --instance-ids {instance_id} --document-name "AWS-RunShellScript" \
  --parameters 'commands=["SECRET=$(aws secretsmanager get-secret-value --secret-id {secret_arn} --query SecretString --output text --region {region}) && PGPASSFILE=$(mktemp) && chmod 600 $PGPASSFILE && printf \"{endpoint}:5432:{database}:{username}:%s\\n\" \"$(echo $SECRET | jq -r .password)\" > $PGPASSFILE && PGPASSFILE=$PGPASSFILE psql -h {endpoint} -U {username} -d {database} -c \"{query}\"; rm -f $PGPASSFILE"]' \
  --region {region} --output json --query "Command.CommandId"

This writes the password to a temporary .pgpass file (chmod 600, removed after) rather than export PGPASSWORD, which is visible via /proc/<pid>/environ — matching the secure temp-file pattern used in the Aurora MySQL prechecks. Alternatively, prefer IAM database authentication where supported — it eliminates passwords entirely. See the AWS docs for enabling IAM auth on Aurora PostgreSQL.

If psql not installed:

Amazon Linux 2: sudo yum install -y postgresql
Amazon Linux 2023: sudo dnf install -y postgresql15
Ubuntu: sudo apt-get install -y postgresql-client

RDS Data API

aws rds-data execute-statement --resource-arn {cluster_arn} --secret-arn {secret_arn} \
  --database {db} --sql "{query}" --region {region}

Precheck Queries

1. Extensions and Versions

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Flag: Extensions that may not be available or changed in target version. Key ones: PostGIS, pg_partman, pglogical.

2. Hash Indexes (need REINDEX after upgrade from < PG 10)

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, restart_lsn FROM pg_replication_slots;

Flag: 🔴 ANY logical replication slot (active or inactive) blocks a major version upgrade — the pre-check fails until all are dropped. Confirm the slot's purpose, then drop unused slots. Even rows with active=false must be dropped (or restarted post-upgrade for pglogical).

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');

Flag: Verify locale compatibility with target version.

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. Large/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: These have version-specific compatibility. Check target version supports them.

10. Table and Index Bloat (performance baseline)

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. pg_stat_statements Top Queries (baseline)

SELECT calls, total_exec_time, mean_exec_time, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

12. Check for reg* Type Columns (can break cross-DB references)

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','regconfig','regdictionary','regnamespace','regcollation')
AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast');

Flag: 🔴 Unsupported reg* types block the upgrade (pg_upgrade can't persist them); remove before upgrading. regclass/regtype/regrole are exempt and survive.

13. 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: 🟡 If statistics are older than 7 days (or never analyzed), recommend running ANALYZE on affected tables before the upgrade. Optimizer statistics are NOT transferred during an Aurora PostgreSQL major version upgrade — pg_upgrade does not carry over the contents of pg_statistic. After every major version upgrade you must run ANALYZE (e.g. ANALYZE VERBOSE;) on every database on all instances to regenerate statistics; otherwise the new planner runs with no statistics and can choose poor plans. Running ANALYZE pre-upgrade does not help post-upgrade because the stats are discarded. Capturing/refreshing stats before the upgrade is still useful for baselining plans, but the authoritative remediation is a full post-upgrade ANALYZE. Each major PostgreSQL version refines the planner's cost model, making it more dependent on accurate statistics.

Action: For each table with stale stats:

ANALYZE schema_name.table_name;

For the entire database:

ANALYZE VERBOSE;

Also consider VACUUM ANALYZE for tables with high dead tuple counts to reclaim space and refresh stats simultaneously.

Result Analysis

After running queries, generate:

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

/amazon-aurora-postgresql

referencesupgrade-planning-prechecks-postgresql.md