Can we use wal2json/pg_recvlogical cross-versions (9.5 and 12.5)

[rjoroy89]

Hi,

I am trying to perform a pg_upgrade from Postgresql v9.5.23 to Postgresql v12.5.1 in Ubuntu 18.04. I have managed to reduce the downtime to 10mins but business is not happy with the same. To give you more context, this is what I am doing :

I have an old cluster(3 node repmgr setup) in v9.5 in Ubuntu 16.04(lets call it as Cluster A) and have to move to a new set of nodes on Ubuntu 18.04 and will have v12.5(lets call it as Cluster B). This is done to resolve existing OS and database security vulnerabilities.

In Cluster B, I have installed both v9.5 and v12.5 and add the v9.5 Postgresql instance as a standby node into cluster A which is in v9.5. Once it is in sync, I stop cluster A and run pg_upgrade --link on Cluster B and start the v12.5 cluster B and allows application to connect into it. This is currently taking 10 mins of business downtime which is un-acceptable as we are stopping cluster A when the upgrade runs.

Hence, I wanted to check about wal2json whether I can apply all the changes captured in v9.5 Cluster A into v12.5 Cluster B which will greatly reduce the downtime. Your inputs will greatly help me. Appreciate your time & prompt response.

Thanks & Regards,
Rijo

[eulerto]

In Cluster B, I have installed both v9.5 and v12.5 and add the v9.5 Postgresql instance as a standby node into cluster A which is in v9.5. Once it is in sync, I stop cluster A and run pg_upgrade --link on Cluster B and start the v12.5 cluster B and allows application to connect into it. This is currently taking 10 mins of business downtime which is un-acceptable as we are stopping cluster A when the upgrade runs.

I'm curious why your pg_upgrade using link mode is lasting so long. Are you waiting vacuumdb to finish? wal2json does not require the statistics to be updated.

Hence, I wanted to check about wal2json whether I can apply all the changes captured in v9.5 Cluster A into v12.5 Cluster B which will greatly reduce the downtime.

wal2json consumes what you have in the WAL. pg_upgrade creates a new cluster, hence, a new WAL sequence. I'm not sure what you want to do during the upgrade process. Do you want to continue accepting changes in 9.5? How do you expect that 12 contains the data inserted in 9.5 during the upgrade?

In order to reduce your downtime, my suggestion is to use logical replication instead of pg_upgrade. You can minimize the downtime for a few seconds.