MIME-Version: 1.0
References: <CACgMzfxO7h+Bj4T8rjTV=trM3+Uw=jOAcUE+0Kimv4DSAVU1JA@mail.gmail.com>
 <d5f3546b99d7d810bc3d50ae0c16e7297a18f34e.camel@cybertec.at>
In-Reply-To: <d5f3546b99d7d810bc3d50ae0c16e7297a18f34e.camel@cybertec.at>
From: Ashish Mukherjee <ashish.mukherjee@gmail.com>
Date: Fri, 26 Sep 2025 20:17:12 +0530
Message-ID: <CACgMzfyYg=nuGcsLT8QPsPMTjoh-uzAo2u+F6F5ivWC4pq0yBQ@mail.gmail.com>
Subject: Re: Downgrade pgsql 17 to pgsql 12 question
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003220a0063fb55ddf"
Archived-At: <https://www.postgresql.org/message-id/CACgMzfyYg%3DnuGcsLT8QPsPMTjoh-uzAo2u%2BF6F5ivWC4pq0yBQ%40mail.gmail.com>
Precedence: bulk

--0000000000003220a0063fb55ddf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM 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 TABL=
E
> 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
>

--0000000000003220a0063fb55ddf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you, Laurenz.=C2=A0<div><br></div><div>Yes, I say bi=
nary dump/restore would be faster because of the -j option.</div><div><br><=
/div><div>Well, I suppose there&#39;s no certainty of what might break with=
out going through the whole process.</div></div><br><div class=3D"gmail_quo=
te gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Sep=
 26, 2025 at 7:57=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laurenz.alb=
e@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex">On Fri, 2025-09-26 at 17:48 +0530, A=
shish Mukherjee wrote:<br>
&gt; I have a strange requirement to downgrade from pgsql 17 to pgsql 12. T=
his is<br>
&gt; because we found in production certain incompatibilities=C2=A0between =
both versions<br>
&gt; for our database. It should have been caught in testing but was not.<b=
r>
&gt; <br>
&gt; The clean way seems to be text file dump and restore but this would be=
 too<br>
&gt; huge and too slow for our database of 3T. If I use pg_dump v17 and the=
n<br>
&gt; restore with pg_restore v 17 on a pgsql v12 database, is there any ris=
k?<br>
<br>
Yes, there is the risk that the restore will fail.<br>
Downgrading is not supported.<br>
<br>
That&#39;s why the best way to do it is a plain format dump: you can edit t=
he<br>
dump file to manually fix any errors.<br>
I am surprised that you think that restoring a plain format dump would<br>
be significantly slower than restoring a different format (unless you are<b=
r>
talking about parallel restore with -j).<br>
<br>
&gt; I tried a small test with a bunch of tables and it worked, but am wond=
ering<br>
&gt; about the pitfalls. I am restoring from the directory format dump.<br>
<br>
Anything can happen...<br>
<br>
&gt; When I do dump/restore like this for a test table, I get the following=
 errors<br>
&gt; during restore but the table gets restored fine.<br>
&gt; <br>
&gt; pg_restore: error: while PROCESSING TOC:<br>
&gt; =C2=A0error: pg_restore: =C2=A0error: =C2=A0 pg_restore: =C2=A0from TO=
C entry 17168; 1259 58572315 TABLE pkgs s14<br>
&gt; pg_restore: error: pg_restore: pg_restore: pg_restore: =C2=A0 from TOC=
 entry 17168; 1259 58572315 TABLE pkgs s14<br>
&gt; pg_restore: =C2=A0 error: pg_restore: from TOC entry 17168; 1259 58572=
315 TABLE pkgs s14<br>
&gt; pg_restore: error: pg_restore: from TOC entry 17168; 1259 58572315 TAB=
LE pkgs s14<br>
&gt; error: from TOC entry 17168; 1259 58572315 TABLE pkgs s14<br>
&gt; pg_restore: warning: errors ignored on restore: 2<br>
&gt; <br>
&gt; pkgs is the table and s14 is my database<br>
<br>
There should be more: the actual error messages.=C2=A0 These will give you =
a clue.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--0000000000003220a0063fb55ddf--