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

From: jtkells(at)verizon(dot)net
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
Date: 2011-08-07 16:23:32
Message-ID: 1set379ibe1biogcgbq7se6k0m5610b5ds@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2011-08-07 16:37:22 Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
Previous Message Vladimir Protasov 2011-08-05 08:16:41 Read-only postgres instance