Scribd
Upload
21 views2 pages

Postgres Upgrade Step by Step

The document outlines the steps to upgrade PostgreSQL from version 12 to 15, including checking the current version, backing up data, installing the new version, and performing the upgrade. It emphasizes ensuring compatibility between old and new data directories and includes commands for stopping the old server, initializing the new cluster, and verifying the upgrade. Finally, it instructs on uninstalling the old version and deleting its data directory.

Uploaded by

Ganesh Padwal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
21 views2 pages

Postgres Upgrade Step by Step

The document outlines the steps to upgrade PostgreSQL from version 12 to 15, including checking the current version, backing up data, installing the new version, and performing the upgrade. It emphasizes ensuring compatibility between old and new data directories and includes commands for stopping the old server, initializing the new cluster, and verifying the upgrade. Finally, it instructs on uninstalling the old version and deleting its data directory.

Uploaded by

Ganesh Padwal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

1) Check whether old version is running and also find location of data and bin

directory
Data : /var/lib/pgsql/12/data
Bin : /usr/pgsql-12/bin

2) Backup data using postgres user


#pg_basebackup -h localhost -p 5432 -U postgres -D /var/lib/pgsql/12/backups -Ft -z
-Xs -P
or
#pg_dumpall -D /var/lib/pgsql/12/backups/clusterall.sql

3) install new version


#yum list modules postgresql15*
#yum install postgresql15-server.x86_64 postgresql15-contrib.x86_64

4) Check whether new version is installed and also find location of data and bin
directory
We can see that folder with name [15] will be created in /var/lib/pgsql
We can see that folder with name [pgsql-15] will be created in /usr

5) Initialize PostgreSQL 15 cluster


#su postgres
#cd /usr/pgsql-15/bin/
#./pg_ctl -D /var/lib/pgsql/15/data/ initdb

6) Check whether old and new data directory are compatible


#su postgres
#cd /tmp
#/usr/pgsql-15/bin/pg_upgrade --old-bindir=/usr/pgsql-12/bin/
--new-bindir=/usr/pgsql-15/bin/ --old-datadir=/var/lib/pgsql/12/data --new-
datadir=/var/lib/pgsql/15/data --check

7) Ensure application is down and no connections can be made to postgresql. If


neeed block connection from pg_hba.conf file.

8) Backup data again using postgres user


#pg_basebackup -h localhost -p 5432 -U postgres -D
/var/lib/pgsql/12/backups/before_upgrade -Ft -z -Xs -P
or
#pg_dumpall -D /var/lib/pgsql/12/backups/before_upgrade/clusterall.sql

9) stop postgres 12
# cd /usr/pgsql-12/bin
# ./pg_ctl -D /var/lib/pgsql/12/data stop
waiting for server to shut down.... done
server stopped
bash-4.4$
# ./pg_ctl -D /var/lib/pgsql/12/data status
pg_ctl: no server running

10) Perform Upgrade step


#su postgres
#cd /tmp
#/usr/pgsql-15/bin/pg_upgrade --old-bindir=/usr/pgsql-12/bin/
--new-bindir=/usr/pgsql-15/bin/ --old-datadir=/var/lib/pgsql/12/data --new-
datadir=/var/lib/pgsql/15/data

11) Start Postgresql 15


#su postgres
#cd /usr/pgsql-15/bin/
#./pg_ctl -D /var/lib/pgsql/15/data/ start

12) check port


#psql
--> select port;

13) check tables and data on old server

14) Uninstall old postgresql ( use root account)


#yum remove postgresql12

15) Delete old version data directory


#cd /tmp
#./delete_old_cluster.sh

You might also like