Subject: Re: Downgrade pgsql 17 to pgsql 12 question

From: Merlin Moncure <[email-masked]>
Date: Sun, 28 Sep 2025 17:40:44 -0600

On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <[email-masked]>
wrote:

> Hello,
>
> I have a strange requirement to downgrade from pgsql 17 to pgsql 12. This
> is because we found in production certain incompatibilities between both
> versions for our database. It should have been caught in testing but was
> not.
>

Agree with others that snap downgrade is not necessarily a good choice
here.  Either way, if I were in your shoes, I'd be loading a plain text
dump, maybe with some light massaging to strip out some compatibility
issues.

Can you let us know what the hang up is?  Version upgrades these days are
usually pretty painless except for some performance issues, unless you have
some unusual situations, for example, exotic extensions.

merlin

---

From: Ashish Mukherjee <[email-masked]>
Date: Tue, 30 Sep 2025 13:53:23 +0530

Thank you all for your inputs.

Well, Percona TDE was leading to the queries being very inefficient / slow
after upgrading to pgsql 17. Explain analyze shows that query planning time
shoots up crazily. A decision was taken to go back to pgsql 12, which
worked out fine as there was no incompatibility. I restored from the binary
dump with the -j option, as our database is huge. I completely agree that
downgrade is not a good option but a pragmatic one under the circumstances.

Now the consideration is to use some other encryption option for the
database which will work fine on pgsql 17. Cybertec's technology is one
route, the other is EDB. I am happy to hear experiences of folks here with
pgsql encryption options for v17 on large databases (2.5T in our case).

On Mon, Sep 29, 2025 at 5:10 AM Merlin Moncure <[email-masked]> wrote:

> On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <
> [email-masked]> wrote:
>
>> Hello,
>>

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 30 Sep 2025 11:47:00 +0200

On Tue, 2025-09-30 at 13:53 +0530, Ashish Mukherjee wrote:
> database which will work fine on pgsql 17. Cybertec's technology is
> one route, the other is EDB. I am happy to hear experiences of folks
> here with pgsql encryption options for v17 on large databases
> (2.5T in our case).

I will refrain from making a recommendation, but you should know that
data-at-rest encryption will always incur a certain performance penalty.

Whatever solution you choose, you should run performance tests.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Tue, 30 Sep 2025 09:12:11 -0400

No restoring to unencrypted PG 17?

On Tue, Sep 30, 2025 at 4:23 AM Ashish Mukherjee <[email-masked]>
wrote:

>>> I have a strange requirement to downgrade from pgsql 17 to pgsql 12.
>>> This is because we found in production certain incompatibilities between
>>> both versions for our database. It should have been caught in testing but
>>> was not.
>>>
>>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 30 Sep 2025 07:47:57 -0700

On 9/30/25 01:23, Ashish Mukherjee wrote:
> Well, Percona TDE was leading to the queries being very inefficient / 
> slow after upgrading to pgsql 17. Explain analyze shows that query 
> planning time shoots up crazily. A decision was taken to go back to 

How did you determine that Percona TDE was the issue vs a 5 version jump 
in Postgres?

> route, the other is EDB. I am happy to hear experiences of folks here 
> with pgsql encryption options for v17 on large databases (2.5T in our case).

Personally I would verify first that you are not hitting some more 
general issue with the 5 years of changes in Postgres since the last 
release of 12 and current release of 17.

> 
> On Mon, Sep 29, 2025 at 5:10 AM Merlin Moncure <[email-masked] 
> <mailto:[email-masked]>> wrote:

-- 
Adrian Klaver
[email-masked]

---

From: Merlin Moncure <[email-masked]>
Date: Tue, 30 Sep 2025 09:13:06 -0600

> Whatever solution you choose, you should run performance tests.

Yeah.  This applies to database upgrades in general.

The lists have quite a bit of, I upgraded, and "query X that drives my
platform now is 100x slower".  I don't think this suggests that postgres is
getting worse; foundationally, it's mostly getting faster, but simply that
the planner changes how it responds to certain conditions.  Over time, I've
learned some painful lessons and try to write SQL that is less risky from a
performance standpoint.

Developers tend to optimize into fragile planner behaviors chasing
performance, sometimes without realizing it.  These kinds of issues are
almost always very fixable assuming you can modify the SQL or the thing
writing the SQL. The takeaway here is: test/verify before making major
upgrades, and bake in some recalibration time.  Adding encryption or other
major features strongly reinforces that need.

merlin

---

From: Ashish Mukherjee <[email-masked]>
Date: Wed, 1 Oct 2025 15:03:30 +0530

I think the conclusion is to do a more thorough testing before the upgrade
next time. Have updated our playbook for upgrades to include more thorough
testing.

> How did you determine that Percona TDE was the issue vs a 5 version jump

*I upgraded multiple non TDE databases from v12 to v17 and they are all
fine.*

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 1 Oct 2025 08:23:30 -0700

On 10/1/25 02:33, Ashish Mukherjee wrote:
> I think the conclusion is to do a more thorough testing before the 
> upgrade next time. Have updated our playbook for upgrades to include 
> more thorough testing.
> 

> /I upgraded multiple non TDE databases from v12 to v17 and they are all 
> fine./

Then raise an issue here:

https://forums.percona.com/c/postgresql/pg-tde-transparent-data-encryption-tde/82

Seems to me finding the cause and a possible fix would build on the 
effort you have already put into using TDE, rather then learning an 
entirely new system and having it possibly fail somewhere else.

---

From: Pavan Kumar <[email-masked]>
Date: Fri, 3 Oct 2025 10:15:58 -0500

Hello Ashish Mukherjee,

Did you get a chance to disable JIT related parameters after  PG 17
upgrade ?

If your database size in TB , try to consider pglogical bi directional
replication. you will have 17.x version and 12.x version. if you ran in to
any issue you can always switch back to old version.
I am not sure TDE supported bi directional replication

-- 

*Regards,#!  Pavan Kumar----------------------------------------------*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *