To: jtkells@verizon.net
cc: pgsql-admin@postgresql.org
Subject: Re: pg_dump: Error message from server: ERROR: out of shared memory
	on one system works fine on another
In-reply-to: <1set379ibe1biogcgbq7se6k0m5610b5ds@4ax.com> 
References: <1set379ibe1biogcgbq7se6k0m5610b5ds@4ax.com>
Comments: In-reply-to jtkells@verizon.net
	message dated "Sun, 07 Aug 2011 12:23:32 -0400"
Date: Sun, 07 Aug 2011 12:37:22 -0400
Message-ID: <23190.1312735042@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

jtkells@verizon.net writes:
> I am having a problem running  "pg_dump -s database "on one system
> while it runs fine on another system.

> when I run the following dump on the Ubuntu system I get :
> pg_dump  -s  DB >/tmp/DB_schema_only.dmp
> pg_dump: WARNING:  out of shared memory
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  out of shared memory
> HINT:  You might need to increase max_locks_per_transaction.
> pg_dump: The command was: LOCK TABLE schema_x.x_table IN ACCESS SHARE
> MODE

> I don't understand what I am doing wrong since I have given a larger
> amount of resources on the Ubuntu system and continue to fail.  Am I
> missing anything else? 

The HINT told you what you need to do: increase max_locks_per_transaction.

The exact point at which you run out of shared memory after exceeding
max_locks_per_transaction will vary depending on a number of
hard-to-predict factors (in this case I'll bet 32-bitness vs 64-bitness
has a lot to do with it), so the fact that it fails on one machine and
not another is not that surprising.  You can be sure though that if the
databases are identical, the "working" machine has not got a lot of
headroom; so you'd be well advised to apply the max_locks_per_transaction
adjustment to both.

			regards, tom lane