From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robins Tharakan <tharakan@gmail.com>
cc: Magnus Hagander <magnus@hagander.net>,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
In-reply-to: 
 <CAEP4nAw2WA1wyb9LG7BOEuN3Xr-xWiZZ0w_hKtpyvdUPKmcAJA@mail.gmail.com>
References: <a9f9376f1c3343a6bb319dce294e20ac@EX13D05UWC001.ant.amazon.com>
 <CABUevEwyLb9VE0D+bAQtUnaA7bffXYzBpopYuh7kGTQxY9T5_g@mail.gmail.com>
 <CAEP4nAw2WA1wyb9LG7BOEuN3Xr-xWiZZ0w_hKtpyvdUPKmcAJA@mail.gmail.com>
Comments: In-reply-to Robins Tharakan <tharakan@gmail.com>
	message dated "Tue, 09 Mar 2021 01:13:02 +1100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1742697.1615221182.1@sss.pgh.pa.us>
Date: Mon, 08 Mar 2021 11:33:02 -0500
Message-ID: <1742698.1615221182@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1742698.1615221182%40sss.pgh.pa.us>
Precedence: bulk

Robins Tharakan <tharakan@gmail.com> writes:
> On Mon, 8 Mar 2021 at 23:34, Magnus Hagander <magnus@hagander.net> wrote:
>> Without looking, I would guess it's the schema reload using
>> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known
>> issue in pg_dump/pg_restore. And if that is the case -- perhaps just
>> running all of those in a single transaction would be a better choice?
>> One could argue it's still not a proper fix, because we'd still have a
>> huge memory usage etc, but it would then only burn 1 xid instead of
>> 500M...

> (I hope I am not missing something but) When I tried to force pg_restore to
> use a single transaction (by hacking pg_upgrade's pg_restore call to use
> --single-transaction), it too failed owing to being unable to lock so many
> objects in a single transaction.

It does seem that --single-transaction is a better idea than fiddling with
the transaction wraparound parameters, since the latter is just going to
put off the onset of trouble.  However, we'd have to do something about
the lock consumption.  Would it be sane to have the backend not bother to
take any locks in binary-upgrade mode?

			regards, tom lane