Re: Seeking Advice: postgreSQL Major Version upgrade for pgpool-II and streaming replication setup

Dear Team,

Please advise.

On Sat, Feb 8, 2025, 10:15 Motog Plus <mplus7535(at)gmail(dot)com> wrote:

> Deal All,
>
> 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:
>
> 1. *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
> 2. *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?
> 3. *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.
>
> Thank you for your time and valuable insights.
>
> Sincerely,
>
> Ramzy
>
>
>