Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 | 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.
1 | /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
1 | /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.
1 | systemctl stop postgresql-11 |
Upgrade Test
After we stop the service, we will do the upgrade test. While doing this, you need to fill the following commands completely.
1 | /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.
1 | /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 |
Upgrade
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /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' \ |
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.
1 | vi /lib/systemd/system/postgresql-12.service |
We write the postgresql-12 data directory to Environment = PGDATA parameter and save and exit.
Enable PostgreSQL Service
1 | systemctl enable postgresql-12 |
Start PostgreSQL Service
1 | systemctl start postgresql-12 |
Check PostgreSQL Service Status
1 | 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.
1 | delete_old_cluster.sh |
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.
After pg_upgrade command from version 11.17 to 12, we got the below warnings.
The data directory is “/db/data”.
WARNING: user-defined tablespace locations should not be inside the data directory, e.g. /db/data/base/tablespace/xxxxxx
Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Could not create a script to delete the old cluster’s data files
because user-defined tablespaces or the new cluster’s data directory
exist in the old cluster directory. The old cluster’s contents must
be deleted manually.
I can see the upgrade is completed, but can I start the postgresql version 12 and delete the old cluster.