RE: pg_upgrade failing for 200+ million Large Objects

Hi all,

Attached is a proof-of-concept patch that allows Postgres to perform
pg_upgrade if the instance has Millions of objects.

It would be great if someone could take a look and see if this patch is in
the right direction. There are some pending tasks (such as documentation /
pg_resetxlog vs pg_resetwal related changes) but for now, the patch helps
remove a stalemate where if a Postgres instance has a large number
(accurately speaking 146+ Million) of Large Objects, pg_upgrade fails. This
is easily reproducible and besides deleting Large Objects before upgrade,
there is no other (apparent) way for pg_upgrade to complete.

The patch (attached):
- Applies cleanly on REL9_6_STABLE -
c7a4fc3dd001646d5938687ad59ab84545d5d043
- 'make check' passes
- Allows the user to provide a constant via pg_upgrade command-line, that
overrides the 2 billion constant in pg_resetxlog [1] thereby increasing the
(window of) Transaction IDs available for pg_upgrade to complete.

Sample argument for pg_upgrade:
$ /opt/postgres/96/bin/pg_upgrade --max-limit-xid 1000000000 --old-bindir
...

With this patch, pg_upgrade is now able to upgrade a v9.5 cluster with 500
million Large Objects successfully to v9.6 - some stats below:

Source Postgres - v9.5.24
Target Version - v9.6.21
Large Object Count: 500 Million Large Objects
Machine - r5.4xlarge (16vCPU / 128GB RAM + 1TB swap)
Memory used during pg_upgrade - ~350GB
Time taken - 25+ hrs. (tested twice) - (All LOs processed sequentially ->
Scope for optimization)

Although counter-intuitive, for this testing purpose all Large Objects were
small (essentially the idea was to test the count) and created by using
something like this:

seq 1 50000 | xargs -n 1 -i -P 10 /opt/postgres/95/bin/psql -c "select
lo_from_bytea(0, '\xffffff00') from generate_series(1,10000);" > /dev/null

I am not married to the patch (especially the argument name) but ideally I'd
prefer a way to get this upgrade going without a patch. For now, I am unable
to find any other way to upgrade a v9.5 Postgres database in this scenario,
facing End-of-Life.

Reference:
1) 2 Billion constant -
https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211
a88f43/src/bin/pg_resetwal/pg_resetwal.c#L444

Thanks,
Robins Tharakan

> -----Original Message-----
> From: Tharakan, Robins
> Sent: Wednesday, 3 March 2021 10:36 PM
> To: pgsql-hackers(at)postgresql(dot)org
> Subject: pg_upgrade failing for 200+ million Large Objects
>
> Hi,
>
> While reviewing a failed upgrade from Postgres v9.5 (to v9.6) I saw that
> the
> instance had ~200 million (in-use) Large Objects. I was able to reproduce
> this on a test instance which too fails with a similar error.
>
>
> pg_restore: executing BLOB 4980622
> pg_restore: WARNING: database with OID 0 must be vacuumed within 1000001
> transactions
> HINT: To avoid a database shutdown, execute a database-wide VACUUM in
> that
> database.
> You might also need to commit or roll back old prepared transactions.
> pg_restore: executing BLOB 4980623
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2565; 2613 4980623 BLOB
> 4980623 postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: database is
> not
> accepting commands to avoid wraparound data loss in database with OID 0
> HINT: Stop the postmaster and vacuum that database in single-user mode.
> You might also need to commit or roll back old prepared transactions.
> Command was: SELECT pg_catalog.lo_create('4980623');
>
>
>
> To remove the obvious possibilities, these Large Objects that are still
> in-use (so vacuumlo wouldn't help), giving more system resources doesn't
> help, moving Large Objects around to another database doesn't help (since
> this is cluster-wide restriction), the source instance is nowhere close
> to
> wraparound and lastly recent-most minor versions don't help either (I
> tried
> compiling 9_6_STABLE + upgrade database with 150 million LO and still
> encountered the same issue).
>
> Do let me know if I am missing something obvious but it appears that this
> is
> happening owing to 2 things coming together:
>
> * Each Large Object is migrated in its own transaction during pg_upgrade
> * pg_resetxlog appears to be narrowing the window (available for
> pg_upgrade)
> to ~146 Million XIDs (2^31 - 1 million XID wraparound margin - 2 billion
> which is a hard-coded constant - see [1] - in what appears to be an
> attempt
> to force an Autovacuum Wraparound session soon after upgrade completes).
>
> Ideally such an XID based restriction, is limiting for an instance that's
> actively using a lot of Large Objects. Besides forcing AutoVacuum
> Wraparound
> logic to kick in soon after, I am unclear what much else it aims to do.
> What
> it does seem to be doing is to block Major Version upgrades if the
> pre-upgrade instance has >146 Million Large Objects (half that, if the LO
> additionally requires ALTER LARGE OBJECT OWNER TO for each of those
> objects
> during pg_restore)
>
> For long-term these ideas came to mind, although am unsure which are
> low-hanging fruits and which outright impossible - For e.g. clubbing
> multiple objects in a transaction [2] / Force AutoVacuum post upgrade
> (and
> thus remove this limitation altogether) or see if "pg_resetxlog -x" (from
> within pg_upgrade) could help in some way to work-around this limitation.
>
> Is there a short-term recommendation for this scenario?
>
> I can understand a high number of small-sized objects is not a great way
> to
> use pg_largeobject (since Large Objects was intended to be for, well,
> 'large
> objects') but this magic number of Large Objects is now a stalemate at
> this
> point (with respect to v9.5 EOL).
>
>
> Reference:
> 1) pg_resetxlog -
> https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a
> 211
> a88f43/src/bin/pg_resetwal/pg_resetwal.c#L444
> 2)
> https://www.postgresql.org/message-id/ed7d86a1-b907-4f53-9f6e-
> 63482d2f2bac%4
> 0manitou-mail.org
>
> -
> Thanks
> Robins Tharakan