Search code examples
postgresqlmemoryamazon-linuxpg-upgrade

Postgres pg_upgrade 13.2 to 14, returns out of shared memory. - Amazon Linux release 2 (Karoo)

When attempting to upgrade a Postgres 13.2 database to 14, I receive the error "out of shared memory" when running the upgrade.

I have adjusted max_locks_per_transaction to different and really high values, and max_connections and other memory settings. I have checked many configuration settings but have not found a reason why it cannot do the pg_dump/pg_upgrade with those settings.

OS is

Amazon Linux release 2 (Karoo). ID_LIKE="centos rhel fedora"

The error is as follows

command: "/usr/pgsql-14/bin/pg_dump" --host /usr/pgsql-14/bin --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_18458.custom" 'dbname=aprivatedb' >> "pg_upgrade_dump_18458.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 "privatedata"."private_activities" IN ACCESS SHARE mode
Solution

  • Where did you make these changes? Based on the error messages, it is the 13 cluster, not the 14 cluster, that needs max_locks_per_transaction raised.

    pg_dump is run against the old cluster (using the new binary), not the new one.