PostgreSQL Major Version Upgrade Services: Method, Rehearsal and Rollback

pg_upgrade, logical replication or dump and restore

The PostgreSQL 18 release notes put it plainly: moving data from an earlier release needs a dump and restore, pg_upgrade or logical replication. They differ in how long writes stop, how much disk they need and what is left to go back to.

Two points decide most cases. First, pg_upgrade is the shortest route when the write freeze is measured in minutes and a restore is an acceptable rollback after the first start. Second, logical replication is the technique to reach for when the freeze must be shorter than a file transfer, and it comes with a checklist: the schema has to be created by hand on the new cluster, sequence values have to be set before the switch, and large objects have to be moved another way or stored in ordinary tables. The PostgreSQL documentation presents logical replication as a way to carry a cluster through pg_upgrade, and does not claim it shortens downtime, so we measure the freeze on the rehearsal rather than quote it.

Can you jump straight from 14 to 18? The pg_upgrade documentation says it supports upgrades from 9.2.X and later to the current major release, so the tool allows it. The checks are cumulative, though. Every release you skip adds items to the release notes that matter to you, which is why the readiness review reads the notes for each major in between and not only the last one.

What breaks between major versions

Most upgrade failures are not the data move. They are something the old server tolerated. These are the categories we check, with examples from the PostgreSQL 18 release notes where the target is 18.

• Extensions — pg_upgrade needs the target's extension binaries installed on the new cluster, and it writes a script to update extensions afterwards. PostGIS, TimescaleDB and similar extensions have their own version matrix with the server. We confirm a combination exists before the window is booked.
• Data checksums — PostgreSQL 18 enables data checksums in initdb by default, and pg_upgrade requires matching settings. An old cluster without checksums needs initdb --no-data-checksums on the new one, or a separate decision to enable them.
• Collations — Text indexes depend on the collation library that built them. Moving to a new operating system release, or to a cluster on a different provider, can change sort order silently. The 18 notes recommend reindexing full-text and pg_trgm indexes for clusters on non-libc providers.
• Column types — pg_upgrade does not support columns of reg* types such as regproc or regconfig, apart from regclass, regrole and regtype. They are found by the check and fixed before the window.
• Configuration files — pg_upgrade works on the data directory. The new cluster starts with its own postgresql.conf and pg_hba.conf, so settings and access rules from the old cluster are compared and merged by hand, and the differences are listed in the runbook.
• Authentication — MD5 password support is deprecated in 18, not removed. Setting an MD5 password now emits a warning, and the 18 notes say support will be removed in a future major, so the move to SCRAM is better planned than discovered.
• Planner statistics — PostgreSQL 18's pg_upgrade carries over most optimizer statistics but not extended statistics, so CREATE STATISTICS objects need an ANALYZE. Before 18, pg_upgrade carries none, and queries can run badly until ANALYZE finishes.
• Defaults and renamed behavior — Examples in 18: virtual generated columns by default, a changed effective_io_concurrency default, VACUUM and ANALYZE processing inheritance children, AFTER triggers running as the role active when the event was queued, and removed columns in pg_stat_wal. We diff the settings and replay your top statements.
• Clients — Drivers, ORMs and psql versions are tested against the target. The 18 notes warn that older psql clients can have problems with \copy against an 18 server.

The dates that set the order of work

PostgreSQL supports each major version for five years after its first release, with minor releases at least every three months. The table shows the community final release dates from postgresql.org and the end of standard support on Amazon RDS, read on 4 November 2026.

On RDS, AWS says Extended Support is charged per vCPU-hour from the day after standard support ends, on every instance including Multi-AZ standbys, that Reserved Instance discounts do not apply, and that the rate rises in the third year. The rates are on AWS's RDS for PostgreSQL pricing page, so we do not copy them here. Rates for Aurora, Cloud SQL and Azure differ and we price those from the provider's own page during the review.

Which target to choose is part of the review. Landing on the newest major gets the longest support window and the newest features. Landing on a version with a mature ecosystem may suit an extension that has not caught up. Either way the target is a supported release, and the choice is written down with the reason.

Managed cloud, Kubernetes and operator estates

The method changes with the platform, and the checks do not. On Amazon RDS and Aurora, Cloud SQL and Azure, the provider's major version upgrade is the mechanism, and our work is the readiness review, the snapshot-based rehearsal, the parameter group and extension changes, and the application tests. On Kubernetes, the operator decides how a major upgrade runs, so we read its documentation for your version before planning, and rehearse on a copy of the persistent volumes.

Patroni and repmgr clusters need a plan for the order in which nodes are stopped, upgraded and resynced, and for what the old replicas do while the primary changes. Logical replication subscribers and Debezium connectors depend on replication slots, which only survive pg_upgrade in specific configurations. We list each slot and consumer and decide whether it is carried across or recreated.

Who this upgrade service is not for

• A minor version update — Minor releases do not need pg_upgrade or a project. Apply them on your normal patch cycle, or have them handled under support or managed operation.
• A small database with a free window — If the data fits in a dump and restore inside an acceptable freeze, your own DBAs can run it with the documentation. A review is still cheap insurance, but not a project.
• A move to another engine — Oracle, SQL Server or MySQL to PostgreSQL is a migration with its own tooling, and is covered separately.
• Staying on an unsupported major — This service moves clusters to supported releases. It is not a plan for running 13 or 14 as they are.
• A fixed price and date before the review — The cluster, its extensions and its surroundings set the effort. We scope after the readiness review, and quotes come back within 1 hour of getting your versions, sizes and platform.