In this article, we will upgrade PostgreSQL 11 to 12. If you want to upgrade PostgreSQL 10 to 11 you should read the article “Upgrade PostgreSQL 10 to 11”
PostgreSQL is evolving day by day and every new release comes with different features. One of the features of PostgreSQL 12 is the optimization of read / write performance in the B-tree index.
The REINDEX CONCURRENTLY property has been added and lots of new features have been announced.
We need to complete the postgresql12 installation first for the upgrade. Install postgresql12 with packages as follows.
yum install postgresql12 postgresql12-devel postgresql12-contrib postgresql12-libs postgresql12-server
We have completed the installation. Let’s switch to the postgres user and perform the initdb operation by specifying the data and log files as follows. If not specified, default path will be set.
/usr/pgsql-11/bin/initdb -D DATA_DIRECTORY -X LOG_DIRECTORY
We specify the path where the data files will be created with the -D parameter, and we specify the path where the log files will be created with the -X parameter.
Sample initdb script
/usr/pgsql-12/bin/initdb -D /postgresql12/data -X /postgresql12/log
Upgrade PostgreSQL 11 to 12
We have done Postgresql12 installation and initdb processes, we can stop the service and start the upgrade process.
systemctl stop postgresql-11
After we stop the service, we will do the upgrade test. While doing this, you need to fill the following commands completely.
/usr/pgsql-12/bin/pg_upgrade \ --old-datadir= postgresql11 data directory \ --new-datadir=postgresql12 data directory \ --old-bindir=postgresql11 bin file \ --new-bindir=postgresql12 bin file \ --old-options '-c config_file=postgresql11 postgresql.conf file' \ --new-options '-c config_file=postgresql12 postgresql.conf file' \ --check
We configure the pg_upgrade script above as follows and performing upgrade test.
/usr/pgsql-12/bin/pg_upgrade \ --old-datadir=/postgresql11/data \ --new-datadir=/postgresql12/data \ --old-bindir=/usr/pgsql-11/bin \ --new-bindir=/usr/pgsql-12/bin \ --old-options '-c config_file=/postgresql11/data/postgresql.conf' \ --new-options '-c config_file=/postgresql12/data/postgresql.conf' \ --check
As can be seen from the screenshot above, the upgrade test has been successfully completed. If you have the same result in your test, you can remove the check command in the test script and perform the upgrade.
--old-options '-c config_file=/postgresql11/data/postgresql.conf' \
--new-options '-c config_file=/postgresql12/data/postgresql.conf' \
Configure Environments After Upgrade
Upgrade has been successfully completed. We need to enable the service, but first we need to change the value of “Environment = PGDATA” parameter in the postgresql-12 service as our own data directory.
You need to do this with the root user.
We write the postgresql-12 data directory to Environment = PGDATA parameter and save and exit.
Enable PostgreSQL Service
systemctl enable postgresql-12
Start PostgreSQL Service
systemctl start postgresql-12
Check PostgreSQL Service Status
systemctl status postgresql-12
As you can see, we started the service from the new data directory.
You have seen that there is no problem in the upgrade process and you have done the necessary tests.
Delete Old PostgreSQL Cluster
Finally, if you want to delete postgresql11 directory, postgresql prepares sh script for you.
When you run the above .sh file, everything related to postgresql11 will be deleted.
NOTE: I recommend that you do not run the delete_old_cluster.sh script without completing your tests.