In this article, we will see how to upgrade from PostgreSQL 10 to 11 on CentSO or Redaht.
We have following options to upgrade from PostgreSQL 10 to 11:
Logical Dump/Restore
Binary In-Place Upgrades(pg_upgrade)
Logical Replication
In this article, we are going to upgrade PostgreSQL 10 to 11 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 10 to 11:
We are performing PostgreSQL upgradation on CentOS system. Same steps can be applied on Redhat.
1. Install PostgreSQL 11
sudo yum install postgresql11-server
2. Initialize database by running below command.
/usr/pgsql-11/bin/postgresql-11-setup initdb
3. Stop PostgreSQL-10 server.
systemctl stop postgresql-10.service
4. Log in as the postgres user.
sudo su - postgres
5. Check clusters using pg_upgrade.
/usr/pgsql-11/bin/pg_upgrade \ -b /usr/pgsql-10/bin \ -B /usr/pgsql-11/bin \ -d /var/lib/pgsql/10/data \ -D /var/lib/pgsql/11/data \ --check
6. Now, upgrade without –check option.
/usr/pgsql-11/bin/pg_upgrade \ -b /usr/pgsql-10/bin \ -B /usr/pgsql-11/bin \ -d /var/lib/pgsql/10/data \ -D /var/lib/pgsql/11/data \
7. Swap the ports for the old and new PostgreSQL versions.
vi /var/lib/pgsql/11/data/postgresql.conf
# …and change “port = 5433” to “port = 5432”
vi /var/lib/pgsql/10/data/postgresql.conf
# …and change “port = 5433” to “port = 5433”
8. Start PostgreSQL server(latest)
systemctl start postgresql-11.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 postgresql10*
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-11/bin:$PATH
20. Then run the command psql. You will connect to PostgreSQL server 11 successfully.