Feedback-ID: i76984098:Fastmail
Message-ID: <b38050fe-49b0-430a-8599-948a4470ad26@aklaver.com>
Date: Sat, 5 Jul 2025 13:23:18 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_upgrade: can I use same binary for old & new?
To: Pierre Fortin <pf@pfortin.com>, pgsql-general@lists.postgresql.org
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20250705151928.3a9ea7b1@pfortin.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b38050fe-49b0-430a-8599-948a4470ad26%40aklaver.com>
Precedence: bulk

On 7/5/25 12:19, Pierre Fortin wrote:
> On Sat, 05 Jul 2025 14:30:20 -0400 Tom Lane wrote:
> 

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

If you really want to use pg_dumpall and get compression then something 
like:

pg_dumpall -U postgres  | gzip  >  pg_backup.gz

Though this will take some time and really is probably better handled using:

pg_dumpall -U postgres -g > pg_globals.sql

and then:

pg_dump -d <some_db> -U -Fc -f <some_db>.out

for each database. This will use compression by default.

Neither of these options will be as quick as doing pg_upgrade with 
--link. Though at this point you are boxed in by not being able to run 
multiple Postgres versions on one machine.


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

-- 
Adrian Klaver
adrian.klaver@aklaver.com