From: jtkells@verizon.net
To: pgsql-admin@postgresql.org
Subject: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
Date: Sun, 07 Aug 2011 12:23:32 -0400
Message-ID: <1set379ibe1biogcgbq7se6k0m5610b5ds@4ax.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jtkells@verizon.net
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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox