Upgrading Amazon RDS for PostgreSQL: A complete technical guide with Terraform and Python

Upgrading the version of an Amazon RDS for PostgreSQL instance is a critical task, especially in production environments. Beyond the upgrade at the managed service (AWS) level, there is a series of precautions at the database (DBMS) level to ensure integrity, performance, and stability after the migration.

In this post, I walk through all the essential steps, automating the infrastructure with Terraform and the DBMS tasks with Python.

Process overview

Upgrading the version of an Amazon RDS for PostgreSQL instance is a process that, while it may look simple at the infrastructure layer, involves a number of essential precautions both in the managed service and at the logical level of the database. It should be treated as a critical upgrade, because any failure can seriously affect the availability and performance of the applications that depend on it.

Broadly speaking, the upgrade is made up of two main fronts, each with well-defined steps:

1. Infrastructure: Managed Service (AWS RDS)

This is the layer where the PostgreSQL engine is upgraded within the AWS-managed infrastructure. The main steps are:

• Infrastructure preparation: This involves reviewing the instance parameters and backup policy, checking compatibility with the new version, and analyzing known impacts (AWS Release Notes). It is essential to make sure all instance settings are compatible with the new engine.
• Upgrade strategy: The upgrade can be done:

— During the maintenance window: the recommended method for critical environments, since it minimizes the risk of unexpected downtime.

— Immediately: the fastest method, suitable only for development or staging environments, where an immediate impact is acceptable.

• Execution with Terraform: The upgrade is done by changing the engine version in the Terraform code, which keeps the infrastructure under version control. The steps in this phase are:

1. Update the Terraform file.
2. Run terraform plan to validate the change.
3. Run terraform apply to apply the change to the infrastructure.

• Monitoring and Validation: After execution, it is essential to monitor the instance status, confirm the new version in the AWS console, and review the basic metrics to catch any anomaly.

This layer handles the physical and operational upgrade of the database engine, but it does not deal with the logical side or the performance of the database's internal objects.

2. Database: DBMS (PostgreSQL)

Physically upgrading the engine is not enough to guarantee the stability and performance of the database. The logical layer requires a series of measures to keep things consistent and optimize performance after the upgrade. The main steps include:

Pre-upgrade checks

• Consistency validation: Checking for invalid objects, analyzing persistent connections, and gathering general state information.
• Logical backup (full dump): Although AWS snapshots are reliable, a logical dump provides an extra layer of safety for schemas, functions, triggers, and other logical objects.

Post-upgrade actions

• Reindexing: Internal changes in the engine can affect index efficiency. Rebuilding the indexes ensures they are optimized for the new version.
• Statistics update: Essential so that the query optimizer works with up-to-date data and produces good execution plans.
• Full vacuum: Physically reorganizes the database, cleans up dead tuples, and improves the efficiency of tables and indexes.
• Extension check: It is important to review all installed extensions and confirm they are compatible with the new PostgreSQL version.

Automated Tests

• Running critical queries and measuring performance before and after the upgrade to identify possible performance regressions.
• Data integrity checks and overall consistency validation.

Unlike the managed service, where AWS takes care of the infrastructure, here the responsibility for keeping the logical environment healthy rests entirely with the technical team.

Part 1: Infrastructure with Terraform

Upgrading an Amazon RDS for PostgreSQL instance with Terraform offers major advantages in terms of traceability, version control, and environment reproducibility. Still, the simple act of changing the engine version requires careful planning and meticulous execution to mitigate risk.

Below I detail all the steps needed to carry out this upgrade safely and efficiently.

1.1 Prerequisites

Before starting the upgrade, you need to make sure the environment is ready. This includes:

• Terraform version: Terraform 1.x or later is recommended for compatibility with the latest AWS providers.
• AWS Provider: Make sure terraform-provider-aws is up to date, since older versions may not recognize new PostgreSQL engine versions.

Backup Policy

— Automated Snapshots: Make sure the backup_retention_period parameter is set (to a value greater than zero) so that automated snapshots exist.

— Manual Snapshot: Even with automation, take a manual snapshot before the upgrade so you have full control over restoration in case of failure.

— Incompatibility Check: Read the AWS and PostgreSQL release notes to understand whether there are breaking changes between the current and target versions.

1.2 Planning the upgrade

The main Terraform resource for managing the RDS instance is aws_db_instance. The upgrade happens by changing the engine_version parameter.

Example Terraform configuration:

resource "aws_db_instance" "postgres" {
  identifier              = "meu-postgres-prod"
  engine                  = "postgres"
  engine_version          = "15.5"
  instance_class          = "db.t3.medium"
  allocated_storage       = 100
  storage_type            = "gp2"
  username                = var.db_user
  password                = var.db_password
  backup_retention_period = 7
  skip_final_snapshot     = false
  apply_immediately       = false
  parameter_group_name    = "postgres15-parameters"
  maintenance_window      = "mon:03:00-mon:04:00"

# Outras configurações relevantes, como vpc_security_group_ids, subnet_group_name etc.
}

Critical aspects

• engine_version: Change it to the desired version (for example, from 14.x to 15.x).
• apply_immediately:

false: waits for the next maintenance window. true: applies immediately (not recommended in production).

• parameter_group_name: Some upgrades require a new Parameter Group, especially when parameters change or new ones are introduced.
• maintenance_window: Define it clearly so the upgrade happens at a controlled time.

Recommendations

• Always create a new Parameter Group for major versions instead of reusing the old one, even if the parameters are initially the same.
• Also check whether you need to create a new Option Group (for specific features such as TDE or PostGIS).

1.3 Execution

Once Terraform is configured, follow these steps:

• Code review

— Validate the changes with terraform fmt and terraform validate.

— Submit the code for review if you are in a corporate environment (Pull Request).

• Simulation (Dry Run)

— Run terraform plan -out=tfplan to simulate the execution.

— Check the output and verify that the only relevant change is engine_version.

• Execution

— Run terraform apply tfplan.

If the upgrade is not immediate, check the maintenance window and plan ahead to notify stakeholders.

Important

• A major engine upgrade (for example, from 14 to 15) always involves downtime.
• Even patch upgrades (for example, from 15.3 to 15.5) can cause temporary unavailability.

1.4 Monitoring and Validation

After running terraform apply, keep a close eye on the instance status in the AWS console or via the CLI/Terraform outputs.

What to watch

• Initial status: The instance will go into the modifying or upgrading state.
• Terraform logs: Follow the output to make sure there were no silent errors.
• AWS Console: Validate:

— Engine: is it on the new version?

— Parameters: is the correct Parameter Group attached?

— Option Group: is it correct?

— Backups: automated and manual snapshots remain valid.

Key metrics to monitor after the upgrade

• CPU Utilization
• Database Connections
• Write IOPS / Read IOPS
• Replica Lag (if there are replicas)
• Deadlocks and locks (via PostgreSQL logs)

Part 2: PostgreSQL Maintenance with Python

After the engine is upgraded at the infrastructure level, the database needs to go through maintenance and validation procedures to make sure it is sound and running optimally. Although AWS does the work of upgrading the engine, it does not tune or optimize the database's logical objects, which leaves indexes, statistics, and extensions to the DBA/DevOps team.

In this part, we cover how to automate these tasks with Python, for reproducibility and speed.

2.1 Prerequisites

• Python >= 3.8
• Libraries

psycopg2: Connecting to and running commands on PostgreSQL. logging: For monitoring execution logs. dotenv: (optional) for storing environment variables in a .env file. time, sys: For execution control and orchestration.

Installing the packages

pip install psycopg2-binary python-dotenv

Basic Connection Structure:

import psycopg2
from dotenv import load_dotenv
import os

load_dotenv()

conn = psycopg2.connect(
    host=os.getenv("DB_HOST"),
    port=os.getenv("DB_PORT"),
    database=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD")
)
cursor = conn.cursor()

2.2 Pre-upgrade checks

These steps should be run before the engine upgrade, as part of the safety and consistency strategy.

a) Check the current version and active connections

It is important to make sure there are no persistent connections that could block the upgrade, and to record the current database version.

cursor.execute("SELECT version();")
print("Versão atual:", cursor.fetchone())

cursor.execute("""
SELECT datname, numbackends 
FROM pg_stat_database;
""")
for db in cursor.fetchall():
    print(f"Banco: {db[0]} | Conexões ativas: {db[1]}")b) Dump Lógico (Backup Adicional)

Despite RDS snapshots, a logical dump protects objects such as functions, triggers, and views that cannot be recovered from physical snapshots. Automate it with subprocess:

import subprocess

dump_command = [
    "pg_dump",
    "-h", os.getenv("DB_HOST"),
    "-U", os.getenv("DB_USER"),
    "-d", os.getenv("DB_NAME"),
    "-F", "c",
    "-f", "backup_pre_update.dump"
]

subprocess.run(dump_command)

2.3 Post-upgrade maintenance

After the version upgrade, a few actions are essential to keep the database healthy.

a) Reindexing all indexes

Engine changes can affect index performance. Rebuilding ensures their integrity and optimization.

cursor.execute("""
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT schemaname, indexname
               FROM pg_indexes
               WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        RAISE NOTICE 'Reindexando índice: %.%', rec.schemaname, rec.indexname;
        EXECUTE format('REINDEX INDEX CONCURRENTLY %I.%I;', rec.schemaname, rec.indexname);
    END LOOP;
END$$;
""")
conn.commit()

Note: Using CONCURRENTLY minimizes locking, but it cannot be used inside a transaction. In critical environments, consider reindexing one index at a time.

b) Updating statistics

For the query optimizer to work well, run:

cursor.execute("ANALYZE VERBOSE;")
conn.commit()

This command updates the statistics for every table in the database.

c) Full vacuum

It is advisable to run a VACUUM FULL on smaller databases, or a plain VACUUM on larger ones, to avoid long periods of unavailability.

cursor.execute("VACUUM FULL VERBOSE;")
conn.commit()

For large databases, go with a regular VACUUM:

cursor.execute("VACUUM VERBOSE;")
conn.commit()

d) Checking and validating extensions

Some extensions may have changed or require an update. Run:

cursor.execute("""
SELECT extname, extversion 
FROM pg_extension;
""")
for ext in cursor.fetchall():
    print(f"Extensão: {ext[0]} | Versão: {ext[1]}")

Check the official PostgreSQL site or the release notes to confirm the versions are compatible with the new engine.

2.4 Automated tests

Running critical queries and measuring response times helps you quickly spot performance regressions.

Example:

import time

queries = [
    "SELECT COUNT(*) FROM minha_tabela_critica;",
    "SELECT * FROM outra_tabela LIMIT 10;"
]

for q in queries:
    start = time.time()
    cursor.execute(q)
    cursor.fetchall()
    end = time.time()
    print(f"Query: {q[:30]}... Tempo: {end - start:.4f} segundos")import time

If you want to log the results:

import logging

logging.basicConfig(filename='db_post_update.log', level=logging.INFO)

for q in queries:
    start = time.time()
    cursor.execute(q)
    cursor.fetchall()
    end = time.time()
    logging.info(f"Query: {q[:30]}... Tempo: {end - start:.4f} segundos")

Post-upgrade flow

I suggest organizing the steps into a master function to make sure they all run in the right order:

def post_update_maintenance():
    print("Iniciando rebuild de índices...")
    reindex_indices()

    print("Atualizando estatísticas...")
    update_statistics()

    print("Executando vacuum completo...")
    vacuum_full()

    print("Validando extensões...")
    validate_extensions()

    print("Executando testes de performance...")
    run_performance_tests()Essa abordagem simplifica o disparo da rotina completa em produção ou homologação.

Final thoughts on Part 2

The logical upgrade of the database is just as critical as the physical engine upgrade. Automating the steps in Python brings speed, safety, and standardization, especially for large-scale databases or mission-critical environments.

Beyond the mandatory maintenance tasks (reindex, vacuum, analyze), running automated tests adds an extra layer of operational safety, reducing the chance of regressions going unnoticed in the short term.

Good practice is to document every run (via logs) and compare the results with previous runs, building a historical baseline for future upgrades.

Checklist for the whole process

— Snapshot Backup✅ —Logical Dump✅ — Engine Upgrade (Terraform)✅ — Connection and Version Checks✅ — Index Rebuild✅ — Statistics Update✅ — Vacuum✅ — Performance Tests✅ — Extension Validation✅

Conclusions

Upgrading the engine of an RDS instance is not just about changing the version and hoping everything works. The invisible part — post-upgrade maintenance — is essential to avoid performance problems and data inconsistencies.

Automating these steps with Terraform and Python ensures reproducibility, safety, and efficiency. On top of that, keeping track of the database's extensions and metrics is indispensable for keeping the environment healthy.

Want to go deeper? Follow me at www.cdiego.blog for more technical guides like this one 🚀.