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
Via Cristoforo Colombo, 21 -
40131, Bologna
Tel. 051 4195069
Cell. 3351092560
mortensi@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxx. 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
