For a 3-node Patroni cluster with PostgreSQL 17.10, here is the high-level approach I typically follow for a major version upgrade to 18.4 with minimal downtime.High-Level Strategy
Preparation & Testing:
Staging Environment: Perform the upgrade on a staging cluster that mirrors your production setup. This is critical for validating application compatibility and performance.
Release Notes: Thoroughly review the release notes for PostgreSQL 18.4 to identify any breaking changes, deprecated features, or specific collation/locale requirements that might impact your data.
Testing: Validate your pgBackRest backups and ensure you can restore from them before proceeding.
Upgrade Execution (Minimal Downtime):
Rolling Upgrade: Utilize Patroni’s rolling upgrade capability. You can upgrade one replica at a time by stopping the Patroni service, upgrading the PostgreSQL binaries, and restarting.
Switchover: Once the replicas are upgraded, perform a controlled switchover to promote an upgraded replica to leader. Finally, upgrade the former leader.
pg_upgrade: In many cases, you may need to use the pg_upgrade utility. If you use it, ensure you run it with the -k (link) option where possible to significantly speed up the process and minimize downtime.
Key Considerations:
OS/Library Compatibility: Since you are moving to a newer PG version, ensure the underlying OS libraries (especially glibc) are compatible, as this can affect collation.
PgBouncer/HAProxy: Ensure your connection poolers and HA setup are correctly updated to point to the new binary paths if the installation location changes.
Validation: After the upgrade, run your application suite against the cluster to catch any performance regressions.
Let me know if you’d like to discuss specific steps for the pg_upgrade process or how to handle the replication configuration changes.
