Migrating your PostgreSQL cluster from 9.6 to 12

As postgresql 9.6 is end of life is near and most of users would be planning to move to higher versions. There are differences between the two major versions. Read to find what they are.

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.

Differences between postgresql version 12 and 9.6

1. The famous recovery.conf file is not present in version 12!
2. 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'

• trigger_file is now called promote_trigger_file which is a file whose presence will force postgresql server to end recovery and start running as primary/main.
• standby_mode config is not needed now.

Keeping these above mentioned configuration parameters in postgresql.conf file does not impact the nature of the role(primary/standby) of the postgresql server.

3. In case of postgresql version 9.6 the config parameter dynamic_shared_memory_type could be set to none and it used to work without any issue. But now this can not be set to none. This parameter specifies postgresql to select the dynamic shared memory implementation that server should use.

Now if we do not have recovery.conf file now, you might be wondering about:

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.

The answer to the above question is standby.signal file. If a file by this name is present inside the postgresql data_directory, then postgresql server will run as standby 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:

• posix: for POSIX shared memory allocated using shm_open
• sysv: for System V shared memory allocated via shmget

Other values can also be configured, but those are not relevant to unix based systems.

In my setup, the underlying OS was Ubuntu 20.04 and I used the value posix for dynamic_shared_memory_type. When I started the server, it crashed. I analysed the logs and encountered following particular error:

2021-06-11T17:33:20.216682+00:00 FATAL:  could not open shared memory segment "/PostgreSQL.1202384855": Permission denied

I tried searching about this particular error and could not find any easy solution to fix this. After doing multiple google searches I came across this old postgresql error thread. Though the bug is for version 9.5 but the error message is exactly the same. The solution talks about permissions on /dev/shm. I came across this mount point for the first time in my life and upon reading about it I found its purpose:

/dev/shm is a temporary file storage filesystem and is an efficient way of passing data between programs. It uses RAM for storing info.

On the system where I was setting up the postgresql server, the permission were as follows:

user:/dev$ ls -l shm 
drwxrwx---  2 root root  40 Jun 16 14:05 shm/

As per the answers given in the error thread the permission should be 1777. Before starting the server I ran following command:

user:~$ chmod 1777 /dev/shm 
user:/dev$ ls -l shm
drwxrwxrwx  2 root root  40 Jun 16 14:10 shm

After this I started the server and the Permission denied issue was gone and postgresql server started to work in primary/standby replication mode without any problem.

Conclusion

• recovery.conf is replaced by standby.signal file and its content is moved to postgresql.conf file.
• If you face the issue of permission denied while opening shared memory segment, then permissions on /dev/shm is not right.

I think I have documented the all major differences in HA part of postgresql setup. Happy migration!