Which PostgreSQL Upgrade Option Should You Choose?

When you’re upgrading your PostgreSQL database, you have two primary methods for handling the process. Properly preparing for your upgrade and selecting the right choice for your organization depends on fully understanding the available options.

Assessing Your PostgreSQL Upgrade Readiness

You don’t want to jump right into your upgrade, as that can lead to a major disruption of your daily business operations. Careful planning is the most important step, especially when you’re working with mission-critical systems.

You’ll want full visibility into your current PostgreSQL environment, as well as any issues that could cause problems with an upgrade. Some of the factors to pay the most attention to include your current PostgreSQL extensions, the version compatibility parameters between both databases, Reg* data types, and any unknown data types present.

Once you establish the PostgreSQL baseline, you want to create a timeline that offers sufficient time to fully migrate your organization’s data, make any changes to your applications for compatibility purposes, and go through a complete testing cycle to catch any problems before they make it to production.

Establishing this schedule depends on the IT resources available to manage the upgrade, database sizes, the maintenance windows scheduled, the amount of storage you have available, and the scope of any compatibility changes.

Once you’ve reached the point in planning where you’ve answered all of these questions, you’re able to start exploring which PostgreSQL upgrade option makes the most sense for your organization. You may need to tweak your plan depending on the one you select, but that’s a minor change that’s easy to handle. Trying to pick the process and then do all the planning can lead to a lack of efficiency if you have a mismatch.

Pg_dumpall Upgrade Process

PostgreSQL has a logical backup tool called pg_dumpall. This tool will dump all of the data contained on your databases to disk. You then reload it to the target PostgreSQL version to complete the upgrade.

This upgrade method excels when you’re working with smaller databases. The downtime is minimal when you don’t have a lot of data to dump, so you’re up and running on the new version quickly. This method also eliminates database fragmentation during the upgrade, which shrinks the table and index sizes. If you had a database get out of control with its storage space, this is one way to quickly handle it. You’re also able to set up upgrades where the source and target database are on different servers. Setting up upgrades on distributed servers or new hardware is simple with this utility.

One of the biggest problems with this upgrade option is that you have to either shut down the application completely or keep it in single-user mode during the dump process. You don’t want any writes on the source database while you’re upgrading, and this is the way to avoid that.

When you have applications that must be accessible at all times, taking them down for the dump process may be logistically challenging. You also need additional disk space for the new cluster and the dumped data.

A quick overview of the pg_dumpall upgrade process is:

1. Install PostgreSQL v13 binaries.
2. Install PostgreSQL extensions.
3. Initialize the PostgreSQL cluster.
4. Perform pg_dumpall/pg_restore.
5. Validate the data and objects in the database.

Pg_upgrade Upgrade Process

You can upgrade in-place when you use pg_upgrade. You get two methods for making this happen: copy mode and link mode. Your database can upgrade in seconds when you use link mode, while copy mode depends on how big the database is. It creates a copy of PGDATA from the source to the target.

Upgrading in place is logistically easier than moving between servers. It’s also far faster to upgrade big databases when you don’t need to move all that data around. When you use the link method, you’re also able to use the same storage for more optimization.

You can only perform this upgrade on the same server, and you do need to account for the downtime.

A quick overview of the pg_upgrade process is:

1. Install PostgreSQL 13 binaries.
2. Install the PostgreSQL cluster.
3. Install PostgreSQL extensions.
4. Execute pg_upgrade with -c option for a consistency check.
5. Execute pg_upgrade and review your logs.
6. Validate your data and objects.

Learn more about the upgrade process to PostgreSQL 13 in our white paper, “You Can’t Put Off a PostgreSQL v9.5 Upgrade Anymore – End of Life is Here” and contact us if you want to leverage our talented database specialists for your upgrades from start to finish.