PostgreSQL provides pg_upgrade to upgrade an existing database cluster to a newer major version without requiring a logical dump and restore.
In this article, I’ll demonstrate how to upgrade PostgreSQL 15 to PostgreSQL 18 using pg_upgrade with the — link option.
Unlike a traditional dump-and-restore approach, link mode allows PostgreSQL to reuse the existing relation files through hard links, significantly reducing the time and disk space required for an upgrade.
1. Checking the Existing PostgreSQL 15 Environment
First, let’s check the existing PostgreSQL installation and its configuration.
I’m using the following environment variables and version:
[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-15 [postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/15/data [postgres@OEL98-PG ~]$ export PGPORT=5415 postgres=# select version(); version ------------------------------------------------------------------------------------------------------------ PostgreSQL 15.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit (1 row) Check the current data directory and databases:
SHOW data_directory; data_directory ------------------------ /var/lib/pgsql/15/data postgres=# \l List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges -----------+----------+----------+-----------------+-------------+-------------+--------+-----------+----------------------- postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | postgres=CTc/postgres+ | | | | | | | | =c/postgres vahiddb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | (4 rows) Before upgrading, let’s identify the physical location of a table. I’ll use mazandaran.tbl_person as an example.
vahiddb=# SELECT c.relname, pg_relation_filepath(c.oid) AS relative_path, current_setting('data_directory') || '/' || pg_relation_filepath(c.oid) AS absolute_path FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'mazandaran' AND c.relkind IN ('r','p') ORDER BY c.relname; relname | relative_path | absolute_path ------------+------------------+----------------------------------------- tbl_person | base/16386/16389 | /var/lib/pgsql/15/data/base/16386/16389 (1 row) vahiddb=# select * from MAZANDARAN.tbl_person; id | fname | lname | sal ----+---------+-------------+------ 1 | Vahid | Yousefzadeh | 5.00 2 | Nader | DastNeshan | 6.00 3 | Kourosh | Barmak | 6.00 (3 rows) Finally, use the Linux stat command to record the inode number, link count of the table’s physical file.
[postgres@OEL98-PG ~]$ stat -c 'Inode=%i Links=%h File=%n' \ > /var/lib/pgsql/15/data/base/16386/16389 Inode=973746 Links=1 File=/var/lib/pgsql/15/data/base/16386/16389 We’ll compare these values after the upgrade.
2. Checking the PostgreSQL 18 Installation
PostgreSQL 18 is already installed on the server. Let’s configure the environment variables for the new installation and check the new PostgreSQL version:
[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-18 [postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/18/data [postgres@OEL98-PG ~]$ export PGPORT=5418 [postgres@OEL98-PG ~]$ psql psql (19beta4, server 18.6) Type "help" for help. postgres=# select version(); version ------------------------------------------------------------------ PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit (1 row) Check the new data directory:
postgres=# show data_directory; data_directory ------------------------ /var/lib/pgsql/18/data (1 row) 3. Stopping Both PostgreSQL Instances
Before running pg_upgrade, both PostgreSQL instances must be stopped.
I use two shell scripts, pg15.sh and pg18.sh, to switch between the environments.
Stop PostgreSQL 15:
[postgres@OEL98-PG ~]$ source pg15.sh [postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/15/data -l logfile stop waiting for server to shut down.... done server stopped Stop PostgreSQL 18:
[postgres@OEL98-PG ~]$ source pg18.sh [postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/18/data -l logfile stop waiting for server to shut down.... done server stopped 4. Checking Upgrade Compatibility
Before performing the actual upgrade, it’s a good idea to run pg_upgrade with the — check option.
This checks whether the existing cluster is compatible with the new PostgreSQL installation without performing the upgrade.
Run:
[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/pg_upgrade \ --old-datadir=/var/lib/pgsql/15/data \ --new-datadir=/var/lib/pgsql/18/data \ --old-bindir=/usr/pgsql-15/bin \ --new-bindir=/usr/pgsql-18/bin \ --check Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database connection settings ok Checking database user is the install user 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* The compatibility check completed successfully.
5. Performing the Upgrade Using Link Mode
Now we can perform the actual upgrade.
The important option in this command is — link.
[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/pg_upgrade \ --old-datadir=/var/lib/pgsql/15/data \ --new-datadir=/var/lib/pgsql/18/data \ --old-bindir=/usr/pgsql-15/bin \ --new-bindir=/usr/pgsql-18/bin \ --link Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database connection settings ok Checking database user is the install user 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 Adding ".old" suffix to old "global/pg_control" ok If you want to start the old cluster, you will need to remove the ".old" suffix from "/var/lib/pgsql/15/data/global/pg_control.old". Because "link" mode was used, the old cluster cannot be safely started once the new cluster has been started. Linking 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 ---------------- Some statistics are not transferred by pg_upgrade. Once you start the new server, consider running these two commands: /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only /usr/pgsql-18/bin/vacuumdb --all --analyze-only Running this script will delete the old cluster's data files: ./delete_old_cluster.sh Important: What does — link actually do?
The — link option uses hard links instead of copying the user relation files into the new cluster.
This means the old and new cluster can reference the same underlying physical file through different directory entries.
Consequently:
The upgrade can be significantly faster than copying all relation files.
It requires less additional disk space.
Both directory entries initially reference the same underlying file.
Once the new PostgreSQL cluster is started, the old cluster must not be started again.
PostgreSQL explicitly warns about this in the upgrade output:
Because "link" mode was used, the old cluster cannot be safely started once the new cluster has been started. Important: Make sure you have a valid backup before performing this operation. With link mode, you should treat the old cluster as unusable once the new cluster has been started.
6. Starting PostgreSQL 18
After the upgrade finishes, start the new PostgreSQL instance:
[postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/18/data -l logfile start waiting for server to start.... done server started 7. Updating Optimizer Statistics
The pg_upgrade output recommends updating optimizer statistics:
[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "vahiddb": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets) vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets) vacuumdb: processing database "vahiddb": Generating medium optimizer statistics (10 targets) vacuumdb: processing database "postgres": Generating default (full) optimizer statistics vacuumdb: processing database "template1": Generating default (full) optimizer statistics vacuumdb: processing database "vahiddb": Generating default (full) optimizer statistics [postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/vacuumdb --all --analyze-only vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "template1" vacuumdb: vacuuming database "vahiddb" This performs a full analysis of the databases without performing vacuum operations.
8. Removing the Old PostgreSQL Cluster
After verifying that the new cluster is working correctly, you can remove the old cluster’s data files.
pg_upgrade automatically creates a cleanup script:
[postgres@OEL98-PG ~]$ ./delete_old_cluster.sh [postgres@OEL98-PG ~]$ ls -l /var/lib/pgsql/15/* total 0 9. Verifying the PostgreSQL 18 Upgrade
Now let’s verify the new PostgreSQL installation.
Check the version:
postgres=# select version(); version ------------------------------------------------------------------------ PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit (1 row) Check the data directory:
[postgres@OEL98-PG ~]$ psql psql (18.6) Type "help" for help. postgres=# show data_directory; data_directory ------------------------ /var/lib/pgsql/18/data (1 row) List the databases:
postgres=# \l List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges -----------+----------+----------+-----------------+-------------+-------------+--------+-----------+----------------------- postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | postgres=CTc/postgres+ | | | | | | | | =c/postgres vahiddb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | (4 rows) Check the table contents:
postgres=# \c vahiddb You are now connected to database "vahiddb" as user "postgres". vahiddb=# select * from mazandaran.tbl_person; id | fname | lname | sal ----+---------+-------------+------ 1 | Vahid | Yousefzadeh | 5.00 2 | Nader | DastNeshan | 6.00 3 | Kourosh | Barmak | 6.00 (3 rows) - Comparing Physical Relation Files Before and After the Upgrade Finally, let’s repeat the physical file location query.
vahiddb=# SELECT c.relname, pg_relation_filepath(c.oid) AS relative_path, current_setting('data_directory') || '/' || pg_relation_filepath(c.oid) AS absolute_path FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'mazandaran' AND c.relkind IN ('r','p') ORDER BY c.relname; relname | relative_path | absolute_path ------------+------------------+----------------------------------------- tbl_person | base/16386/16389 | /var/lib/pgsql/18/data/base/16386/16389 (1 row) Notice that the relative path is still the same:
base/16386/16389 The absolute path has changed because the new PostgreSQL cluster uses a different data directory.
We can also check the inode number again:
[postgres@OEL98-PG ~]$ stat -c 'Inode=%i Links=%h File=%n' \ /var/lib/pgsql/18/data/base/16386/16389 Inode=973746 Links=1 File=/var/lib/pgsql/18/data/base/16386/16389 The inode number is the same as before the upgrade. This is an important observation when investigating how pg_upgrade — link handles physical relation files.
