From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Wieck <jan@wi3ck.info>
cc: Magnus Hagander <magnus@hagander.net>,
        Robins Tharakan <tharakan@gmail.com>,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Fix pg_upgrade to preserve datdba
In-reply-to: <ea5bebf7-683e-f183-ebd7-98d9a552e18c@wi3ck.info>
References: <a9f9376f1c3343a6bb319dce294e20ac@EX13D05UWC001.ant.amazon.com>
 <CABUevEwyLb9VE0D+bAQtUnaA7bffXYzBpopYuh7kGTQxY9T5_g@mail.gmail.com>
 <CAEP4nAw2WA1wyb9LG7BOEuN3Xr-xWiZZ0w_hKtpyvdUPKmcAJA@mail.gmail.com>
 <1742698.1615221182@sss.pgh.pa.us>
 <CABUevEzvU07CqwGdaOmxNfDtrkY-xEcLjiN3GAmurowyCnbG7w@mail.gmail.com>
 <1743618.1615222719@sss.pgh.pa.us>
 <4fbf92f2-ec91-f4fa-a259-f0968e34f3d7@wi3ck.info>
 <0b5d506d-70d2-1506-bbdf-89e6453c0289@wi3ck.info>
 <384713.1616345832@sss.pgh.pa.us>
 <2284b71d-af55-2625-f8c2-cedf1f1a70ca@wi3ck.info>
 <ff77882d-1b1f-1735-402a-f4583f1b2f58@wi3ck.info>
 <388155.1616351018@sss.pgh.pa.us> <388710.1616351698@sss.pgh.pa.us>
 <ea5bebf7-683e-f183-ebd7-98d9a552e18c@wi3ck.info>
Comments: In-reply-to Jan Wieck <jan@wi3ck.info>
	message dated "Sun, 21 Mar 2021 15:36:51 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <399566.1616356574.1@sss.pgh.pa.us>
Date: Sun, 21 Mar 2021 15:56:14 -0400
Message-ID: <399567.1616356574@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/399567.1616356574%40sss.pgh.pa.us>
Precedence: bulk

Jan Wieck <jan@wi3ck.info> writes:
> So let's focus on the actual problem of running out of XIDs and memory 
> while doing the upgrade involving millions of small large objects.

Right.  So as far as --single-transaction vs. --create goes, that's
mostly a definitional problem.  As long as the contents of a DB are
restored in one transaction, it's not gonna matter if we eat one or
two more XIDs while creating the DB itself.  So we could either
relax pg_restore's complaint, or invent a different switch that's
named to acknowledge that it's not really only one transaction.

That still leaves us with the lots-o-locks problem.  However, once
we've crossed the Rubicon of "it's not really only one transaction",
you could imagine that the switch is "--fewer-transactions", and the
idea is for pg_restore to commit after every (say) 100000 operations.
That would both bound its lock requirements and greatly cut its XID
consumption.

The work you described sounded like it could fit into that paradigm,
with the additional ability to run some parallel restore tasks
that are each consuming a bounded number of locks.

			regards, tom lane