How to Upgrade PostgreSQL to a New Major Version: `pg_upgrade` vs. Dump and Restore

When you upgrade a PostgreSQL server from one major version to another, such as PostgreSQL 17 to 18, you must install the new server software and migrate the database data. A major release can change PostgreSQL’s internal data format, so the new server cannot simply open the old data directory. PostgreSQL offers two common ways to migrate data. pg_upgrade reuses existing data files where possible. Dump and restore exports database contents and loads them into a new cluster.

In PostgreSQL, a cluster is the set of databases managed by one server installation, along with shared objects such as roles. pg_upgrade is generally faster when the cluster meets compatibility requirements. Dump and restore takes longer for large databases, but gives you more flexibility to move data between machines or select and reorganize objects. Choose a method based on your extensions, storage setup, data volume, downtime limit, and rollback plan.

How the two upgrade methods work

With dump and restore, PostgreSQL’s pg_dump tool exports one database’s definitions and data in a format that describes database objects and their contents. You create a separate cluster with the new PostgreSQL version, then load the export there. This export describes database objects and their contents instead of copying PostgreSQL’s internal data files. For a cluster-wide migration, you also need shared objects such as roles and tablespaces. pg_dumpall can export those objects along with database contents.

pg_upgrade creates a new cluster and checks whether the old and new clusters meet PostgreSQL’s compatibility requirements. It then reuses or transfers the files that hold user data, rather than exporting and reloading all the data. pg_upgrade also rebuilds or updates PostgreSQL’s internal metadata as needed. The PostgreSQL 18 pg_upgrade documentation describes upgrades from PostgreSQL 9.2 and later to that release; check the target version’s documentation for its supported source versions and limitations.

The two methods take different amounts of time and storage. Dump and restore rebuilds data and indexes, so large databases can take substantial time to load. pg_upgrade generally avoids that work, but it depends on compatible builds and available extensions. Neither method has a universal runtime or downtime estimate. Test the upgrade with data similar to yours to measure how long it takes.

When pg_upgrade fits

Choose pg_upgrade when you want to move a large, compatible cluster without reloading all user data. Before scheduling downtime, inventory the source and target versions, cluster and tablespace locations, and build settings. Also list the extensions and custom modules. PostgreSQL can check some compatibility conditions, but it cannot verify every external module’s binary compatibility. Confirm that each extension supports the target PostgreSQL release and that the shared library files containing its compiled code for that release are installed.

The way pg_upgrade handles data files affects both speed and recovery. In copy mode, pg_upgrade copies files, which takes more time and storage but leaves the old cluster’s files separate. In link mode, it creates hard links, which are directory entries that point to the same underlying files as the old cluster. That saves copying time and space, but the old cluster becomes unsafe to use after the new cluster starts writing to the linked files. Link mode also requires both data directories to be on the same file system. Clone mode offers similar speed and space savings while preserving the old files. It works only with supported operating-system and file-system combinations.

Use the new version’s pg_upgrade --check before the outage. Include the mode you intend to use so the check covers mode-specific requirements. Then block application access and stop the old server. Run the upgrade, followed by any follow-up scripts that pg_upgrade generates. The scripts rebuild indexes or do other required work. Refresh and verify statistics as directed by the target version’s documentation, then test extensions, permissions, replication, application behavior, and important queries before removing the old cluster.

When dump and restore fits

Choose dump and restore to move between machine architectures, migrate only selected objects, or reorganize the database as you change versions. PostgreSQL documents logical dump and restore as a way to migrate across architectures. Some archive formats let you inspect an export, restore selected objects, or change the order in which PostgreSQL restores them. The pg_dump documentation describes those options.

Plan the export around the migration’s scope. pg_dump handles one database, so a full-cluster move also needs global objects such as roles and tablespaces. You can export these separately or use pg_dumpall; restoring a full-cluster script requires appropriate privileges. Confirm that roles, tablespace directories, ownership, and privileges will work in the destination cluster.

A dump represents a consistent snapshot from when the export begins. It does not include writes committed afterward. If the source remains writable during a long export and restore, the destination will be behind by those later changes. To avoid losing them at cutover, freeze writes for the final migration window or use a separately planned replication or change-capture method. For large databases, custom or directory archives support parallel restore. Directory format also supports parallel dumping. Parallel work can reduce the total time, but it uses more connections and puts more load on the server. After the restore, check for errors and run ANALYZE where needed. This gives PostgreSQL information it needs to plan queries. The SQL dump documentation covers dump and restore behavior.

How to choose and plan the cutover

Use pg_upgrade when the source cluster and its extensions meet the target version’s requirements and you want to reduce migration time. Choose copy mode when preserving an untouched old cluster during the upgrade matters and you have enough storage. Consider link mode only when its file-system requirements are met and your recovery plan accounts for the old cluster becoming unusable after the new cluster writes to shared files.

Use dump and restore when portability, selective migration, or a logical rebuild outweighs restore time. Plan how to copy to the target any changes made after the export starts. Keeping the old cluster available during preparation does not, by itself, make a later switchback safe: once applications write to the new cluster, you need a plan for those new records if you return to the old one.

In either case, review the release notes for every major version between the source and target. PostgreSQL permits skipping intervening major versions, but its versioning policy recommends reviewing each intervening release. Test your application with the new version, then rehearse the migration method, settings, and validation steps you plan to use in production. Managed database providers and software distributions can supply their own upgrade procedures, so follow those instructions where they apply.

If you need a very short cutover, consider logical replication. Logical replication sends table changes to a server running the new version before the switch. In the documented PostgreSQL scenario, this reduces the time needed for the final switch. It requires separate handling for schema changes and sequence state because logical replication does not replicate them in the same way as table data.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com