Thursday , March 28 2024

Upgrade PostgreSQL 11 to 12

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.

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.

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

We see that the initdb has performed successfully.

Upgrade PostgreSQL 11 to 12

We have done Postgresql12 installation and initdb processes, we can stop the service and start the upgrade process.

Upgrade Test

After we stop the service, we will do the upgrade test. While doing this, you need to fill the following commands completely.

We configure the pg_upgrade script above as follows and performing upgrade test.

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.

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

Start PostgreSQL Service

Check PostgreSQL Service Status

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.

Loading

About Faruk Erdem

One comment

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories