Upgrading PostgreSQL 9.6 to PostgreSQL 13

It the the time for everybody using PostgreSQL 9.6 to start planning an upgrade to the latest supported PostgreSQL version. PostgreSQL Global Development Group (PGDG) supports any major version for 5 years after its initial release. PostgreSQL 9.6 with its initial release on September 29, 2016 is about to become an unsupported version. Its final minor version will be released on November 11, 2021, upon which no security fixes, bug fixes or patches are supported by PGDG. Thus, it is important to immediately start upgrading your PostgreSQL databases to the latest major versions or PostgreSQL 13, the latest stable major version. For this reason, we are starting a series of articles on the available options in performing the upgrade from PostgreSQL 9.6 to PostgreSQL 13. In this first article, we will cover the steps involved in upgrading PostgreSQL 9.6 to PostgreSQL 13 using pg_upgrade with hard links.

The steps being discussed in this article should work for all the following versions -

• Upgrading PostgreSQL 9.6 to PostgreSQL 10
• Upgrading PostgreSQL 9.6 to PostgreSQL 11
• Upgrading PostgreSQL 9.6 to PostgreSQL 12
• Upgrading PostgreSQL 9.6 to PostgreSQL 13
• Upgrading PostgreSQL 10 to PostgreSQL 11
• Upgrading PostgreSQL 10 to PostgreSQL 12
• Upgrading PostgreSQL 10 to PostgreSQL 13
• Upgrading PostgreSQL 11 to PostgreSQL 12
• Upgrading PostgreSQL 11 to PostgreSQL 13
• Upgrading PostgreSQL 12 to PostgreSQL 13

Point to Note : This approach is only applicable if you are using PostgreSQL on dedicated or virtual servers like AWS EC2 or GCP Virtual machines or Azure Virtual machines. This approach does not work for DBaaS platforms like Amazon RDS or Azure database for PostgreSQL or Google Cloud SQL for PostgreSQL. However, the approach for DBaaS will be covered in the subsequent articles.

PostgreSQL Setup considered in this article

Let us consider a scenario where there exists a PostgreSQL replication cluster. Primary (Server A) to Standby (Server B). We have streaming replication setup between Primary and the Standby servers.

pg_upgrade using hard links

In this approach, pg_upgrade creates files and directories in the new data directory (used for latest or target PostgreSQL version) similar to the old data directory (Old or legacy PostgreSQL version). However, these files and directories are pointed to the same inodes as the files and directories in the old directory. This way, there is no need of copying or moving data between the two data directories. For this reason, this approach only works when the new target data directory is created on the same file system as the older one.

An inode is a data structure contains the metadata about the disk block location. A file or directory is nothing but a link to an inode.

Choosing an Architecture that enables ROLLBACK capabilities

As the hard links upgrade using pg_upgrade is an in-place upgrade, it is also important to enable the possibilities of rollback. In this article, I have included 2 additional servers, because i want to make it more easier for rollback, without the need of risking the existing replication cluster. While it is always recommended to perform a backup before performing any upgrade, it is equally important to have at least one stand-alone server that can be used for the purpose of a rollback. For this reason, MigOps suggests you to add one or more new standby servers that can be used for the purpose of upgrade and enable the possibilities for faster rollback. To understand this better, see the following architecture diagram.

In the above architecture diagram, we could notice that I have added 2 more servers to the existing replication cluster using streaming replication. Let's assume that the new servers are Server C and Server D.

You may always contact MigOps to work with an expert to discuss the overall plan and get assistance for performing your PostgreSQL Upgrades.

Prerequisites

1. Prepare the configuration file for PostgreSQL 13 or the target PostgreSQL version much earlier.
2. Steps 10, 11, 12 discussed in the next steps may be performed much earlier to avoid additional downtime.
3. Ensure to use the pg_upgrade binary of the target version (PostgreSQL 13) during the steps 12 and 14.
4. Always test before using this approach for Production.
5. Please make sure that the OS (Operating System) and its version matches in all the database servers, else, you may experience some issues when there are changes to the locale data by the authors maintaining GNU C library for the respective OS. See this PostgreSQL Wiki article for more details. 

Steps to perform the upgrade using hard links

The numbers of steps to perform this method for a faster upgrade appears to be huge but the amount of time involved is usually a few minutes or even seconds.

Step 1 : Add 1 or 2 more standby servers (Server C and Server D) configured using Streaming Replication. To see the steps to configure streaming replication, you may use this detailed article.

Step 2 : Create a new archive directory. This is one of the most crucial requirements to avoid inconsistencies. Else, upon breaking the replication, there will be a conflict with WAL segments from different timelines, when Server C is promoted.

Step 3 : Stop the application and avoid any writes to the database.

The original servers : Server A and Server B can be shutdown and remain untouched during this upgrade. We would perform the further upgrade steps on Servers C and D. PostgreSQL running on Server D must be shutdown before promoting the Server C as a Primary. This way, the original servers, Server A and B can still be used for the purpose of rollback.

$ pg_ctl -D $PGDATA stop -mf

Validate the status

$ pg_ctl -D $PGDATA status

Step 4 : Modify the archive_command on Server C to set the archive directory location. Changes to archive_command will be applicable upon a SIGHUP.

$ psql -c "ALTER SYSTEM SET archive_command = 'cp %p /new_wal_dir/%f'"
$ psql -c "select pg_reload_conf()"

Validate the archive_command setting using the following command.

$ psql -c "show archive_command"

Step 5 : Promote the PostgreSQL cluster on Server C.

$ pg_ctl -D $PGDATA promote

Validate if the PostgreSQL on Server C is now a primary, using the following command.

$ psql -c "select pg_is_in_recovery()"

We must see the output as f (which means false). If the server is not is recovery, its a primary.

Step 6 : Switch a WAL segment on Server C and validate if archiving is successful.

$ psql -c "select pg_switch_wal()"
$ ls -alrth /new_wal_dir/

Step 7 : Enable replication from Server C to Server D.

For this purpose, we must modify the recovery.conf file of Server D and perform a restart. The only expected change to the already existing recovery.conf file is the restore_command parameter. It must include the command to copy WALs from the new archive directory.

$ cd $PGDATA
$ vi recovery.conf

.. add or modify the following settings in recovery.conf file.
restore_command = 'cp /wal_archive/pgsql13/%f %p'
recovery_target_timeline = 'latest'

Step 8 : Start PostgreSQL on Server D to enable replication from Server C.

$ pg_ctl -D $PGDATA start

Validate replication from Server C to Server D.

$ psql -c "select * from pg_stat_replication"

Step 9 : Switch WAL segments on Server C and validate replication to Server D.

$ psql -c "select pg_switch_wal()"
$ psql -c "select * from pg_stat_replication"

Step 10 : Install PostgreSQL 13 and any other extensions that were not part of the contrib, on both Servers C and D. (This step can be performed much earlier too).

Step 11 : Initialize PostgreSQL 13 data directory using initdb, on the same disk as the older data directory. This must be performed on both Server C and Server D. (This step can be performed much earlier too).

$ initdb -D <data_directory_location>

Step 12 : Perform consistency check to fix the inconsistencies reported. Run the following command on Server C. (This step can be performed much earlier too).

$ /usr/pgsql-13/bin/pg_upgrade \
-b /usr/pgsql-9.6/bin/ \
-B /usr/pgsql-13/bin/ \
-d /<old_data_dir> \
-D /<new_data_dir> \
--check

Step 13 : Shutdown PostgreSQL running on older version on Server C and Server D.

On Server C, perform a checkpoint and then shutdown PostgreSQL.

$ psql -c "select pg_switch_wal()"
$ psql -c "CHECKPOINT"
$ psql -c "select * from pg_stat_replication"

Shutdown older version PostgreSQL clusters on Server C and Server D

$ pg_ctl -D $PGDATA stop -mf

Step 14 : Perform the upgrade on Server C using hard links. Do not start PostgreSQL until step 19.

$ /usr/pgsql-13/bin/pg_upgrade \
-b //usr/pgsql-9.6/bin/ \
-B /usr/pgsql-13/bin/ \
-d /<old_data_dir> \
-D /<new_data_dir> \
-k

Step 15 : Validate the upgrade log and save the inconsistencies reported in the upgrade log.

For example, you may have to rebuild a few indexes.

Step 16 : Perform a dry run of the in-place upgrade through rsync on the standby server using hard links. The following must be performed on Server C. Substitute Server_D with the IP or the host name of the standby server D.

$ rsync --archive --delete --hard-links --size-only --no-inc-recursive --verbose \
/<old_data_dir> \
/<new_data_dir> \
postgres@<Server_D>:/<new_data_dir> --dry-run

Step 17 : Once the dry run in the previous step is successful, we can now upgrade the standby database in place.

$ rsync --archive --delete --hard-links --size-only --no-inc-recursive --verbose \
/<old_data_dir> \
/<new_data_dir> \
postgres@<Server_D>:/<new_data_dir>/

Step 18 : Copy postgresql.conf, pg_hba.conf and any other configuration files prepared for the new PostgreSQL version to their appropriate locations on both the Servers C and D.

Step 19 : Start PostgreSQL on Server C.

$ pg_ctl -D $PGDATA start

Validate Status

$ pg_ctl -D $PGDATA status

Step 20 : To start the PostgreSQL on Server D as a Standby, touch the standby.signal file on the new data directory.

$ touch /<new_pg_13_data_directory>/standby.signal

Step 21 : Start PostgreSQL on Standby.

$ pg_ctl -D $PGDATA start

Validate Status

$ pg_ctl -D $PGDATA status

Once the above step in successfully completely, you could switch your applications to the new Servers C and D. Eventually, you may go ahead and decommission the old Servers A and B.

Conclusion

The hard links approach for performing faster upgrades in PostgreSQL is one of the mostly used approaches for PostgreSQL deployed on On Premise/Cloud - physical and virtual servers. The major concern i have heard about this approach is the (1) possibilities for rollback in accidental issues and (2) the time involved in rebuilding a standby. This article has covered the approach that can be considered to overcome both these concerns and complete the upgrade. In our next article, we are going to discuss what is supported/not-supported while upgrading PostgreSQL 9.6 to PostgreSQL 13 or 14. Meanwhile, if you need any help or support in performing the upgrade, please contact MigOps by providing your details in the following form and someone from our team will contact you.