MIME-Version: 1.0
References: <20250705125207.31b4d475@pfortin.com> <b558441b-0bd8-4cfb-aa4c-64fdcb530531@aklaver.com>
 <20250705142416.06e99667@pfortin.com> <65041.1751740220@sss.pgh.pa.us> <20250705151928.3a9ea7b1@pfortin.com>
In-Reply-To: <20250705151928.3a9ea7b1@pfortin.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 5 Jul 2025 15:29:03 -0400
Message-ID: <CANzqJaBf6z1oVbbqP+knq=kc_as64Wb9nWBY47BX0Ohvi-zTgg@mail.gmail.com>
Subject: Re: pg_upgrade: can I use same binary for old & new?
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003cbc1d063933a0fb"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBf6z1oVbbqP%2Bknq%3Dkc_as64Wb9nWBY47BX0Ohvi-zTgg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jul 5, 2025 at 3:19=E2=80=AFPM Pierre Fortin <pf@pfortin.com> wrote=
:

> On Sat, 05 Jul 2025 14:30:20 -0400 Tom Lane wrote:
>
> Forgive my ignorance; always trying to learn more... :)
>
> >pf@pfortin.com writes:
> >> On Sat, 5 Jul 2025 11:11:32 -0700 Adrian Klaver wrote:
> >>> How did you measure above?
> >
> >> # du -sb /var/lib/pgsql/data
> >> 8227910662297   /var/lib/pgsql/data
> >
> >It's likely that there's a deal of bloat in that.  Even if there's not
> >much bloat, this number will include indexes and WAL data that don't
> >appear in pg_dump output.
>
> Does this imply that on restore, I'll have to re-index everything?
>
> >>> What was the pg_dump command?
> >
> >> Didn't try given:
> >> $ df /mnt/db
> >> Filesystem      Size  Used Avail Use% Mounted on
> >> /dev/sdh1        17T   13T  3.0T  82% /mnt/db
> >
> >I'd say give it a try; be sure to use one of the pg_dump modes
> >that compress the data.
>
> OK...  I failed to mention I have several databases in this cluster; so
> digging into pg_dumpall, I see:
>    --binary-upgrade
>     This option is for use by in-place upgrade utilities. Its use for
>     other purposes is not recommended or supported. The behavior of the
>     option may change in future releases without notice.
>
> pg_upgrade has --link option; but I'm puzzled by this option in a
> dumpall/restore process.


It's _not_ part of a dumpall/restore process.

You _either_ run
- pg_upgrade --link
  OR
- pg_dumpall --globals-only > globals.sql / psql -f globals.sql
- pg_dump --format=3Ddirectory / pg_restore --format=3Ddirectory of db1
- pg_dump --format=3Ddirectory / pg_restore --format=3Ddirectory of db2
- pg_dump --format=3Ddirectory / pg_restore --format=3Ddirectory of db3
- pg_dump --format=3Ddirectory / pg_restore --format=3Ddirectory of etc...

Why not a plain pg_dumpall of the whole instance?  Because that would
create a GINORMOUS text file which can only be loaded in a single-threaded
manner.


> My imagination wonders if this alludes to a way
> to do something like:
>  pg_dumpall --globals-only --roles-only --schema-only ...
> Would restoring this be a way to update only the control structures? Big
> assumption that the actual data remains untouched...
>
> Inquiring mind...  :)
>
> Back to my upgrade issue...
> All my DBs are static (only queries once loaded). Assuming the dumpall
> file fits on one of my drives:
>  pg_dumpall -f <path>/PG.backup -v
> appears to be all I need? pg_dump has compression by default; but I don't
> see compression with dumpall other than for TOAST.
>
> Thanks, You guys are awesome!
>
> >                       regards, tom lane
>
>
>

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jul 5, 2025 at 3:19=E2=80=AFPM Pi=
erre Fortin &lt;<a href=3D"mailto:pf@pfortin.com">pf@pfortin.com</a>&gt; wr=
ote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">On Sat, 05 Jul 2025 14:30:20 -0400 Tom La=
ne wrote:<br>
<br>
Forgive my ignorance; always trying to learn more... :)<br>
<br>
&gt;<a href=3D"mailto:pf@pfortin.com" target=3D"_blank">pf@pfortin.com</a> =
writes:<br>
&gt;&gt; On Sat, 5 Jul 2025 11:11:32 -0700 Adrian Klaver wrote:=C2=A0 <br>
&gt;&gt;&gt; How did you measure above?=C2=A0 <br>
&gt;<br>
&gt;&gt; # du -sb /var/lib/pgsql/data<br>
&gt;&gt; 8227910662297=C2=A0 =C2=A0/var/lib/pgsql/data=C2=A0 <br>
&gt;<br>
&gt;It&#39;s likely that there&#39;s a deal of bloat in that.=C2=A0 Even if=
 there&#39;s not<br>
&gt;much bloat, this number will include indexes and WAL data that don&#39;=
t<br>
&gt;appear in pg_dump output.<br>
<br>
Does this imply that on restore, I&#39;ll have to re-index everything?<br>
<br>
&gt;&gt;&gt; What was the pg_dump command?=C2=A0 <br>
&gt;<br>
&gt;&gt; Didn&#39;t try given:<br>
&gt;&gt; $ df /mnt/db<br>
&gt;&gt; Filesystem=C2=A0 =C2=A0 =C2=A0 Size=C2=A0 Used Avail Use% Mounted =
on<br>
&gt;&gt; /dev/sdh1=C2=A0 =C2=A0 =C2=A0 =C2=A0 17T=C2=A0 =C2=A013T=C2=A0 3.0=
T=C2=A0 82% /mnt/db=C2=A0 <br>
&gt;<br>
&gt;I&#39;d say give it a try; be sure to use one of the pg_dump modes<br>
&gt;that compress the data.<br>
<br>
OK...=C2=A0 I failed to mention I have several databases in this cluster; s=
o<br>
digging into pg_dumpall, I see:<br>
=C2=A0 =C2=A0--binary-upgrade<br>
=C2=A0 =C2=A0 This option is for use by in-place upgrade utilities. Its use=
 for<br>
=C2=A0 =C2=A0 other purposes is not recommended or supported. The behavior =
of the<br>
=C2=A0 =C2=A0 option may change in future releases without notice.<br>
<br>
pg_upgrade has --link option; but I&#39;m puzzled by this option in a<br>
dumpall/restore process.</blockquote><div><br></div><div>It&#39;s _not_ par=
t of a dumpall/restore process.</div><div><br></div><div>You _either_ run</=
div><div>- pg_upgrade --link</div><div>=C2=A0 OR</div><div>- pg_dumpall --g=
lobals-only &gt; globals.sql / psql -f globals.sql</div><div><div>- pg_dump=
 --format=3Ddirectory / pg_restore=C2=A0--format=3Ddirectory=C2=A0of db1</d=
iv><div><div>- pg_dump --format=3Ddirectory / pg_restore=C2=A0--format=3Ddi=
rectory=C2=A0of db2</div><div><div>- pg_dump --format=3Ddirectory / pg_rest=
ore=C2=A0--format=3Ddirectory=C2=A0of db3</div></div><div><div>- pg_dump --=
format=3Ddirectory / pg_restore=C2=A0--format=3Ddirectory=C2=A0of etc...</d=
iv></div><div><br></div></div></div><div>Why not a plain pg_dumpall of the =
whole instance?=C2=A0 Because that would create a GINORMOUS text file which=
 can only be loaded in a single-threaded manner.=C2=A0</div><div>=C2=A0</di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"> My imagination wonders=
 if this alludes to a way<br>
to do something like:<br>
=C2=A0pg_dumpall --globals-only --roles-only --schema-only ...<br>
Would restoring this be a way to update only the control structures? Big<br=
>
assumption that the actual data remains untouched...<br>
<br>
Inquiring mind...=C2=A0 :)<br>
<br>
Back to my upgrade issue...=C2=A0 <br>
All my DBs are static (only queries once loaded). Assuming the dumpall<br>
file fits on one of my drives:<br>
=C2=A0pg_dumpall -f &lt;path&gt;/PG.backup -v <br>
appears to be all I need? pg_dump has compression by default; but I don&#39=
;t<br>
see compression with dumpall other than for TOAST. <br>
<br>
Thanks, You guys are awesome!<br>
<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0regards, tom lane<br>
<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--0000000000003cbc1d063933a0fb--