Upgrading a 5TB PostgreSQL Environment Under Replication Constraints

Major database upgrades are often presented as technical exercises: install new binaries, run pg_upgrade, test the application and move on. In practice, production environments are rarely that straightforward.

This case study describes the upgrade of a multi-terabyte PostgreSQL data warehouse from version 12 to version 17 while preserving disaster recovery capability and minimising operational risk. The challenge was not the upgrade itself, but designing an approach that respected the realities of the production environment: limited storage, constrained network bandwidth between sites, and a maintenance window that could not simply be extended if things took longer than expected.

The project demonstrates that successful production upgrades depend as much on planning, rehearsal and decision-making as they do on technical knowledge.

The organisation's PostgreSQL platform was approaching an important milestone. PostgreSQL 12 was nearing end of life, and the database estate needed to move onto a supported release without disrupting the systems that depended upon it.

Keeping major database platforms reasonably current is often preferable to allowing them to drift several releases behind. Smaller, planned upgrades generally carry less operational risk than infrequent large jumps, while also giving development teams access to newer database capabilities.

During the planning phase another consideration emerged: PostgreSQL 18 had just been released.

On paper, targeting the newest available version might have appeared the obvious choice. In reality, the objective was not to deploy the latest software. The objective was to deliver a stable production platform with the lowest practical level of risk.

PostgreSQL 17 represented a fully supported release that had already accumulated sufficient operational experience across the wider community to give confidence in deploying it into production. It provided the functionality the business required without introducing the additional uncertainty that inevitably accompanies a brand-new major release.

Choosing PostgreSQL 17 was therefore a business decision as much as a technical one.

The production environment itself added further complexity. This was not a development database or a small departmental application. It was a large PostgreSQL data warehouse supported by streaming replication and Barman backups, providing both business-critical reporting and disaster recovery capability.

Everything about the upgrade would therefore need to be considered from an operational perspective, not simply a technical one.

Every business has priorities, and those priorities impose constraints on every project. Good engineering begins by understanding them rather than trying to ignore them.

In this case, the constraints shaped the entire solution.

The production database occupied approximately 5 TB of storage. Moving data at that scale changes the nature of an upgrade. Operations that complete in minutes on a development system can require many hours when measured in terabytes.

Storage presented the next limitation. There was insufficient spare capacity to maintain a complete duplicate of the production system for testing, meaning that rehearsals had to be performed on progressively larger environments rather than a full production clone.

The disaster recovery site introduced another challenge. Although streaming replication functioned normally during day-to-day operation, rebuilding the standby server after the upgrade—as recommended by the PostgreSQL documentation—would have required transferring the entire 5 TB database across the inter-site network.

Based on the available bandwidth, that rebuild was estimated to take between eight and nine days.

Technically, it was possible. Operationally, it was difficult to justify.

The organisation also worked within fixed maintenance windows due to manufacturing considerations. Extending an outage was not something the technical team could decide during implementation. Any significant overrun would have direct consequences for the business.

These constraints fundamentally changed the nature of the project. The question was no longer "How do we upgrade PostgreSQL?" Instead, it became "How do we upgrade PostgreSQL while preserving disaster recovery capability, avoiding a lengthy standby rebuild, and remaining in control of business risk?"

That question shaped every decision that followed.

It is often assumed that the success or failure of a major database upgrade is determined during the maintenance window. In practice, the maintenance window is simply where the preparation is put into effect.

Around a week was spent planning and rehearsing the upgrade before any production work was scheduled. This included reviewing PostgreSQL release notes, confirming extension compatibility, validating repmgr support and determining the most appropriate upgrade path to the target version.

The initial rehearsal answered an important technical question: could the upgrade be completed successfully?

Once that had been established, the emphasis shifted. Subsequent rehearsals were no longer about proving that the procedure worked, but about refining it. Each cycle simplified the runbook, removed unnecessary steps, introduced validation points and identified safe stopping points should circumstances change during production.

Particular attention was given to the proposed disaster recovery synchronisation process. This was repeatedly tested and refined until it could be executed predictably and verified at each stage.

By the time the production maintenance window began, the objective was no longer to discover whether the upgrade would work. That question had already been answered. The objective was to execute a well-rehearsed procedure while dealing only with the differences that inevitably exist between test and production environments.

The most demanding aspect of the project was not upgrading the primary database, but preserving disaster recovery capability without rebuilding the standby from scratch.

Having spent many years working with Oracle Data Guard, my initial instinct was to look for an equivalent approach. In a typical Oracle upgrade, the software binaries are upgraded on both the primary and standby servers before the database internals are upgraded. Once the primary database has been upgraded, Data Guard propagates the resulting changes automatically, allowing the standby to remain synchronised without recreating the database.

PostgreSQL does not work in the same way. Following a pg_upgrade, the standby cannot simply continue streaming from the upgraded primary. The documented approach is to build a new standby from the upgraded system, which is entirely appropriate in many environments.

For this project, however, that approach carried a business cost as well as a technical one. Rebuilding the standby meant transferring approximately 5 TB across the inter-site network — an estimated eight to nine days. For the length of that rebuild, the organisation would have been running production without disaster recovery cover at all: a primary-site failure during that window would have left the business with no standby to fall back on. An eight- to nine-day DR gap was not a technical inconvenience; it was a business risk that had to be weighed against the alternative of finding another way.

That risk shaped the objective: minimise the length of that DR gap rather than accept the eight- to nine-day exposure a full rebuild would require. Working from that objective, an incremental file-synchronisation approach using rsync offered a way to bring the standby back into line with the upgraded primary by transferring only what had changed, rather than the entire database.

The challenge was proving this could be done safely rather than simply quickly. Repeated rehearsals established which files needed to be synchronised and how each stage could be validated before the process became part of the production runbook.

The result avoided the eight- to nine-day rebuild and, with it, the extended DR exposure that would have accompanied it. The objective was not simply to save time. It was to protect the business's disaster recovery position throughout the upgrade, using an approach that had been rehearsed and validated rather than accepting an avoidable window of risk.

Despite the amount of preparation, production inevitably differed from the rehearsal environments.

Additional operating system packages were required and configuration differences emerged that had not been encountered during testing. None of these issues fundamentally changed the upgrade strategy, but each added time to the implementation.

This illustrates the value of rehearsal. The objective is to identify and resolve as many issues as possible before production, recognising that practical constraints rarely allow an exact replica of a live environment. Some differences, and occasionally some surprises, are therefore inevitable. The rehearsals were intended to minimise those uncertainties so that any remaining issues could be managed without compromising the success of the upgrade.

As the maintenance window progressed, it became apparent that although the upgrade itself was proceeding successfully, the accumulated delays meant that completing the final stage would exceed the agreed outage.

At that point, management was presented with two options. The upgrade could continue to PostgreSQL 17, accepting an extended production outage, or the system could be returned to service on PostgreSQL 15 and the remaining work completed during a second maintenance window.

The recommendation was to stop at PostgreSQL 15.

The upgrade could have continued, but doing so would have exceeded the agreed maintenance window without providing any immediate operational benefit. PostgreSQL 15 was already a current, fully supported release, and returning the system to service represented the lower-risk option for the business.

Management accepted the recommendation. Production service resumed on PostgreSQL 15, and the final stage of the upgrade was scheduled separately.

The decision reinforced an important principle: the maintenance window was a business constraint, not a technical target. Respecting that constraint was more important than completing every planned task in a single outage.

The second maintenance window completed the upgrade from PostgreSQL 15 to PostgreSQL 17.

From a technical perspective, this was the simpler of the two maintenance windows. The remaining work was completed without the time pressure that had influenced the earlier decision to stop at PostgreSQL 15.

The project, however, was not considered complete simply because the primary database was running the new version of PostgreSQL. Success depended on restoring the disaster recovery environment and confirming that replication had resumed correctly.

Once the standby had successfully rejoined replication, the original objectives had been achieved. The organisation had a current, supported PostgreSQL platform while retaining the same level of operational resilience that existed before the upgrade.

Only then was the upgrade considered complete.

The project achieved all of its original objectives.

• PostgreSQL was successfully upgraded from version 12 to version 17.
• The database platform returned to a current supported release cycle.
• Disaster recovery capability was preserved throughout the migration.
• The organisation avoided a lengthy standby rebuild that would have occupied the inter-site network for more than a week.
• Production service was restored without allowing an uncontrolled extension of the agreed maintenance window.
• Management remained informed and in control of every significant decision throughout the project.

Most importantly, the organisation finished the project with both its production platform and its disaster recovery capability operating as intended.