Upgrading PostgreSQL to the latest version is essential for accessing new features, performance improvements, security patches, and long-term community support. PostgreSQL 17 introduces several enhancements over version 16, making it a valuable upgrade for developers and database administrators alike. One of the most reliable and efficient ways to perform this upgrade is using the pg_upgrade utility, which allows for fast, in-place upgrades with minimal downtime. In this guide, we'll walk through a tested step-by-step process to upgrade a PostgreSQL 16 cluster to version 17 using pg_upgrade, with both versions installed from source and running on separate ports for isolation.
Why Should You Upgrade PostgreSQL to the Latest Version?
- Security Fixes: New versions patch known vulnerabilities and improve system hardening.
- Performance Enhancements: Each release brings better indexing, faster queries, and improved concurrency.
- New Features: Get access to advanced PostgreSQL features, new data types, and better developer tools.
- Bug Fixes: Long-standing or subtle bugs in older versions are often fixed in newer releases.
- Community & Tooling Support: Extensions, ORMs, and admin tools usually support only the latest few major versions.
- Compliance Requirements: Regulatory environments may require up-to-date, vendor-supported software.
- End-of-Life Avoidance: Old versions eventually stop receiving updates, leaving you unsupported and at risk.
Purpose of Using pg_upgrade
- Minimal Downtime: Unlike dump/restore, pg_upgrade completes in minutes—even for large databases.
- No Data Export/Import Needed: It reuses your existing data files and directory structure.
- Preserves Roles and Configurations: Keeps your users, access controls, and most settings intact.
- Safe and Reversible: Pre-upgrade checks ensure compatibility before making any changes.
- If: Ideal for production systems where downtime and data movement are expensive.
- Optional Post-Upgrade Cleanup: You get a script to safely delete the old cluster after verifying success.
Step 1: Set up PostgreSQL 16 (Source Cluster)
We begin by compiling and setting up PostgreSQL 16.
Install postgres from source code
Mkdir postgres_16_code
cd postgres_16_code/
git clone https://git.postgresql.org/git/postgresql.git -b REL_16_STABLE
cd postgresql/
./configure --prefix=$(pwd) --enable-cassert --enable-debug
make
sudo make install
bin/initdb data_16
# Initialize and start the cluster on port 5433:
bin/pg_ctl -D data_16 -l logfile start -o '-p 5433'
Confirm PostgreSQL 16 is running:
postgres=# show port;
------
5433
(1 row)
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
Create a sample database and table for testing:
CREATE DATABASE test_16;
\c test_16;
CREATE TABLE test_16_1(id serial primary key, name text);
Stop the cluster:
bin/pg_ctl -D data_16 -l logfile stop -o '-p 5433'
Step 2: Setup PostgreSQL 17 (Target Cluster)
Compile and install PostgreSQL 17 from source
mkdir postgres_17_code
cd postgres_17_code/
git clone https://git.postgresql.org/git/postgresql.git -b REL_17_STABLE
cd postgresql/
./configure --prefix=$(pwd) --enable-cassert --enable-debug
make
sudo make install
Initialize the data directory for PostgreSQL 17:
bin/initdb -D data_17
Start PostgreSQL 17 on port 5434:
bin/pg_ctl -D data_17 -l logfile start -o '-p 5434'
Check the version and port:
postgres=# show port;
5434
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
Stop the cluster before upgrade:
bin/pg_ctl -D data_17 -l logfile stop -o '-p 5434'
Step 3: Pre-Upgrade Check with pg_upgrade
Run pg_upgrade in check mode to validate compatibility:
bin/pg_upgrade \
--old-datadir=/home/user/postgres_16_code/postgresql/data_16 \
--new-datadir=/home/user/postgres_17_code/postgresql/data_17 \
--old-bindir=/home/user/postgres_16_code/postgresql/bin \
--new-bindir=/home/user/postgres_17_code/postgresql/bin \
--check
You should see a info message like this
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for not-null constraint inconsistencies ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
Step 4: Perform the Upgrade
Once compatibility is confirmed, run the actual upgrade:
bin/pg_upgrade \
--old-datadir=/home/user/postgres_16_code/postgresql/data_16 \
--new-datadir=/home/user/postgres_17_code/postgresql/data_17 \
--old-bindir=/home/user/postgres_16_code/postgresql/bin \
--new-bindir=/home/user/postgres_17_code/postgresql/bin
After the upgrade completes, you will see a success message and a script suggestion like this
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for not-null constraint inconsistencies ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/home/cybrosys/postgres_17_code/postgresql/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
Step 5: Start PostgreSQL 17 and Verify Data
Start the upgraded PostgreSQL 17 cluster:
bin/pg_ctl -D data_17 -l logfile start -o '-p 5434'
Connect and verify that your data is intact:
\c test_16
\d
Expected Output:
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | test_16_1 | table | cybrosys
public | test_16_1_id_seq | sequence | cybrosys
Conclusion
Upgrading from PostgreSQL 16 to 17 using pg_upgrade offers a reliable, high-performance path with minimal downtime and zero need for time-consuming dump-and-restore operations. By reusing your existing data files and system catalog structures, pg_upgrade ensures a fast transition while preserving your data integrity.
However, successful upgrades depend on careful preparation: test the process in a staging environment, ensure your extensions and custom configurations are compatible, and verify your cluster health before and after the upgrade.