Major PostgreSQL Upgrades on RDS with Blue/Green Deployments

Why Major Upgrades Get Postponed

Every PostgreSQL major version brings performance improvements, better tooling and security fixes, and every team I know postpones the upgrade. The traditional path on Amazon RDS, an in-place major version upgrade, takes the database offline for the duration of pg_upgrade plus pre- and post-upgrade checks. On a busy system that means a maintenance window, a nervous weekend and a rollback plan that amounts to "restore the snapshot and lose everything written since".

The pressure to upgrade has also risen. Older major versions eventually leave standard support on RDS and move into paid Extended Support, so deferring the upgrade now has a direct cost as well as a risk.

RDS Blue/Green Deployments, generally available for RDS for PostgreSQL since October 2023, change the shape of the problem. This post is general advice from planning upgrades of this kind; the details apply to any team running RDS for PostgreSQL.

How It Works

A blue/green deployment creates a copy of your production environment (blue) as a staging environment (green), keeps green in sync with blue, and then switches over by swapping endpoints.

For a PostgreSQL major version upgrade the mechanics are:

1. RDS creates the green instance from a snapshot of blue, then upgrades green to the target major version.
2. RDS uses PostgreSQL logical replication to stream changes from blue to green. Physical replication can't cross major versions, which is why logical replication is used here.
3. You test against the green endpoint while production continues on blue.
4. At switchover, RDS stops writes on blue, waits for green to catch up, synchronises sequences, then renames the instances so green takes over blue's endpoint names. Applications reconnect to the same hostnames.
5. The old blue instances are kept, renamed with an -old1 suffix, for you to inspect or delete.

aws rds create-blue-green-deployment \
  --blue-green-deployment-name orders-pg-upgrade \
  --source arn:aws:rds:eu-west-2:123456789012:db:orders-prod \
  --target-engine-version 16.4 \
  --target-db-parameter-group-name orders-pg16

The target parameter group must belong to the new major version's family, so create and review it before you start rather than discovering missing settings after switchover.

Prerequisites and Limitations

This is where most of the work is. Logical replication has real gaps, and a blue/green deployment inherits them.

Two queries I'd run before starting. First, tables that will break replication of updates and deletes:

SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (
    c.relreplident = 'n'
    OR (
      c.relreplident = 'd'
      AND NOT EXISTS (
        SELECT 1 FROM pg_index i
        WHERE i.indrelid = c.oid AND i.indisprimary
      )
    )
  );

Second, whether you use large objects at all:

SELECT count(*) FROM pg_largeobject_metadata;

Rehearse It

Never make production your first blue/green deployment. Restore a recent snapshot into a separate environment, create a blue/green deployment from it and switch over while a load generator writes to it. A rehearsal answers the questions that matter:

• How long does green take to create, upgrade and catch up?
• Does the application work on the new version? Run the full test suite and key queries against the green endpoint, and compare query plans for your most important queries, because planner changes between major versions can cause regressions.
• How does your application behave during the switchover? Watch connection pools, retries and error rates.
• How long does the switchover take with your number of sequences and your write load?

Rehearsal also flushes out things like an ORM migration that runs on deploy and would break the DDL freeze.

Switchover Guardrails and Timings

Switchover has built-in guardrails: RDS checks that both environments are healthy and that replication is caught up, and if the switchover can't complete within the timeout it rolls back and leaves both environments unchanged. The timeout defaults to 300 seconds and can be set from 30 seconds to one hour.

aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-EXAMPLE1234567 \
  --switchover-timeout 120

In practice, switchover is typically under a minute of write unavailability, but that depends on your load. My own guardrails around it:

• Pick a low-traffic window. Blue/green reduces downtime; it doesn't make it zero. Avoid trading peaks entirely.
• Check replica lag first. Switch only when lag is close to zero; long-running transactions on blue can delay the switchover.
• Stop non-essential writers. Pause batch jobs and queue consumers briefly. With SQS in front of background work, messages simply wait.
• Make clients resilient. Short DNS caching, connection pools that recycle broken connections, and retries on idempotent operations.
• Run ANALYZE on green after switchover. Planner statistics aren't carried across a major upgrade, and missing statistics are a common cause of post-upgrade slowness.

Rollback Thinking

Blue/green gives you an easy rollback before switchover: delete the green environment and nothing has changed. After switchover, it's different. Replication doesn't reverse, so writes made on the new version exist only there. The old instance is a point-in-time copy that is immediately out of date.

Be honest about this in the plan. Realistic options after switchover are to fix forward on the new version, or accept data loss by reverting to the old instance, or set up reverse logical replication yourself in advance, which is possible but adds complexity. For most teams, thorough rehearsal plus fix-forward is the right choice. Keep the old instance for a few days anyway, for comparison and forensics.

Alternatives

The Takeaway

RDS Blue/Green Deployments make major PostgreSQL upgrades a rehearsed, low-downtime operation rather than a weekend outage, but only if you respect logical replication's limits: primary keys, a DDL freeze, large objects and extensions. Rehearse on a restored copy, set tight switchover guardrails and decide your rollback stance before you start. Done that way, upgrades become routine enough that you stop falling behind.