Date: Tue, 22 Nov 2022 14:27:09 +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
Subject: Re: Out of memory error during pg_upgrade in big DB with large
 objects
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
References: <1ox9nq-005Lel-UF@unimaticaspa.it>
 <3215516.1669051820@sss.pgh.pa.us>
From: Massimo Ortensi <mortensi@unimaticaspa.it>
In-Reply-To: <3215516.1669051820@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Message-Id: <1oxTIr-0073TD-ER@unimaticaspa.it>
Archived-At: 
 <https://www.postgresql.org/message-id/1oxTIr-0073TD-ER%40unimaticaspa.it>
Precedence: bulk

I tried (even if vers 15 is not feasible at the moment as we tested only 
vers 14).

It ended with the same Out of memory failure, just more quickly ( 1 hour 
instead of 12 hours)


Il 21/11/2022 18:30, Tom Lane ha scritto:
> Massimo Ortensi <mortensi@unimaticaspa.it> writes:
>> 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
> FWIW, this query was rewritten pretty substantially in v15.
> It's still going to produce a row per large object, but it
> should be a lot narrower because most of the ACL-wrangling
> now happens somewhere else.  I don't know if migrating to
> v15 instead of v14 is an option for you, and I can't promise
> that that'd be enough savings to fix it anyway.  But it's
> something to think about.
>
> 			regards, tom lane