Upgrading PostgreSQL 14 to 15 on Fedora, RHEL, CentOS, Rocky, Alma Linux with PGDG RPM packages
By Jon Jensen
October 14, 2022
PostgreSQL 15 changes
Yesterday PostgreSQL 15 was released! It includes a number of headline features since version 14 that make it worth upgrading your databases running earlier versions of PostgreSQL:
- Improved sort performance
- In-memory statistics collection (removing the statistics collector process)
- SQL-standard
MERGEsyntax that can includeINSERT,UPDATE, andDELETEactions in a single statement - Logical replication publisher options to include all tables in a schema (including those added in the future), row filtering and column lists, and simplified conflict management
- JSON format log output (to files only, not syslog)
- Optional LZ4 and Zstandard compression for WAL (write-ahead log) files, especially useful for those not using btrfs or zfs filesystem built-in compression
- New regular expression functions
regexp_count,regexp_instr,regexp_like, andregexp_substr - And many other performance improvements and feature enhancements
Thanks to the PostgreSQL developers for the continuing amazing work!
Prerequisites for upgrading
This article shows how to upgrade to PostgreSQL 15 if you:
- are running one of the Red Hat family of Linux operating systems, including Fedora, Red Hat Enterprise Linux (RHEL), Rocky Linux, AlmaLinux, Oracle Linux, or CentOS
- have PostgreSQL 14 installed
- are not using
dnfmodularity or the stock distributionpostgresqlRPMs, but rather the PostgreSQL Global Development Group (PGDG)postgresql14RPMs that allow simultaneous coexistence of multiple major Postgres versions
My examples are done on the Fedora 36 x86_64 OS. Things will be very similar on other Red Hat family members.
We are doing system administration work here, so let’s act as system administrators and work as the root user rather than using sudo repetitively:
$ su -
Password:
[root@yourhost ~]#
Stop PostgreSQL 14
Stop your existing PostgreSQL 14 database server, if it is running, and prevent it from starting automatically at boot in the future:
[root@yourhost ~]# systemctl disable --now postgresql-14
Update your system
Make sure your packages are fully updated:
[root@yourhost ~]# dnf upgrade
Last metadata expiration check: 2:28:40 ago on Fri 14 Oct 2022 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 15
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 15, which happily coexists alongside PostgreSQL 14 (and possibly other versions) thanks to the way the PGDG RPMs are designed and where they install files:
[root@yourhost ~]# dnf install postgresql15-server postgresql15-devel postgresql15-contrib
Last metadata expiration check: 2:29:14 ago on Fri 14 Oct 2022 09:01:38 AM MDT.
Dependencies resolved.
==========================================================================
Package Architecture Version Repository Size
==========================================================================
Installing:
postgresql15-contrib x86_64 15.0-1PGDG.f36 pgdg15 708 k
postgresql15-devel x86_64 15.0-1PGDG.f36 pgdg15 5.2 M
postgresql15-server x86_64 15.0-1PGDG.f36 pgdg15 5.9 M
Installing dependencies:
postgresql15 x86_64 15.0-1PGDG.f36 pgdg15 1.5 M
postgresql15-libs x86_64 15.0-1PGDG.f36 pgdg15 290 k
Transaction Summary
==========================================================================
Install 5 Packages
Total download size: 14 M
Installed size: 54 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): postgresql15-contrib-15.0-1PGDG.f36.x86_64.rpm 412 kB/s | 708 kB 00:01
(2/5): postgresql15-libs-15.0-1PGDG.f36.x86_64.rpm 171 kB/s | 290 kB 00:01
(3/5): postgresql15-15.0-1PGDG.f36.x86_64.rpm 435 kB/s | 1.5 MB 00:03
(4/5): postgresql15-devel-15.0-1PGDG.f36.x86_64.rpm 1.0 MB/s | 5.2 MB 00:05
(5/5): postgresql15-server-15.0-1PGDG.f36.x86_64.rpm 1.7 MB/s | 5.9 MB 00:03
-----------------------------------------------------------------------------------
Total 2.0 MB/s | 14 MB 00:06
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql15-libs-15.0-1PGDG.f36.x86_64 1/5
Running scriptlet: postgresql15-libs-15.0-1PGDG.f36.x86_64 1/5
Installing : postgresql15-15.0-1PGDG.f36.x86_64 2/5
Running scriptlet: postgresql15-15.0-1PGDG.f36.x86_64 2/5
Running scriptlet: postgresql15-server-15.0-1PGDG.f36.x86_64 3/5
Installing : postgresql15-server-15.0-1PGDG.f36.x86_64 3/5
Running scriptlet: postgresql15-server-15.0-1PGDG.f36.x86_64 3/5
Installing : postgresql15-contrib-15.0-1PGDG.f36.x86_64 4/5
Installing : postgresql15-devel-15.0-1PGDG.f36.x86_64 5/5
Running scriptlet: postgresql15-devel-15.0-1PGDG.f36.x86_64 5/5
Verifying : postgresql15-15.0-1PGDG.f36.x86_64 1/5
Verifying : postgresql15-contrib-15.0-1PGDG.f36.x86_64 2/5
Verifying : postgresql15-devel-15.0-1PGDG.f36.x86_64 3/5
Verifying : postgresql15-libs-15.0-1PGDG.f36.x86_64 4/5
Verifying : postgresql15-server-15.0-1PGDG.f36.x86_64 5/5
Installed:
postgresql15-15.0-1PGDG.f36.x86_64 postgresql15-contrib-15.0-1PGDG.f36.x86_64
postgresql15-devel-15.0-1PGDG.f36.x86_64 postgresql15-libs-15.0-1PGDG.f36.x86_64
postgresql15-server-15.0-1PGDG.f36.x86_64
Complete!
Create the new PostgreSQL 15 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 14.
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:
[root@yourhost ~]# PGSETUP_INITDB_OPTIONS=-k /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
If you used the default initdb options, just omit the PGSETUP_INITDB_OPTIONS environment variable:
[root@yourhost ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
Migrate your data with pg_upgrade
At this point you could start the PostgreSQL 15 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 14 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
initdbflags that match the old cluster. (Already discussed.) -
Install extension shared object files. (Some may be contained in the
contribpackage, while others are separate, such as PostGIS.) -
Set authentication to
peerinpg_hba.conffor both old and new Postgres versions. This is the default for the new cluster, but your old PostgreSQL 14 cluster may need to be adjusted sopg_updatecan access it.
We will run pg_upgrade as the postgres OS user and specify the standard PGDG RPM locations for Postgres 14 & 15:
[root@yourhost ~]# su - postgres
[postgres@yourhost ~]$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin -d /var/lib/pgsql/14/data -D /var/lib/pgsql/15/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
[root@yourhost ~]# rm -rf ~postgres/15/data/*
[root@yourhost ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
Now let’s try running pg_upgrade again:
[root@yourhost ~]# su - postgres
[postgres@yourhost ~]$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-14/bin -B /usr/pgsql-15/bin -d /var/lib/pgsql/14/data -D /var/lib/pgsql/15/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-15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
That’s better!
Start PostgreSQL 15
You may want to manually migrate over any configuration in postgresql.conf and pg_hba.conf from your old PostgreSQL 14 cluster to your new PostgreSQL 15 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
[root@yourhost ~]# systemctl enable --now postgresql-15
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.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:
[root@yourhost ~]# su - postgres
[postgres@yourhost ~]$ /usr/pgsql-15/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 15 features are really available:
[postgres@yourhost ~]$ psql
psql (15.0)
Type "help" for help.
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 15.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/15/sql-merge.html
Remove PostgreSQL 14
There is likely no urgency for you to do this, but later after you’re convinced PostgreSQL 15 is working well for you, you can remove the old PostgreSQL 14 packages and data.
First we’ll run this command without agreeing, to see what the package manager plans to do:
[root@yourhost ~]# rpm -qa postgresql14\* | xargs dnf erase
Dependencies resolved.
======================================================================
Package Architecture Version Repository Size
======================================================================
Removing:
postgresql14 x86_64 14.5-1PGDG.f36 @pgdg14 7.7 M
postgresql14-devel x86_64 14.5-1PGDG.f36 @pgdg14 19 M
postgresql14-libs x86_64 14.5-1PGDG.f36 @pgdg14 935 k
postgresql14-server x86_64 14.5-1PGDG.f36 @pgdg14 24 M
Transaction Summary
======================================================================
Remove 4 Packages
Freed space: 51 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:
[root@yourhost ~]# rpm -qa postgresql14\* | xargs dnf erase -y
And you can run the script that pg_upgrade left you to delete the old cluster’s data files:
[root@yourhost ~]# su - postgres
[postgres@yourhost ~]$ ./delete_old_cluster.sh
Enjoy!
Reference
- PostgreSQL 15 release announcement
- PostgreSQL 15 release notes
- PostgreSQL Red Hat family Linux downloads
- PostgreSQL Global Development Group (PGDG) Yum Repository
Comments