Thread: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another

I am having a problem running  "pg_dump -s database "on one system
while it runs fine on another system.
Both databases are nearly identical (minor changes to schemas and
tables)
On the older system it is a redhat x.x (32 bit) 12GiB memory running
postgresql 8.4.3 (32 bit)
On the newer system it is aUbuntu 10.04 (64 bit) 8GiB memory running
postgresql 8.4.8 64 bit
Postgresql.conf are identical  .
On the redhat the kernel paramters are :
cat /proc/sys/kernel/sh*
/proc/sys/kernel/shmall  /proc/sys/kernel/shmmax
/proc/sys/kernel/shmmni
4294967296
68719476736
4096

On Ubuntu I have cranked up the shmmax parameter several time to be
now twice what is configured on the redhat system and reduced
max_connections to less than half of the redhat system

Both systems have the same parameter settings for (except
max_connections which is much higher on the redhat system, I have been
downgrading this parameter on Ubuntu) :
 max_connections           | 14      |      | 1       | 536870911  |
100  << Ubuntu
 max_connections           | 100     |      | 1       | 536870911  |
100  << Redhat

 max_files_per_process     | 1000    |      | 25      | 2147483647 |
1000
 max_function_args         | 100     |      | 100     | 100        |
100
 max_identifier_length     | 63      |      | 63      | 63         |
63
 max_index_keys            | 32      |      | 32      | 32         |
32
 max_locks_per_transaction | 64      |      | 10      | 2147483647 |
64
 max_prepared_transactions | 0       |      | 0       | 536870911  | 0
 max_stack_depth           | 2048    | kB   | 100     | 2147483647 |
100


On the Ubuntu the last test that I did I set the sharred_buffer to:
          name           | setting | unit | min_val |  max_val   |
boot_val
--------------------------+---------+------+---------+------------+----------
 shared_buffers           | 32768   | 8kB  | 16      | 1073741823 |
1024
 shared_preload_libraries |         |      |
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


This is a very large table with a lot of inheritances.
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?

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

Tom,
That did the trick.  I made a bad assumption that the shared_memory
was causing the problem and not the other way around.  I  set it up to
256, last attempt was 128 and it still failed, not sure what value
would have given me success  (128 - 256) but it needed quite a bit
more.
Thanks for your help

On Sun, 07 Aug 2011 12:37:22 -0400, tgl@sss.pgh.pa.us (Tom Lane)
wrote:

>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