DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Upgrading PostgreSQL 15 to 18 Using pg_upgrade

#postgres #dba #database #devops

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)
Enter fullscreen mode Exit fullscreen mode

Check the current data directory and databases:

SHOW data_directory;
     data_directory
------------------------
 /var/lib/pgsql/15/data
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode 
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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Check the new data directory:

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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*
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
[postgres@OEL98-PG ~]$ /usr/pgsql-18/bin/vacuumdb --all --analyze-only
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "vahiddb"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  1. 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)
Enter fullscreen mode Exit fullscreen mode

Notice that the relative path is still the same:

base/16386/16389
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

This is an important observation when investigating how pg_upgrade — link handles physical relation files.

Top comments (0)