Zero-Downtime PostgreSQL Upgrades for DBOD Using Replication and Connection Switching

This report presents a procedure for zero-downtime PostgreSQL version upgrades on the DBOD platform at CERN, fully automated by a Python prototype. Several approaches were identified each with different strengths and weaknesses. One method was chosen for which the prototype was implemented. The chosen method utilizes a PgBouncer proxy to orchestrate the transition, reducing downtime to roughly one second, visible as a brief increase in transaction latency by using a transaction pause. While the approach is constrained by factors like replication throughput, a freeze on DDL operations, and the need for client-side retry logic, it demonstrates that zero-downtime upgrades are achievable and offer a substantial enhancement to database availability.