Content-Type: multipart/alternative;
 boundary="------------e8KAX6XuXr1vgDEkFX6gyG4e"
Date: Mon, 21 Nov 2022 17:37:50 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.5.0
To: pgsql-admin@lists.postgresql.org
From: Massimo Ortensi <mortensi@unimaticaspa.it>
Subject: Out of memory error during pg_upgrade in big DB with large objects
Message-Id: <1ox9nq-005Lel-UF@unimaticaspa.it>
Archived-At: 
 <https://www.postgresql.org/message-id/1ox9nq-005Lel-UF%40unimaticaspa.it>
Precedence: bulk

This is a multi-part message in MIME format.
--------------e8KAX6XuXr1vgDEkFX6gyG4e
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi everybody,

I'm trying to upgrade a huge DB from postgres 10 to 14

This cluster is 70+ TB, with one database having more than 2 billion 
records in pg_largeobject

I'm trying pg_upgrade in hard link mode, but the dump of databas schema 
phase always fails with

pg_dump: error: query failed: out of memory for query result
pg_dump: error: query was: SELECT l.oid, (SELECT rolname FROM 
pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, CASE WHEN 
privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl ORDER BY row_n) 
FROM (SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) WITH 
ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(pg_catalog.acldefault('L',l.lomowner)) AS 
privm(orig_acl) WHERE acl = orig_acl)) as foo) END AS initlomacl, CASE 
WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl) FROM (SELECT 
acl, row_n FROM pg_catalog.unnest(pg_catalog.acldefault('L',l.lomowner)) 
WITH ORDINALITY AS privp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(pip.initprivs) AS initp(init_acl) WHERE acl = 
init_acl)) as foo) END AS initrlomacl FROM pg_largeobject_metadata l 
LEFT JOIN pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 
'pg_largeobject'::regclass AND pip.objsubid = 0)

I upgraded server memory to 450 GB and it was not enough, now I'm trying 
with 680 GB RAM but I'm not confident it could work....

 From what I found is a common and known problem of pg_dump with many LOBs

Could pg_upgrade without -k option make any difference ?

Anybody can suggest a workaround ?

-- 

*Massimo Ortensi - Responsabile ICT *
logo <http://www.unimaticaspa.it/>

Via Cristoforo Colombo, 21 - 40131, Bologna
Tel.  051 4195069
Cell.  3351092560

_mortensi@unimaticaspa.it <javascript:message_new('mailto', 
{mailto:'mortensi@unimaticaspa.it', folder:'SU5CT1g=', msgid:'', 
subject: '', body:'0'})> _

Questa e-mail e i suoi allegati contengono informazioni di proprietà di 
Unimatica-RGI S.p.A. e devono essere utilizzati esclusivamente dal 
destinatario in relazione alle finalità per le quali sono stati 
ricevuti. E’ vietata qualsiasi forma di riproduzione o di divulgazione 
senza l’esplicito consenso di Unimatica-RGI S.p.A. Qualora la presente 
e-mail fosse stata ricevuta per errore, si prega di informare 
tempestivamente il mittente e distruggere la copia in possesso.

Privacy: I dati personali contenuti in questa e-mail, nonché nei file 
ivi inclusi, risultano oggetto di tutela ai sensi del Reg. UE 2016/679 
(GDPR). Il Titolare del trattamento dei suddetti dati è Unimatica-RGI 
S.p.A. Gli interessati potranno esercitare tutti i diritti ex artt. 15 e 
ss. del GDPR inviando un messaggio all’indirizzo 
privacy@pec.unimaticaspa.it. Qualsiasi trattamento effettuato da chi ha 
ricevuto per errore tali dati costituisce violazione delle disposizioni 
previste dal GDPR. In ogni momento è possibile proporre reclamo 
all’Autorità competente. Per maggiori informazioni si rinvia al sito 
www.unimaticaspa.it.

Ambiente: Considera la responsabilità che hai verso l’ambiente prima di 
stampare questa e-mail


--------------e8KAX6XuXr1vgDEkFX6gyG4e
Content-Type: multipart/related;
 boundary="------------WYVCIcXD0C6cPGMdXtyIxebD"

--------------WYVCIcXD0C6cPGMdXtyIxebD
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi everybody,</p>
    <p>I'm trying to upgrade a huge DB from postgres 10 to 14</p>
    <p>This cluster is 70+ TB, with one database having more than 2
      billion records in pg_largeobject</p>
    <p>I'm trying pg_upgrade in hard link mode, but the dump of databas
      schema phase always fails with <br>
    </p>
    <p>pg_dump: error: query failed: out of memory for query result<br>
      pg_dump: error: query was: SELECT l.oid, (SELECT rolname FROM
      pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT
      pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n
      FROM
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner)))
      WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1
      FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner)))
      AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl,
      (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl,
      row_n FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner)))
      WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1
      FROM
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner)))
      AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, CASE
      WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl ORDER BY
      row_n) FROM (SELECT acl, row_n FROM
      pg_catalog.unnest(pip.initprivs) WITH ORDINALITY AS
      initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
      pg_catalog.unnest(pg_catalog.acldefault('L',l.lomowner)) AS
      privm(orig_acl) WHERE acl = orig_acl)) as foo) END AS initlomacl,
      CASE WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl)
      FROM (SELECT acl, row_n FROM
      pg_catalog.unnest(pg_catalog.acldefault('L',l.lomowner)) WITH
      ORDINALITY AS privp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
      pg_catalog.unnest(pip.initprivs) AS initp(init_acl) WHERE acl =
      init_acl)) as foo) END AS initrlomacl FROM pg_largeobject_metadata
      l LEFT JOIN pg_init_privs pip ON (l.oid = pip.objoid AND
      pip.classoid = 'pg_largeobject'::regclass AND pip.objsubid = 0)<br>
    </p>
    <p>I upgraded server memory to 450 GB and it was not enough, now I'm
      trying with 680 GB RAM but I'm not confident it could work....</p>
    <p>From what I found is a common and known problem of pg_dump with
      many LOBs</p>
    <p>Could pg_upgrade without -k option make any difference ?</p>
    <p>Anybody can suggest a workaround ? </p>
    <div class="moz-signature">-- <br>
      <p class="MsoNormal" style="mso-margin-top&lt;p class="
        msonormal"="">
        <b>
          <span style="font-size:10.0pt;font-family:'Segoe
            UI',sans-serif;color:#1F497D">Massimo Ortensi - Responsabile
            ICT</span>
        </b>
        <span style="font-size:10.0pt;font-family:'Segoe
          UI',sans-serif;color:#2E74B5"> <br>
        </span><span
          style="font-size:10.0pt;font-family:'Arial',sans-serif;color:black">
          <a href="http://www.unimaticaspa.it/" target="_blank"><span
              style="font-family:'Segoe
              UI',sans-serif;text-decoration:none"><img
                style="width:1.177in;height:.3645in"
                id="Immagine_x0020_1"
                src="cid:part1.x373Tx0b.iMduq0M0@unimaticaspa.it"
                alt="logo" width="113" height="35" border="0"></span></a></span>
        <span style="font-size:10.0pt;font-family:'Segoe
          UI',sans-serif;color:#2E74B5">
          <br>
        </span>
        <span style="font-size:9.0pt;font-family:'Segoe
          UI',sans-serif;color:#4F81BD">
          <br>
          <span style="background:white">Via Cristoforo Colombo, 21 -
            40131, Bologna 
            <br>
            Tel.  051 4195069
            <br>
            Cell.  3351092560</span>
          <br>
          <span
            style="font-size:9.0pt;font-family:'Arial',sans-serif;color:#365F91"><br>
          </span><u>
            <span style="font-size:10.0pt;font-family:'Segoe
              UI',sans-serif;color:blue">
              <a href="javascript:message_new('mailto',
                {mailto:'mortensi@unimaticaspa.it', folder:'SU5CT1g=',
                msgid:'', subject: '', body:'0'})" target="_self">mortensi@unimaticaspa.it</a></span>
          </u>
          <span
            style="font-size:10.0pt;font-family:'Arial',sans-serif;color:#2E74B5">
            <o:p></o:p></span> </span></p>
      <p>
        Questa e-mail e i suoi allegati contengono informazioni di
        proprietà di Unimatica-RGI S.p.A. e devono essere utilizzati
        esclusivamente dal destinatario in relazione alle finalità per
        le quali sono stati ricevuti. E’ vietata qualsiasi forma di
        riproduzione o di divulgazione senza l’esplicito consenso di
        Unimatica-RGI S.p.A. Qualora la presente e-mail fosse stata
        ricevuta per errore, si prega di informare tempestivamente il
        mittente e distruggere la copia in possesso.
      </p>
      <p>
        Privacy: I dati personali contenuti in questa e-mail, nonché nei
        file ivi inclusi, risultano oggetto di tutela ai sensi del Reg.
        UE 2016/679 (GDPR). Il Titolare del trattamento dei suddetti
        dati è Unimatica-RGI S.p.A. Gli interessati potranno esercitare
        tutti i diritti ex artt. 15 e ss. del GDPR inviando un messaggio
        all’indirizzo <a class="moz-txt-link-abbreviated" href="mailto:privacy@pec.unimaticaspa.it">privacy@pec.unimaticaspa.it</a>. Qualsiasi trattamento
        effettuato da chi ha ricevuto per errore tali dati costituisce
        violazione delle disposizioni previste dal GDPR. In ogni momento
        è possibile proporre reclamo all’Autorità competente. Per
        maggiori informazioni si rinvia al sito <a class="moz-txt-link-abbreviated" href="http://www.unimaticaspa.it">www.unimaticaspa.it</a>.
      </p>
      Ambiente: Considera la responsabilità che hai verso l’ambiente
      prima di stampare questa e-mail
      <p><br>
      </p>
    </div>
  </body>
</html>
--------------WYVCIcXD0C6cPGMdXtyIxebD
Content-Type: image/jpeg; name="UnimaticaRGI.jpg"
Content-Disposition: inline; filename="UnimaticaRGI.jpg"
Content-Id: <part1.x373Tx0b.iMduq0M0@unimaticaspa.it>
Content-Transfer-Encoding: base64

/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwkHBgoJCAkLCwoMDxkQDw4ODx4WFxIZJCAm
JSMgIyIoLTkwKCo2KyIjMkQyNjs9QEBAJjBGS0U+Sjk/QD3/2wBDAQsLCw8NDx0QEB09KSMp
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT3/wAAR
CAAbAI0DASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA
AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK
FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG
h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl
5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA
AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk
NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE
hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk
5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD1fT9Th1ON3g3AIdp3CoV1y1IuSA/+jff4
/lVDwjj7LcZ/vj+VZyf6rWTnv/WuD6xL2cZ97nZ7CPtJR7HU22oQ3dn9piJKAZI7iqg1y2Ng
bvD+WH2YxzmuesbiXS1/eZNvdRnB96cv/Irv/wBd6lYuTSLeFim+1zpJdZtoriGF926YAqcV
Pe3kVhbNPNnaPTvXK6yjSXFosed3kgimahqcup20MQB/dLuk9yKTxrjzJ/IFhFLla2e511rd
x3VotwmQjDPNV7fWba5Sd4g2IfvDFZYuxaeEkYHDONqn61S0v/iW6lb7nDR3CbW56E1csTJO
Me5EcOmpPtsa3/CVWXHyTc9ttTf8JBa+THJtkCyNsGV5zVHU40HiKyUKoBHIAp3iVFRrIKoA
83sKPa1UpNvYPZ024pLc0L/WLawwshLOeiqMmpLDU7fUUJgblfvKeCKybBFm8S3JkAYqoAz2
rTj0+0tLyS7jYrIVOUB4/KtKdSpN83QidOEVy9R02q28F/HaSE+Y/QgcUaxq0Gi2DXl0GMak
A7Rk81ylyTdy3OoBwGjkBVc84HpVvxncLeeBmmU/eKfnmqwtb2s3FkYml7KCa36kkfxE0hnU
SLcxqxwHeIhfzrX1PXrHTLBbq4uAIpB+72Dcz/QVwV1rk1xoltoraR5c06LHHJLwD7ird5pz
WuveGdOumEojT5geRmvRdGO+xwqpL1NzTvEGja5ei3JuI7g8qk+V3fSql1qGnxavewSXN2Hi
cAhV4HHr3qnrmqWz65bLe6NPE0NwEhnRtgbn6c1nart/4SrV8/8APVf/AEGpWEpVGuZblPFV
KcbxZ1raVqVhcS/2Y6GGQ52sfu1ImhzR6TdJuD3U/JzwK6AgUYFcH1WCOz6xNmMdIM2gx2sw
AmRcgg9DVJdGvBoJtiqecZd2N3GK6bAFGBTlhoPXysCxE1p53MOXSrhtRspgqlI02uSakn0K
KO0u/si/vZwcAmtnFJgU1h4K4vbz0OZbRbyeOxt5VVYYx+8Ian33hlFhDWGRMrZAZuDXR4FG
BU/VadtSvrNS+jMSbTrufUrK4KriNf3nzdDUmuadcXzWxgCkRvlsnHFa4FLVewjZruT7WV0+
xg32lXaXv23T5FEjABlNQLo+oCO4uHdXu5V2gA4CiukwPSjFJ4aA1XklYwLbwtbC1T7SG87H
zENxmqOpWcmu+F7ix04JvWUKqMccA11pHFcXqpNjq7G1Ji3cnaetZzawrjOCNIReJvCTLev+
G5tT8N20Mexb61VTGxOBkdRmm6h4e1DV9PsLpnW11e0HDZ3K3/662tKXzYVkkJZyOSSa0QBi
u2FeUopnJKkk2jkYvDurapqdtda/cxGO1bckMQ4Y+pqpfeFdSn17ULpFjEU7hkO7kgCu6xSY
HpWka0ou6IdJNH//2Q==

--------------WYVCIcXD0C6cPGMdXtyIxebD--

--------------e8KAX6XuXr1vgDEkFX6gyG4e--