Upgrading postgresql 9.5 to 12

PostgreSQL
,
1

Hi,
We have a set of on premises servers which is currently running on PostgreSQL 9.5 and are having 10TB of data. We have 1 master and 4 slaves. What can be the most suitable approach for upgrade which causes minimum downtime?

Should we go for logical replication between 9.5 and 12 and do a DB flip
or
take downtime and do pg_upgrade?

Regards,
Harshit Gupta

1 Like
2

Hi Harshit,
Upgrading using pg_upgrade is the most common strategy. Mulit-terrabyte databases can be upgraded in a couple of minutes of downtime with hardlink option.
you may please watch the demonstration video : PostgreSQL Upgrade and Host Migration on Ubuntu - PostgreSQL migration tutorial - YouTube
If it is a critical environment, It will be a good idea to hire an expert consultant who has done such upgrades in the past.
Logical replication as you mentioned is also used of upgrades where direct pg_upgrade is not possible. But it is overall more pain taking effort when it comes to big databases and heavy transaction systems.
Regards,
Jobin

2 Likes
3

One other note: What is less talked about is the ability to use rsync on the REPLICA in order to get the same speed that the hard link usage of pg_upgrade. Refer to the postgres documentation for pg_upgrade and do a text search for rsync and you’ll see the reference. Needless to say you’ll need to practice this.

1 Like