Migrating your PostgreSQL cluster from 9.6 to 12

Introduction

Recently, I upgrade my PostgreSQL servers from version 9.6 to 12 and there are quite subtle differences in the way we setup PostgreSQL server in an High Availability(HA) cluster mode. I could not find solution to all the differences and issues encountered during this migration, at a single place. I had to do some research and readings to fix all the issues and that’s the major motivation behind writing this blog.

Who is this blog not for?

This blog does not guide on how we can use pg_upgrade to migrate the existing data to higher versions. It only talks about the differences in the setup part of postgresql cluster in HA mode itself.

Who is this blog for?

If you run your postgresql in HA mode having one main/primary database server and one or more standby servers who replicate from main server and want to move the same setup to version 12 then this blog might be of interest to you. If you only run a single server as part of your database cluster then it might not be very useful for you.

  1. The content of recovery.conf has moved to the default configuration file i.e. postgresql.conf. The usual content of this file used to look like:
standby_mode = 'on'primary_conninfo = 'host=10.11.110.123 port=5432 user=replicator password=password'trigger_file = '/tmp/postgresql.trigger'primary_slot_name = 'replication_slot'recovery_target_timeline = 'latest'
  • standby_mode config is not needed now.

How does postgresql server understands in which mode(either primary or standby ) it has to run?

Earlier, the presence of recover.conf file in the data_directory was an indication to postgresql to run in standby mode and replicate data from main/primary server.

Issue of permission denied while opening shared memory segment

We briefly talked about the config dynamic_shared_memory_type and how its value can not be set to none now. Other possible values are:

  • sysv: for System V shared memory allocated via shmget
2021-06-11T17:33:20.216682+00:00 FATAL:  could not open shared memory segment "/PostgreSQL.1202384855": Permission denied
/dev/shm is a temporary file storage filesystem and is an efficient way of passing data between programs. It uses RAM for storing info.
user:/dev$ ls -l shm 
drwxrwx--- 2 root root 40 Jun 16 14:05 shm/
user:~$ chmod 1777 /dev/shm 
user:/dev$ ls -l shm
drwxrwxrwx 2 root root 40 Jun 16 14:10 shm
  • If you face the issue of permission denied while opening shared memory segment, then permissions on /dev/shm is not right.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Akash Kumar

Akash Kumar

Movie Buff, Cricket Enthusiast, Loves to code and open to have discussion about almost everything