How to upgrade PostgreSQL to Latest version PostgreSQL 15 in CWP Centos 7/8/Almalinux 8/RockyLinux 8

[Sandeep B.]

In this tutorial I’ll show you how you can upgrade PostgreSQL from older version to latest version at the time of writing 13 is the latest version, This is tested in CWP and other servers running centos 7 and 8 i.e. el7/el8.

The upgrade procedure is little bit complicated and you need to follow the below steps one by one :

Step 1 :

Backup all the data

mkdir /home/pgsql
chown -R postgres:postgres /home/pgsql
su - postgres
pg_dumpall > /home/pgsql/backup
exit

Step 2 :

Uninstalling/removing the current version of PostgreSQL :

systemctl stop postgresql
mv /var/lib/pgsql/data/ /home/pgsql/data.old
rpm -e --nodeps postgresql postgresql-devel postgresql-libs postgresql-server

If you already installed from official repo i.e. version 9,10,11 and 12 (skip this steps if you didn’t used official repo to install pgsql) do this :

systemctl stop postgresql-#
mv /var/lib/pgsql/#/data/ /home/pgsql/#/data.old
rpm -e --nodeps postgresql# postgresql#-devel postgresql#-libs postgresql#-server
replace “#”with the version number i.e. 9/10/11/12/13/14

Step 3 :

Now install the Latest version of PostgreSQL :

EL7/Centos 7 :

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y yum-utils centos-release-scl-rh
yum-config-manager --disable centos-sclo-rh
yum --enablerepo=centos-sclo-rh install llvm-toolset-7-clang
yum install postgresql15-server postgresql15-devel

EL8/Centos 8 :

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf install postgresql15-server postgresql15-devel

Step 4 :

Now copying the configuration file to new installation of PostgreSQL :

Centos 7/8/EL7/EL8 :

su - postgres
mv /var/lib/pgsql/15/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf.bak
wget -O https://www.alphagnu.com/upload/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf
chown -R postgres:postgres /var/lib/pgsql/15/data/pg_hba.conf
/usr/pgsql-15/bin/initdb
exit

if you have used official repo to install pgsql (skip this step if you didn’t installed any pgsql from official repo before) :

su - postgres
/usr/pgsql-15/bin/initdb
cp /home/pgsql/#/data.old/pg_hba.conf /var/lib/pgsql/15/data/
cp /home/pgsql/#/data.old/postgresql.conf /var/lib/pgsql/15/data/
exit

replace “#” with version number you used above

Step 5 :

Now start PostgreSQL and enable it to start on boot :

systemctl enable postgresql-15
systemctl start postgresql-15

Step 6 :

Restore the previous backups done in Step 1

su - postgres
psql -d postgres -f /home/pgsql/backup

Step 7 :

Create symlink of new version of service :

systemctl stop postgresql
rm -rf /usr/lib/systemd/system/postgresql.service
ln -s /usr/lib/systemd/system/postgresql-15.service /usr/lib/systemd/system/postgresql.service
systemctl daemon-reload
systemctl stop postgresql-15.service
systemctl enable postgresql
systemctl restart postgresql

EXTRA upgrading phpPgAdmin in CWP :

cd /usr/local/cwpsrv/var/services
yum install cwpPgphp -y
yum reinstall cwpPgphp -y
mv phpPgAdmin phpPgAdmin.bak
wget https://github.com/phppgadmin/phppgadmin/releases/download/REL_7-13-0/phpPgAdmin-7.13.0.zip
unzip phpPgAdmin-7.13.0.zip
mv phpPgAdmin-7.13.0 phpPgAdmin
rm -rf phpPgAdmin-7.13.0.zip