How To Upgrade Postgres To New Version

In this article, I’ll demonstrate the process of upgrading PostgreSQL from version 9.4 to 15.6 on a CentOS 7 operating system. PostgreSQL upgrades are essential for accessing new features and enhancing database performance. Follow along for a concise guide to ensure a smooth transition to the latest version.

Existing cluster version

1. Download and install the postgres 15

https://yum.postgresql.org/rpmchart

[root@localhost rpm]# wget https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# wget https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-contrib-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# wget https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-libs-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# wget https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-server-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# ls -lrth
total 8.5M
-rw-r–r–. 1 root root 286K Feb 8 03:06 postgresql15-libs-15.6-1PGDG.rhel7.x86_64.rpm
-rw-r–r–. 1 root root 1.6M Feb 8 03:06 postgresql15-15.6-1PGDG.rhel7.x86_64.rpm
-rw-r–r–. 1 root root 713K Feb 8 03:06 postgresql15-contrib-15.6-1PGDG.rhel7.x86_64.rpm
-rw-r–r–. 1 root root 5.9M Feb 8 03:06 postgresql15-server-15.6-1PGDG.rhel7.x86_64.rpm

To resolve dependency error for libzstd install following packages

[root@localhost rpm]# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm [root@localhost rpm]# yum install libzstd-devel

Now Install This packages in following sequence

[root@localhost rpm]# yum install postgresql15-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# yum install postgresql15-server-15.6-1PGDG.rhel7.x86_64.rpm

[root@localhost rpm]# yum install postgresql15-contrib-15.6-1PGDG.rhel7.x86_64.rpm

2. Initialise the postgres 15 version

-bash-4.2$ /usr/pgsql-15/bin/initdb -D /pgdata/15/data/

Note:- you can use default data directory to initialize

3. Start and check postgres 15 version

Note:- before starting postgres 15 version stop your old postgres version. other wise you can change the port no. in configuration file and start the postgres 15 version to check it’s working fine. here we stop our old 9 version and start 15 version.

-bash-4.2$ /oracle/pg_data/bin/pg_ctl -D /var/lib/pgsql/9/pg_data stop

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pgdata/15/data/ start

Note:- login into postgres 15 version server and check its working fine

4. Run the postgres upgrade with check option

existing data_directory(v9) – > /var/lib/pgsql/9/pg_data
existing postgres binary(v9) – > /oracle/pg_data/bin
new data_directory(v15) – > /pgdata/15/data/
new postgres binary(v15) – > /usr/pgsql-15/bin

Note:- This command will check the compatibility of upgrade. here we change the port no of 15 version to 5433 in configfile. so we can start both the version simultaneously and test the upgraded version.

-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /var/lib/pgsql/9/pg_data -D /pgdata/15/data -b /oracle/pg_data/bin -B /usr/pgsql-15/bin -p 5432 -P 5433 -c

5. Run the actual postgres upgrade

Note:- make sure that both the cluster should be stop before running actual upgrade

-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /var/lib/pgsql/9/pg_data -D /pgdata/15/data -b /oracle/pg_data/bin -B /usr/pgsql-15/bin -p 5432 -P 5433

6. Start the new postgres cluster and check.

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pgdata/15/data/ start
waiting for server to start….2024-04-09 16:47:51.970 IST [963] LOG: redirecting log output to logging collector process
2024-04-09 16:47:51.970 IST [963] HINT: Future log output will appear in directory “log”.
done
server started

Note:- check database, functions, users and extension in new cluster. execute update_extensions.sql to update extensions and run /usr/pgsql-15/bin/vacuumdb –all –analyze-in-stages command. change the port no and restart again. remember all the old config files data you have to copy manually in new cluster config files. remove old cluster by running the script delete_old_cluster.sh above method is for extra space approach. next we will see the without extra space approach i.e in place upgradation.

Without Extra Space approach

1. Run the postgres upgrade with check option

-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /var/lib/pgsql/9/pg_data -D /pgdata/15/data -b /oracle/pg_data/bin -B /usr/pgsql-15/bin -k -c

Note:- only difference in without extra space approach is it create link between old cluster and new cluster and save the space, both the cluster pointing to same disk location its like hard-link. In command you have use only -k or –link.

2. Run the actual postgres upgrade

Note:- Stop the old and new postgres cluster

-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade -d /var/lib/pgsql/9/pg_data -D /pgdata/15/data -b /oracle/pg_data/bin -B /usr/pgsql-15/bin -p5432 -P5433 -k

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
Checking for removed “abstime” data type in user tables ok
Checking for removed “reltime” data type in user tables ok
Checking for removed “tinterval” data type in user tables ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for tables WITH OIDS ok
Checking for invalid “sql_identifier” user columns ok
Checking for invalid “unknown” user columns ok
Checking for roles starting with “pg_” 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_clog 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/9/pg_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 hash indexes ok
Checking for extension updates notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

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

Note:- execute update_extensions.sql to upgrade extensions and run /usr/pgsql-15/bin/vacuumdb –all –analyze-in-stages command. change the port no and restart again. remember all the old config files data you have to copy manually in new cluster config files. remove old cluster by running the script delete_old_cluster.sh

Conclusion:-

Well done! We’ve upgraded PostgreSQL database from version 9.4 to 15.6 on CentOS 7. Remember, taking backups and snapshots beforehand is essential. With your database now updated, you’re ready to enjoy the latest features and improvements while ensuring data integrity and system security.