Postgres Pro
Downloads
Home   >   mailing lists

How much shared memory does Postgresql need per max_locks_per_transaction? - Mailing list pgsql-general

From David Tinker
Subject How much shared memory does Postgresql need per max_locks_per_transaction?
Date
Msg-id CA+O6_Fd+CsEShi8qD=-C1jDksDLeN4f4rWBi=UQwyjATHtg2CA@mail.gmail.com
Whole thread Raw
List pgsql-general
Tree view
I have a Postgresql 10 database with about 300k tables in 23k schemas. I am trying to upgrade to Postgresql 13 using pg_upgradecluster. This is failing while attempting dump all the schemas:

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 "a45119740"."activity_hrc" IN ACCESS SHARE MODE

Is setting max_locks_per_transaction to 300k something that can be done? I haven't been able to find anything explaining how much shared memory this might need. The machine has 64G of RAM.

(I understand that I need to change my db design .. I have been backing up one schema at a time until now so wasn't aware of this problem)

Thanks
David

pgsql-general by date:

Previous
From: Laura Smith
Date:
Subject: Re: Storage and querying of filesystem paths
Next
From: mobigroup
Date:
Subject: PostgreSQL 12.4 Parallel Query doesn't work while EXPLAIN is OK