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
