PostgreSQL Upgrade

 What is upgrade : 

• Upgrading database from on postgresql release to newer one.
• PostgreSQL version number consist of a major and minor version number ex. 15.1.
• Major release of PostgreSQL , the internal data storage format is subject to change.
• Minor release never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number.
• Before PostgreSQL version 17.0 version numbers consist of three number ex 15.0

Example : PostgreSQL 15 don't have incremental db backup functionality but in PostgreSQL 17 have incremental functionality

Reason for Upgrade

• Security Fixes
• Enhanced Features
• Resolved Bugs and other issues
• Reduced Costs
• End of Support  

Note : 

1 :  If you database big its take lot of time for backup and restore ,hence required a lot of downtime. This is the reason why small database are good candidates for this type of upgrades.

2: Since we are going to server upgrade  so you will have your old copy and we are going to upgrade it to a newer copy on the same server. We have to ensure that there is adequate space is allocated to accommodate both the copies.

for example : if my old database is one TB , I have to ensure that I have to get additional one 1TB. for new copy db.

3. Port : It is purely up to us to decide which version we want to this port to work

PostgreSQL upgrade using PG_Dumpall Utility:

Step 1: First we have to check database is running or not .

Step 2: Check how many database is there in current cluster.

             postgres=# \l

Step 3: check current version :

               postgres=# select version();  

PostgreSQL 15.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 

                                                

Step 4: check size of database: for space

postgres=# select pg_database.datname as "database name",pg_database_size(pg_database.datname)/1024/1024 as size_in_db from pg_database;

 database name | size_in_db

---------------+------------

 postgres      |          8

 testdb          |          7

 template1    |          7

 template0    |          7

(4 rows)

Note : check tables and index after upgrade it will be available 

Step 5: Backup entire cluster using pg_dumpall before insure all application is not use this database due to transaction will lost that mean data lost.

[root@localhost backups]# PGPASSWORD='root@123' pg_dumpall -U postgres -h localhost  > /var/lib/pgsql/backups/cluster.sql

Step 6: Stop PostgreSQL Cluster 

[root@localhost backups]# systemctl stop postgresql-15.service

Step 7 : Rename old Directory 

Step 8: Install postgresql17 as root or as admin user

[root@localhost ~]# yum list modules postgresql17*

Last metadata expiration check: 0:11:33 ago on Tue 12 Aug 2025 11:02:12 PM IST.

Available Packages

postgresql17.x86_64                                                                 17.5-3PGDG.rhel9                                                        

postgresql17-contrib.x86_64                                                    17.5-3PGDG.rhel9                                                        

postgresql17-devel.x86_64                                                       17.5-3PGDG.rhel9                                                      

postgresql17-docs.x86_64                                                         17.5-3PGDG.rhel9                                                        

[root@localhost ~]# yum install postgresql17.x86_64   postgresql17-contrib.x86_64 -y 

Last metadata expiration check: 0:14:47 ago on Tue 12 Aug 2025 11:02:12 PM IST.

Dependencies resolved.

================================================================================================================================================== Package                                    Architecture                    Version                                    Repository                       Size

==================================================================================================================================================Installing:

 postgresql17                               x86_64                          17.5-3PGDG.rhel9                           pgdg17                          1.9 M

Installing dependencies:

 postgresql17-libs                          x86_64                          17.5-3PGDG.rhel9                           pgdg17                          341 k

login by postgres user

[root@localhost ~]# su - postgres   

[postgres@localhost ~]$ cd /usr/

[postgres@localhost usr]$ ls

bin  games  include  lib  lib64  libexec  local  pgsql-16  pgsql-17  sbin  share  src  tmp

Note There is two directory 16 and 17

[postgres@localhost pgsql-17]$ cd bin/

[postgres@localhost bin]$ ./pg_ctl -D /var/lib/pgsql/17/

[postgres@localhost bin]$ ./pg_ctl -D /var/lib/pgsql/17/data/ initdb

The files belonging to this database system will be owned by user "postgres".

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

Note : Now we are going to go ahead version 16 and 17 is compactable 

[postgres@localhost tmp]$ /usr/pgsql-17/bin/pg_upgrade \

  --old-datadir=/var/lib/pgsql/16/data \

  --new-datadir=/var/lib/pgsql/17/data \

  --old-bindir=/usr/pgsql-16/bin \

  --new-bindir=/usr/pgsql-17/bin \

  --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 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*

Step :9 Take backup cluster 

[postgres@localhost backup]$ pg_dumpall > /var/lib/pgsql/backup/clusterall.sql

Step 10 : Stop database

[root@localhost ~]# systemctl stop postgresql-16.service

Finally We have to upgrade database

    [postgres@localhost tmp]$/usr/pgsql-17/bin/pg_upgrade \

    --old-datadir=/var/lib/pgsql/16/data \

    --new-datadir=/var/lib/pgsql/17/data \

    --old-bindir=/usr/pgsql-16/bin \

    --new-bindir=/usr/pgsql-17/bin

Performing Consistency Checks

-----------------------------

Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         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
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

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

Note : -k option i will not able to come back to version 16 again Now --Clone option  This clone option It will take default 

[postgres@localhost tmp]$/usr/pgsql-17/bin/pg_upgrade \

    --old-datadir=/var/lib/pgsql/16/data \

    --new-datadir=/var/lib/pgsql/17/data \

    --old-bindir=/usr/pgsql-16/bin \

    --new-bindir=/usr/pgsql-17/bin  --clone 

Validate data and version of upgrades data

[postgres@localhost tmp]$ cd /usr/pgsql-17/bin/

[postgres@localhost bin]$ ./pg_ctl -D /var/lib/pgsql/17/data/ start

waiting for server to start....2025-08-13 00:06:27.476 IST [14187] LOG:  redirecting log output to logging collector process done

server started

[postgres@localhost bin]$ psql

psql (17.5)

Type "help" for help.

postgres=# select version();

                                                 version

----------------------------------------------------------------------------------------------------------

 PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit

postgres=# show port;

 port : 5432

(1 row)

In Case u are unable to start db

[postgres@localhost ~]$ /usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/data