In this new article about PostgreSQL database, we will upgrade our PostgreSQL. I am using a CentOS Linux 9, aarch64 architecture.
My environment is single database and server. I am not using clusters environments or features as well.
I am using PostgreSQL, version 13. Let’s check it. Connected on Linux with postgres user, execute psql -V command:
[root@dbserver ~]$ su - postgres
Last login: Wed Apr 30 09:35:51 -03 2025 on pts/0
[postgres@dbserver ~]$ psql -V
psql (PostgreSQL) 13.20
Now, execute show data_directory command on psql prompt, to find out current data directory:
[postgres@dbserver ~]$ psql
psql (13.20)
Type "help" for help.
postgres=# show data_directory;
data_directory
---------------------
/var/lib/pgsql/data
(1 row)
postgres=#
But as we did in previous PostgreSQL articles, our data directory is a symbolic link for a specific disk for database called /pgsql/:
[root@dbserver ~]$ cd /var/lib/pgsql/data
[root@dbserver data]$ ls -ltr
total 64
-rwxr----- 1 postgres postgres 3 Apr 22 20:42 PG_VERSION
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_twophase
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_tblspc
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_snapshots
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_serial
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_replslot
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_notify
drwxr----- 4 postgres postgres 36 Apr 22 20:42 pg_multixact
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_dynshmem
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_commit_ts
-rwxr----- 1 postgres postgres 88 Apr 22 20:42 postgresql.auto.conf
-rwxr----- 1 postgres postgres 1636 Apr 22 20:42 pg_ident.conf
drwxr----- 2 postgres postgres 18 Apr 22 20:42 pg_xact
drwxr----- 3 postgres postgres 60 Apr 22 20:42 pg_wal
drwxr----- 2 postgres postgres 18 Apr 22 20:42 pg_subtrans
-rwxr----- 1 postgres postgres 28085 Apr 23 15:51 postgresql.conf
-rwxr----- 1 postgres postgres 4873 Apr 23 15:55 pg_hba.conf
drwxr----- 6 postgres postgres 54 Apr 24 15:24 base
drwx------ 2 postgres postgres 136 Apr 28 10:53 log
-rw------- 1 postgres postgres 45 May 15 15:17 postmaster.opts
-rw------- 1 postgres postgres 30 May 15 15:17 current_logfiles
-rw------- 1 postgres postgres 92 May 15 15:17 postmaster.pid
drwxr----- 2 postgres postgres 6 May 15 15:17 pg_stat
drwxr----- 2 postgres postgres 4096 May 15 15:18 global
drwxr----- 4 postgres postgres 68 May 15 15:22 pg_logical
drwxr----- 2 postgres postgres 84 May 15 15:44 pg_stat_tmp
[root@dbserver data]$ cd ../
[root@dbserver pgsql]$ ls -ltr
total 12
drwx------ 2 postgres postgres 6 Feb 20 13:31 backups
lrwxrwxrwx 1 root root 14 Apr 22 20:54 data -> /pgsql/13/data
-rw-r--r-- 1 postgres postgres 11450 Apr 30 15:20 audit.sql
Let’s find out where is PostgreSQL binaries. Connected with root user on Linux, execute locate command:
[root@dbserver pgsql]$ locate pg_isready
/usr/bin/pg_isready
/usr/share/man/man1/pg_isready.1.gz
[root@dbserver pgsql]$ cd /usr/bin/
[root@dbserver bin]$ ls pg_*
pg_basebackup pg_checksums pg_controldata pg_ctl pg_dump pg_dumpall pg_isready pg_receivewal pg_recvlogical pg_resetwal pg_restore pg_rewind pg_upgrade pg_verifybackup
[root@dbserver bin]$
Now, let’s create a table to verify its existence after the upgrade. With postgres user, connect on psql prompt and execute the create table command:
[postgres@dbserver ~]$ psql
psql (13.20)
Type "help" for help.
postgres=# CREATE TABLE a AS SELECT id AS a, id AS b, id AS c FROM generate_series(1,50000) AS id;
SELECT 50000
postgres=# select count(*) from a;
count
-------
50000
(1 row)
postgres=# \q
[postgres@dbserver ~]$
To upgrade PostgreSQL database, we need to stop postgresql service. Connect with root Linux user, lets do that:
[root@dbserver ~]$ systemctl stop postgresql.service
If you want take a backup of your data directory, the time is now. We will do a frozen file system backup:
[postgres@dbserver ~]$ tar -cf /pgsql/backup.tar /var/lib/pgsql/data
Now, lets see which versions our Linux repository has with yum command:
[root@dbserver bin]$ yum search postgresql*-server
Last metadata expiration check: 0:52:57 ago on Thu 15 May 2025 03:37:54 PM -03.
======================================================================================================================= Name Matched: postgresql*-server =======================================================================================================================
postgresql-server.aarch64 : The programs needed to create and run a PostgreSQL server
postgresql13-server.aarch64 : The programs needed to create and run a PostgreSQL server
postgresql14-server.aarch64 : The programs needed to create and run a PostgreSQL server
postgresql15-server.aarch64 : The programs needed to create and run a PostgreSQL server
postgresql16-server.aarch64 : The programs needed to create and run a PostgreSQL server
postgresql17-server.aarch64 : The programs needed to create and run a PostgreSQL server
[root@dbserver bin]$
Lets download the latest version:
[root@dbserver bin]$ dnf install -y postgresql17-server postgresql17
Last metadata expiration check: 0:55:01 ago on Thu 15 May 2025 03:37:54 PM -03.
Dependencies resolved.
================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================================================================================
Installing:
postgresql17 aarch64 17.5-2PGDG.rhel9 pgdg17 1.9 M
postgresql17-server aarch64 17.5-2PGDG.rhel9 pgdg17 6.8 M
Installing dependencies:
postgresql17-libs aarch64 17.5-2PGDG.rhel9 pgdg17 336 k
Transaction Summary
================================================================================================================================================================================================================================================================================
Install 3 Packages
Total download size: 9.0 M
Installed size: 43 M
Downloading Packages:
(1/3): postgresql17-libs-17.5-2PGDG.rhel9.aarch64.rpm 741 kB/s | 336 kB 00:00
(2/3): postgresql17-17.5-2PGDG.rhel9.aarch64.rpm 2.4 MB/s | 1.9 MB 00:00
(3/3): postgresql17-server-17.5-2PGDG.rhel9.aarch64.rpm 6.3 MB/s | 6.8 MB 00:01
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 8.3 MB/s | 9.0 MB 00:01
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql17-libs-17.5-2PGDG.rhel9.aarch64 1/3
Running scriptlet: postgresql17-libs-17.5-2PGDG.rhel9.aarch64 1/3
Installing : postgresql17-17.5-2PGDG.rhel9.aarch64 2/3
Running scriptlet: postgresql17-17.5-2PGDG.rhel9.aarch64 2/3
failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink
failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink
failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink
failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink
failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink
failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink
failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink
failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink
failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink
failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink
failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink
Running scriptlet: postgresql17-server-17.5-2PGDG.rhel9.aarch64 3/3
Installing : postgresql17-server-17.5-2PGDG.rhel9.aarch64 3/3
Running scriptlet: postgresql17-server-17.5-2PGDG.rhel9.aarch64 3/3
Verifying : postgresql17-17.5-2PGDG.rhel9.aarch64 1/3
Verifying : postgresql17-libs-17.5-2PGDG.rhel9.aarch64 2/3
Verifying : postgresql17-server-17.5-2PGDG.rhel9.aarch64 3/3
Installed:
postgresql17-17.5-2PGDG.rhel9.aarch64 postgresql17-libs-17.5-2PGDG.rhel9.aarch64 postgresql17-server-17.5-2PGDG.rhel9.aarch64
Complete!
[root@dbserver bin]$
Installation created a new data and binaries directories for the new version:
[root@dbserver bin]$ cd /var/lib/pgsql
[root@dbserver pgsql]$ ls
17 audit.sql backups data
[root@dbserver pgsql]$ cd 17/
[root@dbserver 17]$ pwd
/var/lib/pgsql/17
[root@dbserver 17]$ ls -ltr
total 0
drwx------ 2 postgres postgres 6 May 13 05:08 data
drwx------ 2 postgres postgres 6 May 13 05:08 backups
[root@dbserver 17]$ cd /usr/pgsql-17/
[root@dbserver pgsql-17]$ ls
bin lib share
[root@dbserver pgsql-17]$
Lets create a new data directory on /pgsql/ for new version:
[root@dbserver pgsql-17]$ cd /pgsql/
[root@dbserver pgsql]$ ls -l
total 0
drwxr-xr-x 3 postgres postgres 18 Apr 22 20:42 13
[root@dbserver pgsql]$ mkdir 17
[root@dbserver pgsql]$ chown postgres.postgres 17
[root@dbserver pgsql]$ ls -l
total 0
drwxr-xr-x 3 postgres postgres 18 Apr 22 20:42 13
drwxr-xr-x 2 postgres postgres 6 May 15 16:45 17
[root@dbserver pgsql]$
Let’s remove the new data directory created by installation. Be careful not to drop the wrong data directory.
[root@dbserver pgsql]$ cd /var/lib/pgsql/17/
[root@dbserver 17]$ ls -l
total 0
drwx------ 2 postgres postgres 6 May 13 05:08 backups
drwx------ 2 postgres postgres 6 May 13 05:08 data
[root@dbserver 17]$ rmdir data/
[root@dbserver 17]$ ls -l
total 0
drwx------ 2 postgres postgres 6 May 13 05:08 backups
[root@dbserver 17]$
Now, we need to execute the initdb for PostgreSQL version 17, to create the data directory on right directory. Connected with postgres user, execute the initdb command:
[root@dbserver pgsql]$ su - postgres
Last login: Thu May 15 16:28:06 -03 2025 on pts/0
[postgres@dbserver ~]$ /usr/pgsql-17/bin/initdb -D /pgsql/17/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /pgsql/17/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... America/Sao_Paulo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-17/bin/pg_ctl -D /pgsql/17/data -l logfile start
[postgres@dbserver ~]$ cd /pgsql/17/
[postgres@dbserver 17]$ ls -l
total 4
drwx------ 19 postgres postgres 4096 May 15 17:46 data
[postgres@dbserver 17]$ cd data
[postgres@dbserver data]$ ls
base global pg_commit_ts pg_dynshmem pg_hba.conf pg_ident.conf pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase PG_VERSION pg_wal pg_xact postgresql.auto.conf postgresql.conf
[postgres@dbserver data]$
Lets create a new symbolic link to the new data directory:
[postgres@dbserver ~]$ cd /var/lib/pgsql/17/
[postgres@dbserver 17]$ ls
backups
[postgres@dbserver 17]$ ln -s /pgsql/17/data data
[postgres@dbserver 17]$ ls -l
total 0
drwx------ 2 postgres postgres 6 May 15 17:53 backups
lrwxrwxrwx 1 postgres postgres 14 May 15 19:10 data -> /pgsql/17/data
[postgres@dbserver 17]$
Now, we are ready to upgrade our database. Please, pay attention to the new and old database directories. You will need to change them to the directories for your environment. The pg_upgrade binary the we are using, is the new binary of PostgreSQL 17.
First, lets check if everything is good to go, executing a pre check:
[postgres@dbserver ~]$ time /usr/pgsql-17/bin/pg_upgrade --old-bindir /usr/bin/ --new-bindir /usr/pgsql-17/bin/ --old-datadir /var/lib/pgsql/data --new-datadir /var/lib/pgsql/17/data/ --link --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
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 user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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*
real 0m0.499s
user 0m0.055s
sys 0m0.032s
[postgres@dbserver 17]$
And if everything looks good, execute the upgrade:
[postgres@dbserver ~]$ time /usr/pgsql-17/bin/pg_upgrade --old-bindir /usr/bin/ --new-bindir /usr/pgsql-17/bin/ --old-datadir /var/lib/pgsql/data --new-datadir /var/lib/pgsql/17/data --link
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 user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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/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
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
real 0m1.362s
user 0m0.152s
sys 0m0.039s
[postgres@dbserver ~]$
Post upgrade ask for you to execute two scripts. We will execute it, if necessary, after change the services. First lets enable the new one, and after that, disable the old one, connected with root user:
[root@dbserver 17]$ systemctl enable --now postgresql-17.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-17.service → /usr/lib/systemd/system/postgresql-17.service.
[root@dbserver 17]$ systemctl disable postgresql.service
Removed "/etc/systemd/system/multi-user.target.wants/postgresql.service".
[root@dbserver 17]$
Now, let’s execute the post scripts. Connected with postgres user on Linux, execute vacummdb script:
[postgres@dbserver ~]$ /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "dvdrental": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "dvdrental": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "dvdrental": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
[postgres@dbserver ~]$
A very important point here is delete_old_cluster.sh script. This script will remove the old data directory:
[postgres@dbserver ~]$ pwd
/var/lib/pgsql
[postgres@dbserver ~]$ cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/var/lib/pgsql/data'
[postgres@dbserver ~]$
So, before execute that, check if this still necessary or not. Because my environment is a test environment, I will execute, but check everything before.
[postgres@dbserver ~]$ ./delete_old_cluster.sh
[postgres@dbserver ~]$
Lets check our new psql prompt version:
[postgres@dbserver ~]$ cd /usr/pgsql-17/bin/
[postgres@dbserver bin]$ ./psql -V
psql (PostgreSQL) 17.5
[postgres@dbserver bin]$
Finally, lets check the table that we created in the beginning of this article:
[postgres@dbserver bin]$ ./psql
psql (17.5)
Type "help" for help.
postgres=# \d a
Table "public.a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
postgres=# select count(*) from a;
count
-------
50000
(1 row)
postgres=#
Everything looks good.
If you want to call the binaries, directly, without entering in the directory, you will have to remove every pg_ binary on /usr/bin/ and create a symbolic link for each under /usr/pgsql-17/bin/. Please, pay attention if you will do that.
My Piece of Data 