How to Upgrading PostgreSQL 9.2 to 12

That’s a Tutorial for upgrading the PostgreSQL9 database on CentOS 7 server. In order for your database to be properly patched, secured, and have all the DB features your applications need, you are supposed to upgrade your database. The upgrade process in this article installs another PostgreSQL engine alongside the old one and migrates the data. This how-to can also be used for production deployments, as the original data is not modified during the upgrade.

mkdir -p /data/postgres_upgrade

mkdir /data/postgres_upgrade/backup_postgres92

mkdir /data/postgres_upgrade/backup_postgres12

chown postgres:root /data/postgres_upgrade/backup_postgres92

chown postgres:root /data/postgres_upgrade/backup_postgres12

You can backup parameter file

cd $PGDATA

cp pg_hba.conf pg_hba_new.conf

cp postgresql.conf postgresql_new.conf

Make sure to close all applications using Postgres(Tomcat – IdM). PostgreSQL should be running during the upgrade, but we also need to make sure that no daemons are accessing the database. We achieve this by changing the TCP port on which the database is listening. The file is in /etc/systemd/system/postgresql.service:

vi /etc/systemd/system/postgresql.service

Environment=PGPORT=6000

systemctl daemon-reload
systemctl restart PostgreSQL
systemctl status PostgreSQL

Backup all databases on old Postgres

cd /data/postgres_upgrade/backup_postgres92/

pg_dump –compress=9 –create –port=5432 postgres > postgres_backup.sql.gz

You can stop process

service postgresql-9.2 stop

You can install new postgres

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum -y install epel-release yum-utils

yum-config-manager –enable pgdg12

yum install postgresql12-server postgresql12

You can install postgres instance

/usr/pgsql-12/bin/postgresql-12-setup initdb

You can change postgres12 port

vi /var/lib/pgsql/12/data/postgresql.conf

port = 7000

systemctl daemon-reload

You can run postgres service

systemctl enable –now postgresql-12

systemctl status postgresql-12

systemctl stop postgresql-12

systemctl stop postgresql-12.service

You can start upgrade process

/usr/pgsql-12/bin/pg_upgrade -v –old-datadir=”/var/lib/pgsql/9.2/data/” –new-datadir=”/var/lib/pgsql/12/data/” –old-bindir=”/usr/pgsql-9.2/bin/” –new-bindir=”/usr/pgsql-12/bin/” –old-port=6000 –new-port=7000

systemctl enable –now postgresql-12
systemctl status postgresql-12

su – postgres

/usr/pgsql-9.6/bin/psql -p 7000

You can change default postgres port 5432

vi /var/lib/pgsql/12/data/postgresql.conf

port = 5432

systemctl daemon-reload

systemctl restart postgresql-12

systemctl status postgresql-12

ss -tulwn | grep LISTEN

You can delete old PostgreSQL 9x

mv /var/lib/pgsql/9.2/data/ /data/postgres_upgrade/

yum remove postgresql.x86_64

yum remove postgresql-libs.x86_64

systemctl stop postgresql-12

systemctl start postgresql-12

Everything is okay

You can create symlink

ln -s /etc/alternatives/pgsql-psql /usr/pgsql-12/bin/psql
ln -s /etc/alternatives/pgsql-clusterdb /usr/pgsql-12/bin/clusterdb
ln -s /etc/alternatives/pgsql-createdb /usr/pgsql-12/bin/createdb
ln -s /etc/alternatives/pgsql-createlang /usr/pgsql-12/bin/createlang
ln -s /etc/alternatives/pgsql-createuser /usr/pgsql-12/bin/createuser
ln -s /etc/alternatives/pgsql-dropdb /usr/pgsql-12/bin/dropdb
ln -s /etc/alternatives/pgsql-droplang /usr/pgsql-12/bin/droplang
ln -s /etc/alternatives/pgsql-dropuser /usr/pgsql-12/bin/dropuser
ln -s /etc/alternatives/pgsql-pg_basebackup /usr/pgsql-12/bin/pg_basebackup
ln -s /etc/alternatives/pgsql-pg_dump /usr/pgsql-12/bin/pg_dump
ln -s /etc/alternatives/pgsql-pg_dumpall /usr/pgsql-12/bin/pg_dumpall
ln -s /etc/alternatives/pgsql-pg_restore /usr/pgsql-12/bin/pg_restore
ln -s /etc/alternatives/pgsql-reindexdb /usr/pgsql-12/bin/reindexdb
ln -s /etc/alternatives/pgsql-vacuumdb /usr/pgsql-12/bin/vacuumdb

Congrats Have a nice day.