pg_upgrade with large pg_largeobject table

From: Mate Varga <m(at)matevarga(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_upgrade with large pg_largeobject table
Date: 2018-08-14 17:41:26
Message-ID: CAK4GaZ551DKU=W4fuWYS1cgwftMT-jy7oBZsX65rCfbazDARVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi.

hanks in advance for any advice.

We have a PSQL 9.5 DB with 16G physical RAM and ~ 1 TB data mostly stored
in the pg_largeobject system table. This table has 250M rows at the moment.
We're trying to upgrade this to 10.x with an in-place upgrade. The command
I'm using is:sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade -b
/usr/lib/postgresql/9.5/bin -B /usr/lib/postgresql/10/bin -p 5433 -P 5434
-d /etc/postgresql/9.5/test -D /etc/postgresql/10/test --linkThis command
fails because of an OOM. Logs say:

command: "/usr/lib/postgresql/10/bin/pg_dump" --host
/var/log/postgresql/pg_upgradecluster-9.5-10-test.wjNi --port 5433
--username postgres --schema-only --quote-all-identifiers --binary-upgrade
--format=custom --file="pg_upgrade_dump_31803.custom" 'dbname=tolven' >>
"pg_upgrade_dump_31803.log" 2>&1
pg_dump: [archiver (db)] query failed: out of memory for query result
pg_dump: [archiver (db)] query was: SELECT oid, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = lomowner) AS rolname, lomacl, NULL AS
rlomacl, NULL AS initlomacl, NULL AS initrlomacl FROM
pg_largeobject_metadata

Any ideas about how could we make this work on a server with only 16G RAM
(possibly a few dozens of gigabytes of swap)?Thanks.
Would it help if we'd inline these largeobjects as e.g. text cols (instead
of storing them as lobs)?
Thanks,
Mate

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Edmundo Robles 2018-08-14 17:44:41 upgrading from pg 9.3 to 10
Previous Message Dimitri Maziuk 2018-08-14 17:40:37 Re: pg_basebackup failed to read a file