From: "Tharakan, Robins" <tharar@amazon.com>
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: pg_upgrade failing for 200+ million Large Objects
Thread-Topic: pg_upgrade failing for 200+ million Large Objects
Thread-Index: AdcQGHPbpt3tdBnIQ6apvAiQCfVXxw==
Date: Wed, 3 Mar 2021 11:36:26 +0000
Message-ID: <12601596dbbc4c01b86b4ac4d2bd4d48@EX13D05UWC001.ant.amazon.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=SHA1; boundary="----=_NextPart_000_009B_01D7107D.763992C0"
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/12601596dbbc4c01b86b4ac4d2bd4d48%40EX13D05UWC001.ant.amazon.com>
Precedence: bulk

------=_NextPart_000_009B_01D7107D.763992C0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

------=_NextPart_000_009B_01D7107D.763992C0
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIQATCCBA8w
ggL3oAMCAQICEFeHFPCN4dmrSiV65deu/iEwDQYJKoZIhvcNAQEFBQAwOTE3MDUGA1UEAxMuQW1h
em9uLmNvbSBJbnRlcm5hbCBSb290IENlcnRpZmljYXRlIEF1dGhvcml0eTAeFw0wNzA4MzAxODAy
MjVaFw0yNzA4MzAxODEwNTlaMDkxNzA1BgNVBAMTLkFtYXpvbi5jb20gSW50ZXJuYWwgUm9vdCBD
ZXJ0aWZpY2F0ZSBBdXRob3JpdHkwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDugYdG
nDajFgUbfr6ek3APnSiy6nHUWUEbvxKPFc4eiWaWcGBge4JTiamxVmZu3yFh86klFGYBxixwJ32z
oD56NfWlubhv2feMQFxxKKOiK3dZSIDLbGqCMvILDW/hYHLGL6/rFDFPYbSbs7mJLxFBmWdyCFvf
pDFEMDcPVOFNw4EEQL7TgmPn5loWvtgkSAue5EIgpEcKwy86ylpvzq/Oj/GEWqD8sXAUnhWLgSm6
r1jsAKlk1tmaKsaWBjMC4fiSg8Zq1JI/CQ+FckZ5nHkiCBztzWEYp1m7uBQBBcZ/+l2qdz93u/7f
DxmyICIE4enGr5xTWSv9MDNwQQd9YLh9AgMBAAGjggERMIIBDTALBgNVHQ8EBAMCAYYwDwYDVR0T
AQH/BAUwAwEB/zAdBgNVHQ4EFgQUf42xTkyimArciye/YgVpPCUSs8IwEAYJKwYBBAGCNxUBBAMC
AQAwgbsGA1UdIASBszCBsDCBrQYMKwYBBAGla4FIAQEBMIGcMHIGCCsGAQUFBwICMGYeZABBAG0A
YQB6AG8AbgAuAGMAbwBtACAASQBuAHQAZQByAG4AYQBsACAAQwBlAHIAdABpAGYAaQBjAGEAdABl
ACAAUAByAGEAYwB0AGkAYwBlACAAUwB0AGEAdABlAG0AZQBuAHQwJgYIKwYBBQUHAgEWGmh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcHMvMA0GCSqGSIb3DQEBBQUAA4IBAQB0AW6ePZaQ83mcE9Etduc1
aSp4nvLUoJ2NAI1v4UDB3A0iBggNo9XfEsfin/tJoXkWuHxtB5ucZNAW3ZletXQfW3DAbWVr5kAZ
Tv4h/u/9OqAVZCOuxYMUZqfwJiPybm4xj9dnll6F9mF7Ur5I7D+PX+MmuJNsEza3MqcJaxcefrI5
0XTn9uCMgxo6/x56LqWD46AxrYBe4YjF8FQ9VBRz4S1cS0KI7mA40i5dyOc2nmnET6e+iIQPGHzQ
iTuereCRhGybLkKi3yChe4Uw6KqQ4qSVVAYf1nJjrDYk3RUHHF95JcWCHyTh4safTHcTETNWx8F9
MWWlF96gZ4B8/ORlMIIFjjCCBHagAwIBAgIKYRwhegAAAAAAJjANBgkqhkiG9w0BAQsFADA5MTcw
NQYDVQQDEy5BbWF6b24uY29tIEludGVybmFsIFJvb3QgQ2VydGlmaWNhdGUgQXV0aG9yaXR5MB4X
DTE4MTEwNjIzMzExOFoXDTIzMTEwNjIzNDExOFowZDETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQG
CgmSJomT8ixkARkWBmFtYXpvbjETMBEGCgmSJomT8ixkARkWA2FudDEgMB4GA1UEAxMXQW1hem9u
LmNvbSBDSUEgQ0EgRzQgMDEwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQDBNKNhXeYZ
RggQpGc194zGplBxDWOWYsMY7jc4EkhplrCkkPWqcfCuJh0SLJpcbZwin70hnu3DXwmt6N5nZu7c
Yd1h40YL/p6zULMebqn0p9AwUMK+8qV514J79VjioN/GE7h5G8ZkyjUjmqeBtTczJUifwAJ72n6b
wSFLLZS28Nar4blskiGdX6ZaB+N3nNp4DN+RzCqfFc15Ta9iVIj50ChknahX8Ia4Hjw3azNKWJl1
JGTsZIETr9N/m6ta1LMjuAMpJCqwCx8Ndbkml+cjyul6T5MMHNMBYCeABm7P0HWcSoinZbK7qNJO
TZDfP6NWFm/6VgSr/U/WqMJKZrN9Gb3KEr3kVBDMG07Al7YM6nwzLG60P3oPodOUW5MSU+zpN3Fa
vqWsopJ5uBBk26YFpn1+8CJnuwwT4VjQtRF6SQk04fNpowHxOtgrVlIr7Wuf4bD3eMU6A8mbSZyp
9SBHxR1n8GLO8o3/IQhC75AawgBTm04X+K9eQdoaLdk5rNxxMHJ+y15fwZpab5WfpBNN71Qi/fWO
/hoimZ1ytSMHSMmAnf4Q/jQC7GLXTIRikjR4f+KLSPrWu17h4Cqv2ehbHlOS8tJG866cViWlm9pc
JNfz1QOpgoPn1fT6UpoVh40YsigC/qJktW3HmyCWOzXU0u7H61BpgxJBZS7AMvBSqQIDAQABo4IB
azCCAWcwEAYJKwYBBAGCNxUBBAMCAQAwHQYDVR0OBBYEFOAp5m6qUdZa9admS/aG9DXnqw+rMBkG
CSsGAQQBgjcUAgQMHgoAUwB1AGIAQwBBMAsGA1UdDwQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgEA
MB8GA1UdIwQYMBaAFH+NsU5MopgK3Isnv2IFaTwlErPCMGUGA1UdHwReMFwwWqBYoFaGVGh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcmwvQW1hem9uLmNvbSUyMEludGVybmFsJTIwUm9vdCUyMENlcnRp
ZmljYXRlJTIwQXV0aG9yaXR5LmNybDBwBggrBgEFBQcBAQRkMGIwYAYIKwYBBQUHMAKGVGh0dHA6
Ly9wa2kuYW1hem9uLmNvbS9jcnQvQW1hem9uLmNvbSUyMEludGVybmFsJTIwUm9vdCUyMENlcnRp
ZmljYXRlJTIwQXV0aG9yaXR5LmNydDANBgkqhkiG9w0BAQsFAAOCAQEAFY6kjPmVGLYIPhHlDX+f
HshBGUEoGXAPbtNLg+WV7vvpu5dT3vgNsAFb9tjfa1j46D0ZppFMtxpM6GHGP89yqB4r/6+R3dMg
kwd5uinR22ExYISRwS4Aw8f6KwS4xK1SMnVGNWj2PhYrUyLRqPmRK/FviIgbCR55tPEZrDtsvaVW
GMlfpLFa3WIozj8Zkd7Q0Wn7H3+SRWwcSnf88bIbGyllr2Hr6QzC2tJlaD2VxcZdPqFxRkA7as3X
RQ2u/GnavSdbs/oTh5UO7mEMNASHfyl2pAgpGyLDy2ab8F5FjihnNCxHaKP9AXVrkm6ncqsYAHdG
ipZ3WqWrIosMV6m+VjCCBlgwggRAoAMCAQICE2gAyK8og25ErglxdPcAAADIrygwDQYJKoZIhvcN
AQELBQAwZDETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQGCgmSJomT8ixkARkWBmFtYXpvbjETMBEG
CgmSJomT8ixkARkWA2FudDEgMB4GA1UEAxMXQW1hem9uLmNvbSBDSUEgQ0EgRzQgMDEwHhcNMjAx
MTEzMDczNDQ1WhcNMjExMTEzMDczNDQ1WjCBtzETMBEGCgmSJomT8ixkARkWA2NvbTEWMBQGCgmS
JomT8ixkARkWBmFtYXpvbjETMBEGCgmSJomT8ixkARkWA2FudDEMMAoGA1UECxMDT1JHMRIwEAYD
VQQLEwlBdXN0cmFsaWExDjAMBgNVBAsTBVNZRDEwMQ4wDAYDVQQLEwVVc2VyczEPMA0GA1UEAxMG
dGhhcmFyMSAwHgYJKoZIhvcNAQkBFhF0aGFyYXJAYW1hem9uLmNvbTCCASIwDQYJKoZIhvcNAQEB
BQADggEPADCCAQoCggEBAPXXLe5ApZHjG7dzdJANAxxaqxU2yp0rcBOtQEWdjIDVxTkI6AiSyEMe
0yaQMy0oaWAzfennPaRwzduDwIw6B91hsFqP3bE0QDcc6GUgRfW8WfAN1nfsCJZbBVZWBp5kV/B/
kTHoKwlgWW58EnpBdwg5C+WmcO1vgUGGZ7aAa5yAuhpN6gLqJR7atBxC4VpX+cgnpu7Q47E97acq
OIYfuYcpZy+zuCKNZ0Vi6bnnHw8Opc+xFeM0EF9YHwoA+1P4FMGvYvRWZ9UDnakYTWdpRRB+BPXU
eKdziAEinKFjtU9pa42k6/GpyBWtdaA2OIuhXLnNVWZKfaJkS6SGvufLdrUCAwEAAaOCAa0wggGp
MDwGCSsGAQQBgjcVBwQvMC0GJSsGAQQBgjcVCITFkUGFu5oogsGRL4TQqFfGmyMIh6brIoeJ+xoC
AWQCAQswEwYDVR0lBAwwCgYIKwYBBQUHAwQwCwYDVR0PBAQDAgXgMBsGCSsGAQQBgjcVCgQOMAww
CgYIKwYBBQUHAwQwHQYDVR0OBBYEFE14CkFPHcko33oMylI28+V9rXpyMB8GA1UdIwQYMBaAFOAp
5m6qUdZa9admS/aG9DXnqw+rME4GA1UdHwRHMEUwQ6BBoD+GPWh0dHA6Ly9wa2kuYW1hem9uLmNv
bS9jcmwvQW1hem9uLmNvbSUyMENJQSUyMENBJTIwRzQlMjAwMS5jcmwwWQYIKwYBBQUHAQEETTBL
MEkGCCsGAQUFBzAChj1odHRwOi8vcGtpLmFtYXpvbi5jb20vY3J0L0FtYXpvbi5jb20lMjBDSUEl
MjBDQSUyMEc0JTIwMDEuY3J0MD8GA1UdEQQ4MDagIQYKKwYBBAGCNxQCA6ATDBF0aGFyYXJAYW1h
em9uLmNvbYERdGhhcmFyQGFtYXpvbi5jb20wDQYJKoZIhvcNAQELBQADggIBAL1rzJpNq8ppMxZo
7viCegsnANvePrebNGn4QrkAk/xtgOQ8do1WypOCsGMbdSc8EKAEjlaufXXGf4KUkAQ5XLZoRLX+
DlrL1vZB38PILiaxtK0HnyN0kWQYA7GfKfBuUYr3AMjCsGbfTvoFcGQcTHOQZryXOW3xugDKedGU
LDp/cVn1jxlUwDmcNhXu9Yd+XjHNbEAeDhMw/QY6I1o/DFYXweazPICE8MkT4yEHoOotDxBlpbCA
gIYQSreJ0yYT8wtS2INBl3HCLoMMatUDmP2HeYHsLT/PKEqEou68eAwTrc44k5Bjvy1sHMOD4OAo
C92vb33Y8Sp/uvXT93tvBExLkIILBN+Py95Kb+6WEg5Ko741XmAv+z6DJ54MbAcJC/dT05e6Yt22
dBYVHW1rRWcrfJbmuS18dcuXhRizYBK3QuVXWU46bJgBft3VaPAqbxMX9E7jfD7Yh3Q9S1sPL9ad
gmaiDwnLRpXoCwiuZ57LLWPn2BoAphx7IexWIDNXac36j7hLw9PyY8tWsVz6VPowquWPfO4IoOmu
VXKaKUvlmp+ugCNqXRHpJkL+hbTsyxzeCvBMDVlz01aE3PDZIfc3wd5cmrXJT4XMS0nSX563BS9C
xA820RSUn1hFuFTg0zzovcksHMfuPxAWfjoKxi+bieOhbmXr+dlxh9EmY7jRMYIDtTCCA7ECAQEw
ezBkMRMwEQYKCZImiZPyLGQBGRYDY29tMRYwFAYKCZImiZPyLGQBGRYGYW1hem9uMRMwEQYKCZIm
iZPyLGQBGRYDYW50MSAwHgYDVQQDExdBbWF6b24uY29tIENJQSBDQSBHNCAwMQITaADIryiDbkSu
CXF09wAAAMivKDAJBgUrDgMCGgUAoIICDzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqG
SIb3DQEJBTEPFw0yMTAzMDMxMTM1MDdaMCMGCSqGSIb3DQEJBDEWBBTk6e4f/D1OV38TjjgAxDE6
/AA73zCBigYJKwYBBAGCNxAEMX0wezBkMRMwEQYKCZImiZPyLGQBGRYDY29tMRYwFAYKCZImiZPy
LGQBGRYGYW1hem9uMRMwEQYKCZImiZPyLGQBGRYDYW50MSAwHgYDVQQDExdBbWF6b24uY29tIENJ
QSBDQSBHNCAwMQITaADIryiDbkSuCXF09wAAAMivKDCBjAYLKoZIhvcNAQkQAgsxfaB7MGQxEzAR
BgoJkiaJk/IsZAEZFgNjb20xFjAUBgoJkiaJk/IsZAEZFgZhbWF6b24xEzARBgoJkiaJk/IsZAEZ
FgNhbnQxIDAeBgNVBAMTF0FtYXpvbi5jb20gQ0lBIENBIEc0IDAxAhNoAMivKINuRK4JcXT3AAAA
yK8oMIGTBgkqhkiG9w0BCQ8xgYUwgYIwCwYJYIZIAWUDBAEqMAsGCWCGSAFlAwQBFjAKBggqhkiG
9w0DBzALBglghkgBZQMEAQIwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsOAwIa
MAsGCWCGSAFlAwQCAzALBglghkgBZQMEAgIwCwYJYIZIAWUDBAIBMA0GCSqGSIb3DQEBAQUABIIB
AMteMGkC+Gs+vz2uDr6SnDv4aVauwcyHzLWvR91pMMihzUFPhNvuKh5CC9O0azchtRXlf0TRsxeF
DfC1SgX7EPZiLNuxj6eerHyTWnDYp+qBvmCGQGrJSSYgev2MZQ5PzHfsNdiI8MdrZL6bqkfNSSif
6MnUE070z+7IRrkBjgl26jdEqlaKumkU5n/TrqE5W/MOJU5CbM0k1ugrqKkanVbgtAho1kn30Ytq
cPn3eJN50L/trzSMsO0KuQExVy+cAEPZcVQX0WF6uuTPLsfwplTejEqJD42Oxclf7l0Ybm8kA8YD
fBIWM/95YoCHu/dajfDY6VDaSCpBWO9WmGl3Mv8AAAAAAAA=

------=_NextPart_000_009B_01D7107D.763992C0--