From: Massimo Ortensi <mortensi@unimaticaspa.it>
To: pgsql-admin@lists.postgresql.org
Subject: Out of memory error during pg_upgrade in big DB with large objects
Date: Mon, 21 Nov 2022 17:37:50 +0100
Message-ID: <1ox9nq-005Lel-UF@unimaticaspa.it> (raw)
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
Attachments:
[image/jpeg] UnimaticaRGI.jpg (1.6K, ../1ox9nq-005Lel-UF@unimaticaspa.it/3-UnimaticaRGI.jpg)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mortensi@unimaticaspa.it, pgsql-admin@lists.postgresql.org
Subject: Re: Out of memory error during pg_upgrade in big DB with large objects
In-Reply-To: <1ox9nq-005Lel-UF@unimaticaspa.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox