public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: pg_upgrade: can I use same binary for old & new?
Date: Sat, 5 Jul 2025 15:29:03 -0400
Message-ID: <CANzqJaBf6z1oVbbqP+knq=kc_as64Wb9nWBY47BX0Ohvi-zTgg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Sat, Jul 5, 2025 at 3:19 PM Pierre Fortin <[email protected]> wrote:
> On Sat, 05 Jul 2025 14:30:20 -0400 Tom Lane wrote:
>
> Forgive my ignorance; always trying to learn more... :)
>
> >[email protected] 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=directory / pg_restore --format=directory of db1
- pg_dump --format=directory / pg_restore --format=directory of db2
- pg_dump --format=directory / pg_restore --format=directory of db3
- pg_dump --format=directory / pg_restore --format=directory 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
>
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pg_upgrade: can I use same binary for old & new?
In-Reply-To: <CANzqJaBf6z1oVbbqP+knq=kc_as64Wb9nWBY47BX0Ohvi-zTgg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox