Message-ID: <d5f3546b99d7d810bc3d50ae0c16e7297a18f34e.camel@cybertec.at>
Subject: Re: Downgrade pgsql 17 to pgsql 12 question
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ashish Mukherjee <ashish.mukherjee@gmail.com>, 
	pgsql-general@postgresql.org
Date: Fri, 26 Sep 2025 16:27:33 +0200
In-Reply-To: <CACgMzfxO7h+Bj4T8rjTV=trM3+Uw=jOAcUE+0Kimv4DSAVU1JA@mail.gmail.com>
References: 
	<CACgMzfxO7h+Bj4T8rjTV=trM3+Uw=jOAcUE+0Kimv4DSAVU1JA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/d5f3546b99d7d810bc3d50ae0c16e7297a18f34e.camel%40cybertec.at>
Precedence: bulk

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=C2=A0between bot=
h versions
> for our database. It should have been caught in testing but was not.
>=20
> The clean way seems to be text file dump and restore but this would be to=
o
> 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 wonderi=
ng
> 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 er=
rors
> during restore but the table gets restored fine.
>=20
> pg_restore: error: while PROCESSING TOC:
> =C2=A0error: pg_restore: =C2=A0error: =C2=A0 pg_restore: =C2=A0from TOC e=
ntry 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: error: pg_restore: pg_restore: pg_restore: =C2=A0 from TOC en=
try 17168; 1259 58572315 TABLE pkgs s14
> pg_restore: =C2=A0 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
>=20
> pkgs is the table and s14 is my database

There should be more: the actual error messages.  These will give you a clu=
e.

Yours,
Laurenz Albe