From: Tom Lane <tgl@sss.pgh.pa.us>
To: Massimo Ortensi <mortensi@unimaticaspa.it>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Out of memory error during pg_upgrade in big DB with large objects
Date: Mon, 21 Nov 2022 12:30:20 -0500
Message-ID: <3215516.1669051820@sss.pgh.pa.us> (raw)
In-Reply-To: <1ox9nq-005Lel-UF@unimaticaspa.it>
References: <1ox9nq-005Lel-UF@unimaticaspa.it>
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
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: tgl@sss.pgh.pa.us, 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: <3215516.1669051820@sss.pgh.pa.us>
* 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