Major PostgreSQL Upgrade: Where Production Breaks
Moving to a new major version branch is not a package upgrade but a change to the internal catalog format: the new server physically cannot read data files from the previous branch, and an old cluster cannot be started on it.
That is why a major PostgreSQL upgrade always follows a separate procedure — with preparation, a rehearsal on a copy, and separation between "upgraded the build" and "switched production." Rollback through the package manager does not work here: the server files will return, the data will not.
First thing that breaks is extensions. Some require rebuilding for the new branch, some change the set of types, functions, and operators, and indexes over user-defined types may be invalid after transfer.
Errors after switching appear not at startup but on specific queries: an operator is not found, a type cast raises an exception, an aggregate returns a different value. Therefore extension compatibility is checked before the window, not inside it.
Second source of silent failures — configuration parameters during the transition. Some directives are removed, some renamed, some kept the name but changed meaning or default value.
The server will start, but the query plan, cost estimate, and garbage collector behavior will be different — disk grows without a single line in the log.
And the main constraint — the database downtime window. An upgrade with shutdown fits into minutes, but requires full downtime and leaves no room for a second attempt.
Transition via logical replication gives almost no downtime, but stretches preparation and requires explicit reconciliation of discrepancies. Next we analyze how this is closed in practice.
What Changes After Moving to a New Major Version
A managed transition to a new major version produces an effect not in the list of new features but in removed limitations.
The planner and storage in the new branch serve queries that hit a ceiling on the previous one: parallel operations on large tables, partitioning without manual triggers, indexes without periodic rebuilds.
Service downtime is reduced to the moment of switching — compatibility checks and warm-up run on a replica in advance.
The eliminated class of failures is felt most strongly. Workarounds live on a schedule: scheduled restarts, manual bloat cleanup, monitoring directory sizes. Such tasks do not fail loudly — they are quietly skipped, and then the database degrades into an incident. After the transition, PostgreSQL performs these operations itself, and maintenance windows shrink.
Predictable rollback is designed before switching. The previous cluster remains the leader, changes flow to the new branch, and the reverse move is returning application routing to the previous node, not restoring from a backup.
The plan is rehearsed on a copy: if query plans diverge after switching or a specific query slows down, the return takes minutes.
The output is a cluster on a single branch: replicas, failover rules, and security updates at the same level, without a zoo of versions.
The effect of the upgrade is measured before and after: response time of key queries, number of maintenance interventions, recovery time when a node fails.
Upgrade Methods: Comparison of Modes and Limitations
A major PostgreSQL upgrade changes the data directory format, so the transition method is chosen along three dimensions: acceptable downtime window, database size, and presence of incompatible objects — extensions, types, procedural languages.
There is no universal option, and in practice they are combined: for example, first transfer with the utility, and manually create mismatched objects.
Utility-based transfer upgrades the data directory in place and transfers files directly, so downtime is measured in minutes even on a large database. The price is disk space for both directories and matching encoding and bitness.
Logical replication between versions deploys the target server of the new branch alongside and streams changes on the fly: downtime is reduced to traffic switching, but objects outside the publication, sequences, and permissions are transferred separately.
Dump and restore remains the most predictable path when the schema is also being rebuilt or the platform changed — at the cost of full downtime for unload and load.
A parallel cluster is deployed on a separate node, the load is moved there, and the old one is kept as a rollback point, paying with a second set of resources.
The limitations of transition methods come down to one fork: either a short window and strict compatibility requirements, or a managed migration with manual rework.
| Variant | When it fits | Limitations |
|---|---|---|
| Utility-based transfer | Large database, short window, compatible extensions | Need disk space, matching encoding and bitness |
| Logical replication between versions | Service cannot be stopped, step-by-step migration | Manual transfer of objects outside the publication and permissions |
| Dump and restore | Platform change, schema rebuild | Downtime for the entire unload and load |
| Parallel cluster | Need safe rollback and isolation | Double resources, synchronization of permissions and sequences |
Work Order: From Compatibility Audit to Switching
A major PostgreSQL upgrade is not a single pg_upgrade command but a sequence where each step removes a specific risk.
Between branches, system table catalogs, default behavior, and extension requirements change, so we go from inventory to a rollback procedure that stays ready until the end of the work.
-
Inventory of objects. We collect the list of databases, schemas, extensions, user-defined types, triggers, rules, and full-text configurations. Separately, we record encoding, locale, owners, and permissions — the things most often lost during migration.
-
Compatibility audit. We compare objects with the target branch: what is supported, what requires an extension update, what is removed. The output is a list of changes in the schema and queries that we make before switching, not after.
-
Test run on a copy. We deploy a copy of the cluster with the same data volume and a similar load profile and run the upgrade end to end: we get a real estimate of the downtime window and see extension problems in advance.
-
Configuration and integrity control. We transfer parameters, rebuild statistics, check background cleanup, heavy query plans, and replication. We verify row counts in key tables, sequences, and access permissions.
-
Switching plan. We write out the steps: stop writes, read-only mode, final synchronization, change the application connection point, remove restrictions — with an owner and time estimate for each.
-
Rollback procedure and handover. We keep the old directory and replica untouched until the result is confirmed, fix return criteria in advance, and test the reverse path on a copy. After that — documentation and monitoring of the new branch.
What Remains: Configurations, Schemas, Procedures
After the upgrade, the customer is left not only with a working database but also with a set of artifacts that can be used to maintain it further — on their own or with our help.
We hand them over to a repository with change history, not to a chat or one engineer's notes: the next specialist reconstructs the picture without oral explanations.
- Cluster configurations — working configuration files with notes on why such buffer sizes, timeouts, and logging mode were chosen, and what was changed for the load.
- Replication scheme — node topology, their roles, switching order, and acceptable replica lag.
- Access policies and roles — permission matrix, owners of objects and extensions, confirmation that privileges were transferred without losses.
- Reconciliation and rollback scripts — queries for comparing row counts and checksums of key tables before and after, plus the order for returning to the previous version.
- Database documentation — object map, application dependencies on types and extensions, bottlenecks that surfaced during migration.
- Upgrade procedure — step-by-step order: preparation, run on a copy of the production volume, stop writes, transfer, checks, traffic enablement — with the work window and stop criteria.
Such a set turns a one-time upgrade into a reproducible procedure: the next major version follows an already tested route rather than starting from scratch.
Case: Transition Without Long Downtime and With Fast Rollback
The cluster served data marts and a background task queue, so blocking writes for several hours meant stopping data ingestion.
In the previous transition between major PostgreSQL versions, unavailability stretched to almost four hours, and some objects had to be recreated manually after launch — errors in them surfaced within the first day.
| Metric | Before | After |
|---|---|---|
| Write unavailability | 3 h 40 min | 40–50 s for switching |
| Incidents in the first two weeks | 7, of which 2 with partial data loss | 0 |
| Return to previous version | about 2 h, restore from copy | 3–5 min, via reverse replica |
| Objects requiring manual fixes | 34 % | 9 % |
Preparation began in advance: the transfer was run twice on a full cluster copy, a registry was fixed of objects that do not migrate automatically — functions in a non-native language, custom extensions, views with aggregates — and a migration command with validation on test data was written for each. This cut the share of manual rework.
Switching was done in a low-load window: ingestion was stopped, changes were caught up, the new version was brought up, and traffic was returned. Read replicas remained available the whole time, so external reports were not interrupted.
The reverse replica on the previous version was kept for a day after switching — rollback was tested in rehearsal and we knew it takes minutes, not hours. If discrepancies are found, we return to the previous version and continue working until we find the cause.
Such a plan is the main insurance: it allows a transition without long downtime and does not depend on a single attempt.
What to Do If the Database Already Runs in a Cluster With Replicas?
A cluster with replicas is upgraded not node by node but as a new replication topology. Physical replication between major versions is incompatible: the change log format differs, so a replica of the previous version will not connect to the upgraded primary.
Hence the main consequence — rebuilding replicas is inevitable: they are built anew from a copy of the already upgraded primary.
The step-by-step cluster transition looks like this. First we make sure replicas are not lagging and no replication slots remain holding logs. Then we transfer data on the primary to the new major version — without re-streaming the database itself.
After that, we take a copy of the primary and build replicas from it: they join the stream and catch up. Next we verify row counts in key tables and the lag of each replica, and only then switch the application.
Read replicas serving reports and exports are removed from balancing in advance — while rebuilding is underway, the application must not send queries there.
If reporting can tolerate it, they can remain on the previous version until the very switching, but writes never go to them at any moment.
When the downtime window is critical, the topology is assembled differently: a new cluster is brought up alongside and filled via logical replication from the old one. Switching then takes seconds, and rollback is returning the application to the previous node.
The price is schema compatibility on both versions and manual tuning of what logical replication does not transfer.
Technical Doubts Before Transition: Questions and Answers
Before moving a cluster to a new major version, doubts usually come down to five points: downtime duration, incompatible extensions, safe rollback, database size, and changes in application code. Below are short answers; the detailed analysis follows further.
How long will downtime be?
We perform compatibility checks, rehearsal on a copy, and extension preparation on the running cluster. We stop transaction ingestion only for the directory transfer — that is minutes, not hours; on large databases, parallel transfer reduces the time.
What to do with incompatible extensions?
We compare installed extensions with the target branch before work starts: some require a package update, rare ones require replacement with an analogue. This way incompatibility does not surface at the moment of switching.
Is safe rollback possible?
Until the new branch is accepted into operation, the old directory and settings remain untouched: return is switching the service to the previous directory. Before the point of no return, rollback takes as long as the switching itself.
Will a large database size hold up?
The transfer happens at the level of directory files, not row-by-row unloading, so time does not grow linearly with volume. For large databases, we separately check free space for the new directory and logs, as well as index build time.
Will application code need changes?
The changes mainly concern default behavior and deprecated constructs in queries. We run the application on a copy of the new branch and capture query plans — we record discrepancies before switching.
Let's Discuss the Task and Come Back With a Work Plan
To discuss the task substantively, we need a minimum of inputs: the current major version and the target one, database size and its growth, number of databases per instance, presence of physical and logical replicas, installed extensions and their compatibility with the new branch.
Separately, it is important to understand the load profile and the acceptable downtime window that switching must fit into.
Based on this data, we assemble a transition plan and rollback scenario, not general recommendations. We come back with a document that an engineer can work from without further clarification.
- Transition plan by stages: preparation, validation on a data copy, switching, post-switch control — with points where one can stop.
- Rollback scenario: to what point we return, what happens to data accumulated during switching, how long it takes.
- Timeline estimate: duration of the downtime window and labor costs of your and our team.
- List of incompatibilities: extensions, deprecated configuration parameters, queries requiring changes.
- Procedure for updating replicas and requirements for disk reserve for the new branch.
- Handover of the procedure: checklist and instructions so that your team can perform the next major transition itself.
Describe the task in free form — configuration, downtime window constraints, what you have already tried and where you stopped.
We will come back with a timeline estimate, transition plan, and rollback scenario that can be discussed substantively, not at the level of general words.