Back to all use cases

ConsultingCross-Industry

A major version move with the write freeze measured on a copy before the window is booked

SaaS Platform Team

A typical engagement for a team that must move a production PostgreSQL cluster across major versions: method chosen on a rehearsal, a measured cutover and a rollback target kept until the new version has proved itself.

Overview

A typical engagement starts with a platform team that runs a PostgreSQL cluster on an older major version, with a primary, two standbys, a connection pooler and a few logical replication consumers. The community's final release for that version is close, the application cannot be down for long, and the last upgrade overran its window.

Challenge

pg_upgrade is the shortest route, but the team needs a write freeze it can defend and a way back it can describe. Logical replication can shorten the freeze, but it does not replicate DDL, sequence values or large objects, and the cluster has replication slots that feed other systems. Extensions, collations and drivers add unknowns that only surface when the whole procedure is run.

Environment

A self-managed PostgreSQL cluster on virtual machines, with Patroni-managed failover, pgBouncer in front, one or more logical replication subscribers, a few extensions and nightly base backups with WAL archiving.

Approach

AceMQ engineers run a readiness review, then restore a copy of production and run the candidate methods end to end on it, timing each phase. The method that fits the team's freeze budget and rollback requirement is chosen from that evidence. The runbook is rehearsed again on a pre-production environment before production is scheduled.

Solution

  1. Readiness review of extensions, collation provider, data checksum setting, reg* column types and roles still on MD5 passwords, with pg_upgrade --check on a restored copy
  2. Side-by-side rehearsal of pg_upgrade in a fast mode and a logical replication cutover, with the write freeze and the rollback described for each
  3. Checklist for the logical replication route: schema created ahead of time, sequence values set before the switch, large objects handled separately, replica identity on every published table
  4. Plan for Patroni, standbys, pgBouncer and the replication slots that feed other systems, including which are carried across and which are recreated
  5. Comparison of the slowest statements and query plans before and after, and statistics regenerated on the new version
  6. Timed cutover runbook with an agreed rollback decision point, and a retention period for the old cluster and its backups

Outcome

The engagement delivers a written method decision with its evidence, a timed runbook, a tested rollback target and a monitored period on the new version before the old cluster is retired. The customer's own engineers can run the cutover, and AceMQ can carry it out alongside them if they want it.

Technologies

PostgreSQLPatronipgBouncerLogical Replication

Related Reading

Need PostgreSQL Architecture Guidance?

AceMQ's senior PostgreSQL engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.