In this article, we will see how to upgrade from PostgreSQL 11 to 12 on CentSO or Redaht.
We have following options to upgrade from PostgreSQL 11 to 12:
Logical Dump/Restore
Binary In-Place Upgrades(pg_upgrade)
Logical Replication
In this article, we are going to upgrade PostgreSQL 11 to 12 using pg_upgrade binary.
pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]
Where
-b: the old PostgreSQL executable directory; environment variable PGBINOLD
-B: the new PostgreSQL executable directory; environment variable PGBINNEW
-d: the old database cluster configuration directory; environment variable PGDATAOLD
-D: the new database cluster configuration directory; environment variable PGDATANEW
-c or –check: check clusters only, don’t change any data
Steps to PostgreSQL upgrade from 11 to 12:
We are performing PostgreSQL upgradation on CentOS system. Same steps can be applied on Redhat.
1. Install PostgreSQL 12
sudo yum install postgresql12-server
2. Initialize database by running below command.
/usr/pgsql-12/bin/postgresql-12-setup initdb
3. Stop PostgreSQL-11 server.
systemctl stop postgresql-11.service
4. Log in as the postgres user.
sudo su - postgres
5. Check clusters using pg_upgrade.
/usr/pgsql-12/bin/pg_upgrade \ -b /usr/pgsql-11/bin \ -B /usr/pgsql-12/bin \ -d /var/lib/pgsql/11/data \ -D /var/lib/pgsql/12/data \ --check
6. Now, upgrade without –check option.
/usr/pgsql-12/bin/pg_upgrade \ -b /usr/pgsql-11/bin \ -B /usr/pgsql-12/bin \ -d /var/lib/pgsql/11/data \ -D /var/lib/pgsql/12/data \
7.
vi /var/lib/pgsql/12/data/postgresql.conf
# …and change “port = 5433” to “port = 5432”
vi /var/lib/pgsql/11/data/postgresql.conf
# …and change “port = 5433” to “port = 5433”
8. Now start PostgreSQL12 server.
systemctl start postgresql-12.service
9. Switch to postgres user
sudo su - postgres
10. Let connect to postgresql and check the PostgreSQL version.
psql select version();
11. Lets verify the databases and users by running below commands.
select datname from pg_database; \du
12. Now run generated script analyze_new_cluster.sh
13. Find the list of postgresql ackages
yum list --installed | grep postgresql
14. Remove old version packages
sudo yum remove postgresql11*
15. switch to postgres user and drop the old cluster data by running below script which was generated at the time of PostgreSQL upgradation.
./delete_old_cluster.sh
16. Now, connect to PostgreSQL by running psql. It throughs error.
bash-4.4$ psql bash: psql: command not found
17. Solution is goto cd /etc/profile.d/
18. Create a .sh file like below
touch postgresqlprofile.sh
19. Add the below line to postgresqlprofile.sh by vi postgresqlprofile.sh
export PATH=/usr/pgsql-12/bin:$PATH
20. Then run the command psql. You will connect to PostgreSQL server 12 successfully