On Sat, Jul 5, 2025 at 3:19 PM Pierre Fortin <p...@pfortin.com> wrote:
> On Sat, 05 Jul 2025 14:30:20 -0400 Tom Lane wrote: > > Forgive my ignorance; always trying to learn more... :) > > >p...@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=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!
