PostgreSQL Upgrade FAQ: Compatibility, Downtime, Rollback

This article answers the most common questions about upgrading PostgreSQL: what breaks, how much downtime to expect, whether you can roll back, and how long each major version is supported. In short, a typical in-place major upgrade requires a short downtime (often under a minute with modern tools), cannot be rolled back once the data directory is switched, and each major version is supported for five years.

Why Upgrade at All?

PostgreSQL releases a new major version roughly once a year, and each one brings performance improvements, new features, and security fixes. Staying on an old version means missing out on those benefits, and eventually you will be outside the support window, which leaves you without bug fixes or security patches. For example, PostgreSQL 12 was released in 2019 and its final minor update came in 2024, so anyone still running it today is on an unsupported version. Upgrading is not just about new toys; it is about staying safe and keeping your database healthy.

What Does “Major Version” Mean?

PostgreSQL uses a two-part version number, like 16.4. The first number (16) is the major version, and the second (4) is the minor version. Major versions add new features and can change internal storage formats, which is why they require a special upgrade procedure. Minor versions are bug fixes and security patches that are designed to be drop-in replacements—you can usually just install the new package and restart. The PostgreSQL Global Development Group supports each major version for five years from its initial release, with minor releases coming out roughly every quarter.

Compatibility: What Breaks and What Doesn’t?

The good news is that PostgreSQL is very stable across major versions. Your SQL queries, indexes, and most extensions will keep working. The data itself is stored in a format that is forward-compatible, meaning a new version can read data written by an older one. However, there are a few things to check before you upgrade:

• Extensions: Some extensions (like PostGIS or pg_trgm) may need to be updated or recompiled to work with the new version. Check the extension’s documentation for compatibility.
• Configuration: Some default settings change between major versions. For example, PostgreSQL 16 changed the default for log_destination to include jsonlog. Review your postgresql.conf for any removed or changed parameters.
• Deprecated features: A few old features are removed after a long deprecation period. For instance, the hstore extension’s => operator was removed in PostgreSQL 16. Check the release notes for a list of removed items.
• Client applications: Older client libraries usually work with newer servers, but the reverse is not always true. If you have very old clients, they might not be able to connect to a new server.

The safest approach is to run the pg_upgrade --check command before you actually upgrade. It will scan your cluster and report any incompatible extensions, settings, or other issues without changing anything.

Downtime: How Long Will It Take?

This is the most common concern, and the answer depends on the method you choose. There are two main ways to upgrade:

• In-place upgrade (pg_upgrade): This is the fastest method. It shuts down the old server, runs pg_upgrade to rewrite the data files (if needed), and starts the new server. The downtime is typically just a few minutes, and for many small databases it can be under a minute. The actual time depends on the size of your database and the speed of your disk.
• Logical replication: This method sets up a new server running the new version, then uses logical replication to copy changes from the old server to the new one in real time. When you are ready to switch, you just point your applications at the new server. Downtime can be as low as a few seconds, but it requires more setup and careful monitoring.

For most people, pg_upgrade is the right choice. It is simple, fast, and well-tested. The downtime is usually short enough to schedule during a maintenance window without complaint.

Rollback: Can You Go Back?

Here is the hard truth: you cannot roll back a major version upgrade. Once you have run pg_upgrade and started the new server, the data directory is modified to be incompatible with the old version. There is no built-in “undo” command. If you need to revert, you must restore from a backup taken before the upgrade. This is why a full backup is absolutely mandatory before any major upgrade.

If you use logical replication, you can keep the old server running until you are confident the new one is stable. That gives you a natural rollback path: just point your applications back at the old server. But once you stop the old server and delete it, you are committed.

How to Choose the Right Upgrade Method

Here is a simple decision guide:

• If you can afford a few minutes of downtime and want the simplest path, use pg_upgrade.
• If you need near-zero downtime (for a 24/7 service), use logical replication.
• If you are on a very old version (like 9.6 or 10), you may need to do a dump and restore because pg_upgrade only supports jumping a limited number of versions. Check the docs for the exact range.

Whichever you choose, always test the upgrade on a staging server first. This will reveal any compatibility issues without risking production data.

Common Pitfalls and How to Avoid Them

• Forgetting to back up. This is non-negotiable. Use pg_dump or a file-system-level backup, and verify that you can restore it.
• Ignoring extension updates. After upgrading, run ALTER EXTENSION ... UPDATE for each extension to get the new version.
• Not checking for removed settings. Use pg_upgrade --check to catch these before you start.
• Upgrading during peak hours. Even a short downtime can cause problems if you have a busy application. Do it during a maintenance window.
• Assuming your application is compatible. Test your application against the new version in a staging environment. Pay special attention to any queries that use deprecated features.

Frequently Asked Questions

How long is each PostgreSQL major version supported?

Each major version is supported for five years from its initial release. After that, you will not receive any more minor releases (bug fixes or security patches). For example, PostgreSQL 14 was released in September 2021, so it will be supported until September 2026.

Can I skip a major version when upgrading?

Yes, you can skip versions. The pg_upgrade tool supports upgrading from any version that is at least two versions old. For example, you can go directly from 12 to 16. However, if you are on a version older than that, you will need to do a dump and restore or upgrade in steps.

Will my indexes need to be rebuilt after an upgrade?

Usually not. pg_upgrade preserves indexes as much as possible. However, if the new version changes the format of a particular index type (which is rare), you may need to rebuild it. The upgrade process will tell you if that is necessary.

Can I use logical replication to upgrade without downtime?

Yes, logical replication is the standard way to achieve near-zero downtime upgrades. You set up a new server with the new version, subscribe it to the old server, let it catch up, then switch your applications. The main caveat is that logical replication does not copy all data types and sequences; you need to handle those manually.

What is the difference between a major and a minor upgrade?

A major upgrade changes the first number (e.g., 16 to 17) and can change internal storage formats, requiring a special upgrade procedure. A minor upgrade changes the second number (e.g., 16.4 to 16.5) and only contains bug fixes and security patches. Minor upgrades are drop-in replacements—you just install the new package and restart.

Is it safe to upgrade to the newest version right after it is released?

It is generally safe, but many organizations wait a few weeks or months to let others find any edge-case bugs. The PostgreSQL community is very good at fixing issues quickly, but if you are risk-averse, wait for the first minor release (e.g., 17.1) before upgrading production.

Final Thoughts

Upgrading PostgreSQL is a routine task that is well-supported by the community. The key is to plan ahead: back up your data, test on a staging server, and choose the method that fits your downtime tolerance. With pg_upgrade and logical replication, you have solid tools to make the process smooth. Just remember that there is no rollback button, so always have a backup you trust.

Related

• FFXIV Job Selector: Solo & Group Play Guide
• Digital vs Paper Exams: Accessibility, Reliability, Cost
• Star Wars Zero Company Weapons Tier List