We are planning a major PostgreSQL version upgrade. We have the following setup:
Data Volume: Terabytes of data, including Large Object (LOB) data.
Replication: Streaming replication setup.
Load Balancing: pgpool-II is used for load balancing and read/write splitting.
Infrastructure: All components are running on Virtual Machines (VMs).
We would greatly appreciate your guidance on the following aspects:
Upgrade Procedure:
Should we start with stopping pgpool-II first, followed by the standby server?
Is it necessary to stop or delete anything on the standby server related to replication before upgrading the primary?
Then Should we delete the replication slot on the primary server using pg_drop_replication_slot() before stopping the primary server? Is there any other point we need to take care of before installing the new postgres version on the primary and doing the upgrade using pg_upgrade
Data Migration:
Given the large data volume, is it likely that streaming replication will fail during the upgrade?
Should we consider manually copying the data directory from the upgraded primary server to the standby server as a more efficient approach?
If so, what are the best practices for copying the data directory?
Post-Upgrade Steps:
After the primary server upgradation, new version installation on standby, data copy from primary to standby should we then create the replication slot on primary and setup the streaming replication the same way as was done earlier - creating standby file, updating conninfo and starting replication using pg_basebackup?
We are eager to learn from your expertise and ensure a smooth and successful upgrade process.
