AW: Major upgrade in patroni cluster - Mailing list pgsql-admin

 

Hi Raj,

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

1. 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.
2. 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.
3. 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.

Best regards,

Mahesh Sathe