public inbox for [email protected]help / color / mirror / Atom feed
Re: Downgrade pgsql 17 to pgsql 12 question 9+ messages / 6 participants [nested] [flat]
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-28 23:40 Merlin Moncure <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Merlin Moncure @ 2025-09-28 23:40 UTC (permalink / raw) To: Ashish Mukherjee <[email protected]>; +Cc: pgsql-general On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <[email protected]> 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-30 08:23 Ashish Mukherjee <[email protected]> parent: Merlin Moncure <[email protected]> 0 siblings, 4 replies; 9+ messages in thread From: Ashish Mukherjee @ 2025-09-30 08:23 UTC (permalink / raw) To: Merlin Moncure <[email protected]>; +Cc: pgsql-general 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 protected]> wrote: > On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee < > [email protected]> 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 > > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-30 09:47 Laurenz Albe <[email protected]> parent: Ashish Mukherjee <[email protected]> 3 siblings, 1 reply; 9+ messages in thread From: Laurenz Albe @ 2025-09-30 09:47 UTC (permalink / raw) To: Ashish Mukherjee <[email protected]>; Merlin Moncure <[email protected]>; +Cc: pgsql-general On Tue, 2025-09-30 at 13:53 +0530, Ashish Mukherjee wrote: > 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). 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-30 13:12 Ron Johnson <[email protected]> parent: Ashish Mukherjee <[email protected]> 3 siblings, 0 replies; 9+ messages in thread From: Ron Johnson @ 2025-09-30 13:12 UTC (permalink / raw) To: pgsql-general No restoring to unencrypted PG 17? On Tue, Sep 30, 2025 at 4:23 AM Ashish Mukherjee <[email protected]> wrote: > 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 protected]> wrote: > >> On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee < >> [email protected]> 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 >> >> -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-30 14:47 Adrian Klaver <[email protected]> parent: Ashish Mukherjee <[email protected]> 3 siblings, 1 reply; 9+ messages in thread From: Adrian Klaver @ 2025-09-30 14:47 UTC (permalink / raw) To: Ashish Mukherjee <[email protected]>; Merlin Moncure <[email protected]>; +Cc: pgsql-general On 9/30/25 01:23, Ashish Mukherjee wrote: > 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 How did you determine that Percona TDE was the issue vs a 5 version jump in Postgres? > 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). 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 protected] > <mailto:[email protected]>> wrote: -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-09-30 15:13 Merlin Moncure <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Merlin Moncure @ 2025-09-30 15:13 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Ashish Mukherjee <[email protected]>; pgsql-general On Tue, Sep 30, 2025 at 3:47 AM Laurenz Albe <[email protected]> wrote: > On Tue, 2025-09-30 at 13:53 +0530, Ashish Mukherjee wrote: > > 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). > > 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. > 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-10-01 09:33 Ashish Mukherjee <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Ashish Mukherjee @ 2025-10-01 09:33 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Merlin Moncure <[email protected]>; pgsql-general 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. On Tue, Sep 30, 2025 at 8:17 PM Adrian Klaver <[email protected]> wrote: > On 9/30/25 01:23, Ashish Mukherjee wrote: > > 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 > > How did you determine that Percona TDE was the issue vs a 5 version jump > in Postgres? > *I upgraded multiple non TDE databases from v12 to v17 and they are all fine.* > > > > 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). > > 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 protected] > > <mailto:[email protected]>> wrote: > > > > -- > Adrian Klaver > [email protected] > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-10-01 15:23 Adrian Klaver <[email protected]> parent: Ashish Mukherjee <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Adrian Klaver @ 2025-10-01 15:23 UTC (permalink / raw) To: Ashish Mukherjee <[email protected]>; +Cc: Merlin Moncure <[email protected]>; pgsql-general 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. -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Downgrade pgsql 17 to pgsql 12 question @ 2025-10-03 15:15 Pavan Kumar <[email protected]> parent: Ashish Mukherjee <[email protected]> 3 siblings, 0 replies; 9+ messages in thread From: Pavan Kumar @ 2025-10-03 15:15 UTC (permalink / raw) To: Ashish Mukherjee <[email protected]>; +Cc: Merlin Moncure <[email protected]>; pgsql-general 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 On Tue, Sep 30, 2025 at 3:23 AM Ashish Mukherjee <[email protected]> wrote: > 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 protected]> wrote: > >> On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee < >> [email protected]> 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 >> >> -- *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 * ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-10-03 15:15 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-28 23:40 Re: Downgrade pgsql 17 to pgsql 12 question Merlin Moncure <[email protected]> 2025-09-30 08:23 ` Ashish Mukherjee <[email protected]> 2025-09-30 09:47 ` Laurenz Albe <[email protected]> 2025-09-30 15:13 ` Merlin Moncure <[email protected]> 2025-09-30 13:12 ` Ron Johnson <[email protected]> 2025-09-30 14:47 ` Adrian Klaver <[email protected]> 2025-10-01 09:33 ` Ashish Mukherjee <[email protected]> 2025-10-01 15:23 ` Adrian Klaver <[email protected]> 2025-10-03 15:15 ` Pavan Kumar <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox