Обсуждение: Downgrade pgsql 17 to pgsql 12 question
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.
The clean way seems to be text file dump and restore but this would be too huge and too slow for our database of 3T. If I use pg_dump v17 and then restore with pg_restore v 17 on a pgsql v12 database, is there any risk? I tried a small test with a bunch of tables and it worked, but am wondering about the pitfalls. I am restoring from the directory format dump.
When I do dump/restore like this for a test table, I get the following errors during restore but the table gets restored fine.
pg_restore: error: while PROCESSING TOC:
error: pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: warning: errors ignored on restore: 2
error: pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
pg_restore: warning: errors ignored on restore: 2
pkgs is the table and s14 is my database
Any input would be appreciated.
Regards,
Ashish
On Fri, 2025-09-26 at 17:48 +0530, Ashish Mukherjee 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. > > The clean way seems to be text file dump and restore but this would be too > huge and too slow for our database of 3T. If I use pg_dump v17 and then > restore with pg_restore v 17 on a pgsql v12 database, is there any risk? Yes, there is the risk that the restore will fail. Downgrading is not supported. That's why the best way to do it is a plain format dump: you can edit the dump file to manually fix any errors. I am surprised that you think that restoring a plain format dump would be significantly slower than restoring a different format (unless you are talking about parallel restore with -j). > I tried a small test with a bunch of tables and it worked, but am wondering > about the pitfalls. I am restoring from the directory format dump. Anything can happen... > When I do dump/restore like this for a test table, I get the following errors > during restore but the table gets restored fine. > > pg_restore: error: while PROCESSING TOC: > error: pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14 > pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14 > pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14 > pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14 > error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14 > pg_restore: warning: errors ignored on restore: 2 > > pkgs is the table and s14 is my database There should be more: the actual error messages. These will give you a clue. Yours, Laurenz Albe
On Fri, Sep 26, 2025 at 10:27 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2025-09-26 at 17:48 +0530, Ashish Mukherjee wrote:
[snip]
> pg_restore: error: while PROCESSING TOC:
> error: pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: warning: errors ignored on restore: 2
>
> pkgs is the table and s14 is my database
There should be more: the actual error messages. These will give you a clue.
Probably in the log_directory log file.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Thank you, Laurenz.
Yes, I say binary dump/restore would be faster because of the -j option.
Well, I suppose there's no certainty of what might break without going through the whole process.
On Fri, Sep 26, 2025 at 7:57 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2025-09-26 at 17:48 +0530, Ashish Mukherjee 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.
>
> The clean way seems to be text file dump and restore but this would be too
> huge and too slow for our database of 3T. If I use pg_dump v17 and then
> restore with pg_restore v 17 on a pgsql v12 database, is there any risk?
Yes, there is the risk that the restore will fail.
Downgrading is not supported.
That's why the best way to do it is a plain format dump: you can edit the
dump file to manually fix any errors.
I am surprised that you think that restoring a plain format dump would
be significantly slower than restoring a different format (unless you are
talking about parallel restore with -j).
> I tried a small test with a bunch of tables and it worked, but am wondering
> about the pitfalls. I am restoring from the directory format dump.
Anything can happen...
> When I do dump/restore like this for a test table, I get the following errors
> during restore but the table gets restored fine.
>
> pg_restore: error: while PROCESSING TOC:
> error: pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: warning: errors ignored on restore: 2
>
> pkgs is the table and s14 is my database
There should be more: the actual error messages. These will give you a clue.
Yours,
Laurenz Albe
On 9/26/25 05:18, Ashish Mukherjee 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. > What are the incompatibilities? Might be easier to deal with those then doing the below. > The clean way seems to be text file dump and restore but this would be > too huge and too slow for our database of 3T. If I use pg_dump v17 and > then restore with pg_restore v 17 on a pgsql v12 database, is there any > risk? I tried a small test with a bunch of tables and it worked, but am > wondering about the pitfalls. I am restoring from the directory format dump. > > When I do dump/restore like this for a test table, I get the following > errors during restore but the table gets restored fine. > Any input would be appreciated. > > Regards, > Ashish -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 9/26/25 05:18, Ashish Mukherjee 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.
> What are the incompatibilities?
> Might be easier to deal with those then doing the below.
Yeah ... sitting on a PG release that's already a year beyond EOL
is not a sustainable practice. You should consult the last several
release notes for v13 and reflect on the likelihood that most of those
bugs also exist in v12.
regards, tom lane
Ashish Mukherjee <ashish.mukherjee@gmail.com> writes:
> When I do dump/restore like this for a test table, I get the following
> errors during restore but the table gets restored fine.
> pg_restore: error: while PROCESSING TOC:
> error: pg_restore: error: pg_restore: from TOC entry 17168; 1259
> 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: pg_restore: pg_restore: from TOC entry
> 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE
> pkgs s14
> pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TABLE
> pkgs s14
> error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: warning: errors ignored on restore: 2
FWIW, the only output I see when trying a simple restore of a couple
of tables from HEAD into a v12 server is
pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
pg_restore: warning: errors ignored on restore: 1
which is expected since v12 didn't have transaction_timeout.
(If you use parallel mode you will see this error per-worker.)
This means you can't use --single-transaction, but it should
work otherwise as long as you're not using DDL features v12
didn't have.
So I'm wondering why your output doesn't show that, and also
why it fails to show the text of whatever error you are getting.
Can you make a reproducible test case that behaves like that?
regards, tom lane
On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <ashish.mukherjee@gmail.com> 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
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 <mmoncure@gmail.com> wrote:
On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <ashish.mukherjee@gmail.com> 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
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
No restoring to unencrypted PG 17?
On Tue, Sep 30, 2025 at 4:23 AM Ashish Mukherjee <ashish.mukherjee@gmail.com> 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 <mmoncure@gmail.com> wrote:On Fri, Sep 26, 2025 at 8:16 AM Ashish Mukherjee <ashish.mukherjee@gmail.com> 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!
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 <mmoncure@gmail.com > <mailto:mmoncure@gmail.com>> wrote: -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Sep 30, 2025 at 3:47 AM Laurenz Albe <laurenz.albe@cybertec.at> 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
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 <adrian.klaver@aklaver.com> 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 <mmoncure@gmail.com
> <mailto:mmoncure@gmail.com>> wrote:
--
Adrian Klaver
adrian.klaver@aklaver.com
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 adrian.klaver@aklaver.com