Subject: RHEL certified repo provided PostgreSQL Major Version Upgrade.

From: Sameer Malve <[email-masked]>
Date: Tue, 8 Oct 2024 13:54:11 +0530

Hi Team,

We are trying to perform the Major version upgrade on RHEL 8 using
RHEL-certified repo `rhel-8-for-x86_64-appstream-rpms` from PG 15.6 to PG
16.4 using `pg_upgrade` but we are facing the below issue.

As the binaries are getting upgraded in-place `/usr/bin` hence while
performing pg_upgrade we are not getting the older binaries.

pg_upgrade command is as follows.

[2024-10-04 11:18:47]  $ /usr/bin/pg_upgrade -b /usr/bin/ -B /usr/bin/ -d
> /pgData/pgsql/15/data -D /pgData/pgsql/16/data -c
> [2024-10-04 11:19:19]
> [2024-10-04 11:19:19]  You must identify the directory where the old
> cluster binaries reside.
> [2024-10-04 11:19:19]  Please use the -b command-line option or the
> PGBINOLD environment variable.
> [2024-10-04 11:19:19]  Failure, exiting
>

Regards,
Sameer Malve

---

From: obi reddy <[email-masked]>
Date: Tue, 8 Oct 2024 14:02:42 +0530

Hi Sameer,

Please try with this command.

/usr/bin/pg_upgrade -b /usr/pgsql-15/bin/ -B /usr/pgsql-16/bin/ -d
/pgData/pgsql/15/data -D /pgData/pgsql/16/data -c

If above command is not working check the below one.

/usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-15/bin/ -B /usr/pgsql-16/bin/ -d
/pgData/pgsql/15/data -D /pgData/pgsql/16/data -c

Thanks & Regards
Obireddy

---

From: Zaid Shabbir <[email-masked]>
Date: Tue, 8 Oct 2024 14:54:21 +0500

Hello Sameer,

For upgrade you can also use quick upgrade scripts

/usr/pgsql-16/bin/postgresql-16-setup check_upgrade

/usr/pgsql-16/bin/postgresql-16-setup upgrade

Above commands are helpful to upgrade from 15 to 16, in case someone wants
to upgrade from -2 older version then some minor adjustments required in
setup script.

Regards,

Zaid

---

From: Sameer Malve <[email-masked]>
Date: Tue, 8 Oct 2024 15:55:44 +0530

Hi Obi,

Just to update you, we are  not using the PGDG community that provides
Postgres.

And the major difference between the PGDG community provided Postgres and
RHEL provided Postgres is the binary files are installed in different
location.

in PGDG Postgres it is installed under `/usr/pgsql-16/bin` whereas in RHEL
Postgres it gets installed under `/usr/bin`.

hence we can't use the command you suggested.

Regards,
Sameer Malve.

On Tue, Oct 8, 2024 at 2:02 PM obi reddy <[email-masked]> wrote:

> /usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-15/bin/ -B /usr/pgsql-16/bin/
> -d /pgData/pgsql/15/data -D /pgData/pgsql/16/data -c
>
> Thanks & Regards
> Obireddy
>
>
>

---

From: Sameer Malve <[email-masked]>
Date: Tue, 8 Oct 2024 16:03:17 +0530

Hi Zaid,

Does this Upgrade command stand true in case of postgres upgrade between
community (PGDG) to RHEL?

Regards,
Sameer Malve

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 08 Oct 2024 12:48:24 +0200

On Tue, 2024-10-08 at 16:03 +0530, Sameer Malve wrote:
[about pg_upgrade]
> Does this Upgrade command stand true in case of postgres upgrade between community (PGDG) to RHEL?

Probably, unless they configured something differently that affects
the storage format.  Try and see.

Recommendation: keep using PGDG packages.

Yours,
Laurenz Albe

---

From: Sameer Malve <[email-masked]>
Date: Tue, 8 Oct 2024 16:50:58 +0530

[email-masked] <[email-masked]>

Hi Laurenz ,

We have installed the PostgreSQL from RHEL repo using `dnf install <package
name>` so we have not passed any customised path for installation.

And there the problem arises as older version binaries get overwritten when
we upgrade/ install the new version of Postgres.

Regards,
Sameer Malve

---

From: Scott Ribe <[email-masked]>
Date: Tue, 8 Oct 2024 06:28:55 -0600

> On Oct 8, 2024, at 5:20 AM, Sameer Malve <[email-masked]> wrote:
> 
> And there the problem arises as older version binaries get overwritten when we upgrade/ install the new version of Postgres.  

Install the older version and proceed. (Although first I'd make sure that the PG in /usr/bin is not a symlink.)

---

From: Tom Lane <[email-masked]>
Date: Tue, 08 Oct 2024 09:45:49 -0400

> performing pg_upgrade we are not getting the older binaries.

It's been more than ten years since I was responsible for Red Hat's
packaging of PG, but I think I recall that the solution in place for
pg_upgrade involved a separate postgresql-upgrade RPM containing
previous-version executables, which you were supposed to install
alongside the new version for long enough to perform the upgrade.
Whatever the details were, I'm quite certain the package's README file
contained an explanation of how to do it.  Read that, do not pay
attention to people or chatbots telling you how you'd do it with some
other packaging.

			regards, tom lane

---

From: Sameer Malve <[email-masked]>
Date: Tue, 8 Oct 2024 14:02:00 +0000

Thanks Tom.  I have installed that upgrade package rpm will go through it and try. And would let you know incase I get stucked .

Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Tom Lane <[email-masked]>
Sent: Tuesday, October 8, 2024 7:15:49 PM
To: Sameer Malve <[email-masked]>
Cc: [email-masked] <[email-masked]>; Sameer Malve <[email-masked]>
Subject: Re: RHEL certified repo provided PostgreSQL Major Version Upgrade.

Caution :External

                        regards, tom lane

---

From: Holger Jakobs <[email-masked]>
Date: Tue, 08 Oct 2024 16:20:10 +0200

Before installing the new version over the old one, save the binaries and libraries to another path. You can later provide this path to pg_upgrade.

After successful upgrade, delete the saved old files. 

Regards
Holger

-- 
Holger Jakobs, Bergisch Gladbach 
Tel. +49 178 9759012

---

From: Sameer Malve <[email-masked]>
Date: Thu, 10 Oct 2024 11:52:47 +0530

Hi Tom,

Thanks for providing me with the correct point to look for a solution, and
I was able to upgrade my cluster from PG15.6 to PG 16.4.

I have few questions.

1. As I have observed, the PostgreSQL upgrade package has only the lower
major version binaries. So what if I want to upgrade from PG 13 to PG 16?
2. If upgrade fails then what are the rollback steps. Do you know if it is
documented somewhere?

Regards,
Sameer Malve

On Tue, Oct 8, 2024 at 7:32 PM Sameer Malve <[email-masked]>
wrote:

> Thanks Tom.  I have installed that upgrade package rpm will go through it
> and try. And would let you know incase I get stucked .
>
> Get Outlook for Android <https://aka.ms/AAb9ysg>
> ------------------------------
> *From:* Tom Lane <[email-masked]>
> *Sent:* Tuesday, October 8, 2024 7:15:49 PM
> *To:* Sameer Malve <[email-masked]>
> *Cc:* [email-masked] <[email-masked]>;
> Sameer Malve <[email-masked]>
> *Subject:* Re: RHEL certified repo provided PostgreSQL Major Version
> Upgrade.
>
> Caution :External
>

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 10 Oct 2024 08:33:25 +0200

>    major version binaries. So what if I want to upgrade from PG 13 to PG 16?

That works just the same.

> 2. If upgrade fails then what are the rollback steps. Do you know if it is documented somewhere? 

If you didn't use the --link option of pg_upgrade, you can simply fall back to
the old cluster.

If you used --link, you will have to restore your backup (or promote a standby
server that you prepared for this eventuality).

See the pg_upgrade documentation.

Yours,
Laurenz Albe