Subject: Downgrade pgsql 17 to pgsql 12 question

From: Ashish Mukherjee <[email-masked]>
Date: Fri, 26 Sep 2025 17:48:07 +0530

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

pkgs is the table and s14 is my database

Any input would be appreciated.

Regards,
Ashish

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 26 Sep 2025 16:27:33 +0200

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.
> 
> 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

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.
> 
>  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

There should be more: the actual error messages.  These will give you a clue.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Fri, 26 Sep 2025 10:39:37 -0400

> On Fri, 2025-09-26 at 17:48 +0530, Ashish Mukherjee wrote:
[snip]

> There should be more: the actual error messages.  These will give you a

Probably in the log_directory log file.

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

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 26 Sep 2025 08:10:03 -0700

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.
> 

-- 
Adrian Klaver
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Fri, 26 Sep 2025 11:13:57 -0400

> 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