In-place PostgreSQL major upgrades possible for Spock distributed clusters

A new technical workflow has been detailed for performing in-place major version upgrades on distributed PostgreSQL clusters using Spock. While rolling upgrades—adding new nodes and retiring old ones—are standard, they often require significant extra hardware or storage to hold duplicate datasets. This alternative method focuses on upgrading individual nodes one at a time within a multi-master environment.

By utilizing the pg_upgrade --link command, administrators can upgrade a single node while the remaining nodes in the cluster continue to handle application writes. This approach minimizes downtime to the specific node being serviced rather than the entire application. The process involves draining traffic, pausing Patroni management, and carefully matching configuration settings like encoding and checksums to ensure cluster consistency.

Technical requirements for in-place upgrades

• Storage Optimization: The use of the --link flag in pg_upgrade employs hard-links for data files instead of copying them, which prevents the need for doubling the storage capacity during the upgrade process.
• Configuration Synchronization: Successful upgrades require the new PostgreSQL instance to explicitly match the old instance's encoding and data checksum settings, as PostgreSQL 18 enables checksums by default.
• Spock Extension Integration: To prevent library loading errors during the upgrade, Spock must be added to shared_preload_libraries in the new postgresql.conf before running the upgrade utility.
• Replication Continuity: The workflow requires disabling DDL replication across the cluster during the rollout and necessitates that the Spock version remains consistent across both the old and new binaries.
• Cluster Topology: A minimum of three nodes is required to maintain high availability, ensuring at least two nodes remain operational to accept writes while one node undergoes the upgrade.

Why it matters

This method provides a critical operational path for database administrators managing large-scale distributed PostgreSQL clusters where hardware constraints prevent traditional rolling upgrades. It allows for seamless version transitions in multi-master setups, reducing the total cost of ownership and infrastructure overhead for high-availability database environments.