How to upgrade Postgresql from version 15 to version 16 in Fedora , RHEL, CentOS, Rocky, Alma Linux

Stop PostgreSQL 15

Stop your existing PostgreSQL 15 database server, if it is running, and prevent it from starting automatically at boot in the future:

[username@yourhost ~]# sudo systemctl disable --now postgresql-15

Update your system

Make sure your packages are fully updated:

[username@yourhost ~]# sudo dnf upgrade
Last metadata expiration check: 2:28:40 ago on Thu 21 Sep 2023 09:01:38 AM MDT.
Dependencies resolved.
Nothing to do.
Complete!

If there were updates pending, apply them. If the Linux kernel, glibc, systemd, or other core packages were updated, reboot to load the latest of all parts of your system.

Install PostgreSQL 16

We will here use a basic set of PostgreSQL client, server, development, and extra contributed packages.

You may also want some of the other available packages for procedural languages such as PL/Perl and PL/Python, PostGIS, or other packages. See the Yum repository index.

Now install PostgreSQL 16, which happily coexists alongside PostgreSQL 15 (and possibly other versions) thanks to the way the PGDG RPMs are designed and where they install files:

[username@yourhost ~]# sudo dnf install postgresql16-server postgresql16-contrib
Last metadata expiration check: 2:29:14 ago on Thu 21 Sep 2023 09:01:38 AM MDT.
Dependencies resolved.
==========================================================================
 Package                  Architecture  Version         Repository   Size
==========================================================================
Installing:
 postgresql16-contrib     x86_64        16.0-1PGDG.f38  pgdg16      708 k
 postgresql16-server      x86_64        16.0-1PGDG.f38  pgdg16      5.9 M
Installing dependencies:
 postgresql16             x86_64        16.0-1PGDG.f38  pgdg16      1.5 M
 postgresql16-libs        x86_64        16.0-1PGDG.f38  pgdg16      290 k

Transaction Summary
==========================================================================
Install  4Packages
Total download size: 14 M
Installed size: 54 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): postgresql16-contrib-16.0-1PGDG.f38.x86_64.rpm  412 kB/s | 708 kB     00:01
(2/4): postgresql16-libs-16.0-1PGDG.f38.x86_64.rpm     171 kB/s | 290 kB     00:01
(3/4): postgresql16-16.0-1PGDG.f38.x86_64.rpm          435 kB/s | 1.5 MB     00:03
(4/4): postgresql16-server-16.0-1PGDG.f38.x86_64.rpm   1.7 MB/s | 5.9 MB     00:03
-----------------------------------------------------------------------------------
Total                                                  2.0 MB/s |  7 7 MB     00:06
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                             1/1
  Installing       : postgresql16-libs-16.0-1PGDG.f38.x86_64     1/4
  Running scriptlet: postgresql16-libs-16.0-1PGDG.f38.x86_64     1/4
  Installing       : postgresql16-16.0-1PGDG.f38.x86_64          2/4
  Running scriptlet: postgresql16-16.0-1PGDG.f38.x86_64          2/4
  Running scriptlet: postgresql16-server-16.0-1PGDG.f38.x86_64   3/4
  Installing       : postgresql16-server-16.0-1PGDG.f38.x86_64   3/4
  Running scriptlet: postgresql16-server-16.0-1PGDG.f38.x86_64   3/4
  Installing       : postgresql16-contrib-16.0-1PGDG.f38.x86_64  4/4
  Verifying        : postgresql16-16.0-1PGDG.f38.x86_64          1/4
  Verifying        : postgresql16-contrib-16.0-1PGDG.f38.x86_64  2/4
  Verifying        : postgresql16-libs-16.0-1PGDG.f38.x86_64     3/4
  Verifying        : postgresql16-server-16.0-1PGDG.f38.x86_64   4/4
Installed:
  postgresql16-16.0-1PGDG.f38.x86_64         postgresql16-contrib-16.0-1PGDG.f38.x86_64
  postgresql16-devel-16.0-1PGDG.f38.x86_64   postgresql16-libs-16.0-1PGDG.f38.x86_64
  postgresql16-server-16.0-1PGDG.f38.x86_64
Complete!

Create the new PostgreSQL 16 database cluster

Now that we have the new PostgreSQL version installed, we can create the new database cluster.

You need to use the same Postgres initdb options that were used when you ran initdb for Postgres 15.

For example, if you used the helpful data checksums feature, you need to pass the -k option to initdb, which is done indirectly via the RPM-specific postgresql-15-setup script like this:

[username@yourhost ~]# PGSETUP_INITDB_OPTIONS=-k sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
Initializing database ... OK

If you used the default initdb options, just omit the PGSETUP_INITDB_OPTIONS environment variable:

[username@yourhost ~]# /usr/pgsql-16/bin/postgresql-16-setup initdb
Initializing database ... OK

Migrate your data with pg_upgrade

At this point you could start the PostgreSQL 16 server and use psql to connect to it, if you want to start with an empty database or otherwise load your data by the usual means such as with psql or pg_restore using output from pg_dump.

But if you want to convert all of your existing PostgreSQL 16 cluster’s data so everything comes over as is, you can now run pg_upgrade.

For any important system, back up your data before doing anything else, and read through the whole pg_upgrade manual. It contains several important points to consider:

• Use compatible initdb flags that match the old cluster. (Already discussed.)
• Install extension shared object files. (Some may be contained in the contrib package, while others are separate, such as PostGIS.)
• Set authentication to peer in pg_hba.conf for both old and new Postgres versions. This is the default for the new cluster, but your old PostgreSQL 16 cluster may need to be adjusted so pg_update can access it.

We will run pg_upgrade as the postgres OS user and specify the standard PGDG RPM locations for Postgres 15 & 16:

[username@yourhost ~]# sudo su - postgres
[postgres@yourhost ~]$ /usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin -d /var/lib/pgsql/15/data -D /var/lib/pgsql/16/data -j 4
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
old cluster does not use data checksums but the new one does
Failure, exiting

Oops! pg_upgrade can’t convert an old cluster that didn’t have data checksums into a new one that did, so in this case we need to delete our new cluster and recreate it without the initdb -k option for checksums.

Exit back to your root shell and re-run a suitable initdb command like those shown above:

[postgres@yourhost ~]$ exit
logout
[username@yourhost ~]# rm -rf ~postgres/16/data/*
[username@yourhost ~]# /usr/pgsql-16/bin/postgresql-16-setup initdb
Initializing database ... OK

Now let’s try running pg_upgrade again:

[username@yourhost ~]# sudo su - postgres
[postgres@yourhost ~]$ /usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin -d /var/lib/pgsql/15/data -D /var/lib/pgsql/16/data -j 4
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 system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       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
------------------
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:
    /usr/pgsql-16/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

That’s better!

If this does not work too, then try the following to enable checksum

 sudo /usr/pgsql-16/bin/pg_checksums -D /var/lib/pgsql/16/data --enable --progress --verbose

or try following to disable the checksum

 sudo /usr/pgsql-15/bin/pg_checksums -D /var/lib/pgsql/15/data --disable --progress --verbose

Start PostgreSQL 16

You may want to manually migrate over any configuration in postgresql.conf and pg_hba.conf from your old PostgreSQL 15 cluster to your new PostgreSQL 16 cluster, using diff or similar tools.

Once you’re ready, exit back to your root shell, then start your new PostgreSQL 15 database and set it to start automatically at boot:

[postgres@yourhost ~]$ exit
logout
[username@yourhost ~]# sudo systemctl enable --now postgresql-16
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-16.service → /usr/lib/systemd/system/postgresql-16.service.

Let’s take the advice of pg_upgrade to analyze our freshly imported databases so the query planner has statistics it needs to plan wisely. We aren’t in any hurry to get the database back online, so we won’t bother analyzing in stages, and will just do it all at once:

[username@yourhost ~]# sudo su - postgres
[postgres@yourhost ~]$ /usr/pgsql-16/bin/vacuumdb -a -Z
vacuumdb: vacuuming database "funtimes"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

Try it out

Now we can try our new installation, and check that some of those neat new PostgreSQL 16 features are really available:

[postgres@yourhost ~]$ psql
psql (16.0)
Type "help" for help.

postgres=# select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.1 20220819 (Red Hat 12.2.1-2), 64-bit
(1 row)
postgres=# select regexp_count('the quick brown fox jumped over the lazy', ' ');
 regexp_count
--------------
            7
(1 row)
postgres=# \h merge
Command:     MERGE
Description: conditionally insert, update, or delete rows of a table
Syntax:
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
where data_source is:
{ source_table_name | ( source_query ) } [ [ AS ] source_alias ]
and when_clause is:
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
and merge_insert is:
INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
and merge_update is:
UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
and merge_delete is:
DELETE
URL: https://www.postgresql.org/docs/16/sql-merge.html

Remove PostgreSQL 15

There is likely no urgency for you to do this, but later after you’re convinced PostgreSQL 16 is working well for you, you can remove the old PostgreSQL 15 packages and data.

First we’ll run this command without agreeing, to see what the package manager plans to do:

[username@yourhost ~]# rpm -qa postgresql15\* | sudo xargs dnf erase
Dependencies resolved.
======================================================================
 Package              Architecture  Version         Repository   Size
======================================================================
Removing:
 postgresql15         x86_64        15.4-1PGDG.f38  @pgdg15     7.7 M
 postgresql15-libs    x86_64        15.4-1PGDG.f38  @pgdg15     935 k
 postgresql15-server  x86_64        15.4-1PGDG.f38  @pgdg15      24 M

Transaction Summary
======================================================================
Remove  3 Packages
Freed space: 31 M
Is this ok [y/N]: Operation aborted.

If it proposes to remove only what you want, and not any other dependencies you want to keep, you can give dnf the go-ahead with the -y (yes) option:

[username@yourhost ~]# rpm -qa postgresql15\* | sudo xargs dnf erase -y

And you can run the script that pg_upgrade left you to delete the old cluster’s data files:

[username@yourhost ~]# sudo su - postgres
[postgres@yourhost ~]$ ./delete_old_cluster.sh

Enjoy!