shared memory size during upgrade pgsql with partitions

From: Piotr Włodarczyk <piotr(dot)wlodarczyk(at)gnb(dot)pl>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: shared memory size during upgrade pgsql with partitions
Date: 2019-12-17 20:03:41
Message-ID: 86a9cb749c7749c2aeca5e1ebb212598@EX13MBX02.gb.ad.corp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1.

During that we have a problem:

command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port 50432
--username postgres --schema-only --quote-all-identifiers --binary-upgrade
--format=custom --file="pg_upgrade_dump_281535902.custom" 'dbname=sprint'
>> "pg_upgrade_dump_281535902.log" 2>&1
pg_dump: error: query failed: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: error: query was: LOCK TABLE
"some_schemaa"."table_part_80000000_2018q3" IN ACCESS SHARE MODE

On current instance we have about one thousand of partitions, partitioned in
two levels: first by id_product, and second level by quarter of the year, as
you can see on above log.

How have we to calculate shared memory, and (eventually
max_locks_per_transaction) to be fit to the limits during upgrade?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kaijiang Chen 2019-12-18 03:23:37 Re: weird long time query
Previous Message Tom Lane 2019-12-17 17:04:20 Re: weird long time query