Date: Sat, 5 Jul 2025 15:19:28 -0400
Feedback-ID: :pf@pfortin.com:pfortin.com:flockmailId
From: Pierre Fortin <pf@pfortin.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade: can I use same binary for old & new?
Message-ID: <20250705151928.3a9ea7b1@pfortin.com>
In-Reply-To: <65041.1751740220@sss.pgh.pa.us>
References: <20250705125207.31b4d475@pfortin.com>
	<b558441b-0bd8-4cfb-aa4c-64fdcb530531@aklaver.com>
	<20250705142416.06e99667@pfortin.com>
	<65041.1751740220@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250705151928.3a9ea7b1%40pfortin.com>
Precedence: bulk

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. 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