[FREE] Your Next Major Upgrade Doesn't Have to Be a Sev-1
What the PostgreSQL manual actually says about version upgrades (why most DBAs learn it the hard way)
Why I Wrote This
I’ve lost count of how many Sev-1 incidents I’ve been part of because:
→ Teams were new to PostgreSQL
→ Nobody read what the manual actually recommends
→ Someone followed a blog article and hit a dead end
Major upgrades are scheduled with downtime windows. Application teams are standing by. Everyone expects it to just work.
Then it doesn’t.
The pressure hits everyone. The clock is ticking. The rollback plan suddenly looks very appealing.
The goal of this article: Educate you on what PostgreSQL documentation actually recommends so you can choose the right method for your database size and avoid your next Sev-1 moment.
Table of Contents
Understanding Version Numbers (Part most DBAs get wrong)
Why Major Upgrades Are Different
Method 1: pg_dumpall: Traditional Way
Method 2: pg_upgrade: Fast Way
Method 3: Logical Replication: Near-Zero Downtime
What to Watch For in Major Upgrades
Choosing the Right Method
Key Takeaways
References
🚀 Get the PostgreSQL Health Report: single SQL function that runs 60+ diagnostic checks including TXID wraparound detection, autovacuum health, much more and ready-to-run fixes.
Upgrade Question Every DBA Faces
You’re running PostgreSQL 15 and new version is out.
Your team wants the new features. Security patches are piling up. The question isn’t whether to upgrade it’s how.
The PostgreSQL manual dedicates an entire section to this.
Most DBAs skim it once and then search Stack Overflow. But the manual contains critical details that determine whether your upgrade takes minutes or hours.
Let’s break down what it actually says.
First: Understand Version Numbers
The manual starts with something many DBAs get wrong:
Current PostgreSQL version numbers consist of a major and a minor version number. For example, in the version number 10.1, the 10 is the major version number and the 1 is the minor version number.
For versions before 10, it was different:
For releases before PostgreSQL version 10.0, version numbers consist of three numbers, for example, 9.5.3. In those cases, the major version consists of the first two digit groups of the version number, e.g., 9.5.
Current PostgreSQL version numbers consist of a major and a minor version number. For example, in the version number 10.1, the 10 is the major version number and the 1 is the minor version number.
Why This Matters for Upgrades
The manual makes a critical distinction:
Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number.
Translation:
→ 16.1 to 16.4 — Just replace binaries and restart. Done.
→ 15.x to 16.x — Major upgrade. Storage format changed. Requires migration.
For minor upgrades the manual says:
To update between compatible versions, you simply replace the executables while the server is down and restart the server. The data directory remains unchanged minor upgrades are that simple.
That’s it. Stop the server, replace binaries, start the server. No data migration needed.
Why Major Upgrades Are Different
For major versions the manual warns:
For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades.
We have three methods, each with different trade-offs.
Method 1: pg_dumpall (Traditional Way)
This the traditional method for moving data to a new major version.
How It works:
One upgrade method is to dump data from one major version of PostgreSQL and restore it in another to do this, you must use a logical backup tool like pg_dumpall; file system level backup methods will not work.
The manual provides step-by-step instructions:
Step 1. Stop writes to the database
If making a backup, make sure that your database is not being updated. This does not affect the integrity of the backup, but the changed data would of course not be included.
Step 2. Create the dump
pg_dumpall > outputfile
The manual recommends:
It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of enhancements that might have been made in these programs.
Step 3. Stop the old server
pg_ctl stopStep 4. Install new version and initialize
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/dataStep 5. Restore configuration
Restore your previous pg_hba.conf and any postgresql.conf modifications.
Step 6. Start new server and restore
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
/usr/local/pgsql/bin/psql -d postgres -f outputfileReducing Downtime with Parallel Installation
The manual offers a pro tip:
The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports.
pg_dumpall -p 5432 | psql -d postgres -p 5433This pipes the dump directly into the new server without writing to disk first.
When to Use pg_dumpall
→ Small to medium databases
→ When you need to restructure or clean data
→ When other methods fail
→ Cross-platform migrations
Downside
Slow. Very slow for large databases. The entire database must be dumped and restored, which can take hours or days for multi-terabyte databases.
Method 2: pg_upgrade (Fast Way)
The manual describes this as the faster alternative:
A faster method is pg_upgrade.
How It works:
The pg_upgrade module allows an installation to be migrated in-place from one major PostgreSQL version to another.
Key advantage:
Upgrades can be performed in minutes, particularly with --link mode.
Link Mode Explained
With --link, pg_upgrade creates hard links to the old data files instead of copying them. This is dramatically faster but means you cannot revert to the old installation easily.
What the Manual Says About Steps
It requires steps similar to pg_dumpall above, e.g., starting/stopping the server, running initdb.
The general flow:
→ Install new PostgreSQL version (keep old one)
→ Stop both servers
→ Run pg_upgrade pointing to old and new data directories
→ Start new server
→ Run recommended vacuumdb commands
When to Use pg_upgrade
→ Large databases where dump/restore takes too long
→ When you need minimal downtime
→ Same platform, same architecture
→ When data files are healthy
Downside
→ Requires both versions installed simultaneously
→ Link mode prevents easy rollback
→ Some edge cases require falling back to pg_dumpall
Method 3: Logical Replication (Near-Zero Downtime)
The manual describes the most sophisticated approach:
It is also possible to use logical replication methods to create a standby server with the updated version of PostgreSQL.
Key Insight
This is possible because logical replication supports replication between different major versions of PostgreSQL.
How It works
The standby can be on the same computer or a different computer. Once it has synced up with the primary server (running the older version of PostgreSQL), you can switch primaries and make the standby the primary and shut down the older database instance.
Downtime
Such a switch-over results in only several seconds of downtime for an upgrade.
Seconds. Not hours. Not minutes. Seconds.
Options Available
This method of upgrading can be performed using the built-in logical replication facilities as well as using external logical replication systems such as pglogical, Slony, Londiste, and Bucardo.
When to Use Logical Replication
→ Production systems requiring near-zero downtime
→ Very large databases
→ When you need a fallback option
→ Gradual migration with testing
Downside
→ More complex setup
→ Requires understanding of logical replication
→ Some data types and features may not replicate
→ Need additional infrastructure during migration
What to Watch For in Major Upgrades
The manual warns about compatibility:
New major versions also typically introduce some user-visible incompatibilities, so application programming changes might be required.
Where to Find Breaking Changes
All user-visible changes are listed in the release notes; pay particular attention to the section labeled Migration.
Categories of Changes
The manual lists what might change:
Administration: The capabilities available for administrators to monitor and control the server often change and improve in each major release.
SQL: Typically this includes new SQL command capabilities and not changes in behavior, unless specifically mentioned in the release notes.
Library API: Typically libraries like libpq only add new functionality, again unless mentioned in the release notes.
System Catalogs: System catalog changes usually only affect database management tools.
Server C-language API: This involves changes in the backend function API, which is written in the C programming language.
Testing Recommendation
Cautious users will want to test their client applications on the new version before switching over fully; therefore, it’s often a good idea to set up concurrent installations of old and new versions.
Choosing the Right Method
Based on database size and downtime tolerance:
Small database with maintenance window available
→ Use pg_dumpall
→ Simplest approach, full data validation
Medium to large database, hours of downtime acceptable
→ Use pg_upgrade with --link
→ Minutes instead of hours, but limited rollback
Production database requiring minimal downtime
→ Use logical replication
→ Seconds of downtime, but complex setup
Enterprise database with zero tolerance for failure
→ Use logical replication with extensive testing
→ Maintain parallel systems until confident
Key Takeaways
From the PostgreSQL manual:
1. Minor upgrades are simple: Replace binaries and restart
2. Major upgrades require data migration: Internal storage format changes
3. pg_dumpall is the traditional method: Slow but reliable
4. pg_upgrade is faster: Minutes with --link mode
5. Logical replication offers near-zero downtime: Seconds to switch over
6. Always read the release notes: Check the Migration section
7. Test with parallel installations: Don’t upgrade production blind
References
The manual sections referenced in this article:
PostgreSQL Documentation: Upgrading a PostgreSQL Cluster
📧 Want the deep-dive playbooks?
Paid subscribers get complete Sev-1 incident guides with production SQL queries for memory diagnostics, OOM risk assessment, and step-by-step recovery procedures.
Next week will share more diagnostic Queries for Paid Audience.
🚀 Get the PostgreSQL Health Report: a single SQL function that runs 60+ diagnostic checks including TXID wraparound detection, autovacuum health, and ready-to-run fixes..