PostgreSQL shared memory settings

FATAL: connection limit exceeded for non-superusers

The shared memory settings are mostly set too low for the PostgreSQL database server
on most systems. In particular on systems with BSD heritage.
When PostgreSQL exceeds one of the various hard shared memory and
semaphores (IPC) limits, the server will refuse to start.

The most important shared memory parameter is SHMMAX, the maximum size, in
bytes, of a shared memory segment. If you get an error message from
shmget like Invalid argument, it is likely that this limit has been exceeded.

You can see the current shared memory values with:
$ sysctl kern.sysv.shmmax
$ sysctl -a | grep shm

You can play around with the values temporarily (until restart) on the command line
$ sudo sysctl -w kern.sysv.shmmax=536870912
$ sudo sysctl -w kern.sysv.shmall=131072


Configuration parameters affecting PostgreSQL's shared memory usage:

• max_connections
• max_prepared_transactions
• shared_buffers
• wal_buffers
• max_fsm_relations
• max_fsm_pages

While it is possible to get PostgreSQL to run with SHMMAX as small as
1 MB, you need at least 4 MB for acceptable performance, and desirable
settings are in the tens of megabytes.

Lowering the number of allowed connections is a temporary workaround for failures.

---

In OS X 10.3.9 and later, you may create a file named ‘/etc/sysctl.conf’,
containing variable assignments such as:

kern.sysv.shmmax=536870912
kern.sysv.shmall=131072
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8


My original settings (before modifications) was:
kern.sysv.shmmax=4194304
kern.sysv.shmall=1024
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8

This method is better than editing ‘/etc/rc’ because your changes will be preserved across system updates.
Note that all five shared-memory parameters must be set in ‘/etc/sysctl.conf’, else the values will be ignored.
Beware that recent releases of OS X ignore attempts to set
SHMMAX to a value that isn't an exact multiple of 4096.
SHMALL is measured in 4 kB pages on this platform.

To check your settings you may issue the command:
$ cat /etc/sysctl.conf

In all OS X versions, you'll need to reboot to make changes in the
shared memory parameters take effect.

---
In OS X 10.3 and later, the commands is set in ‘/etc/rc’ and must be edited there. Note that
‘/etc/rc’ is usually overwritten by OS X updates so you should expect to have to redo your editing
after each update.
---
In OS X 10.2 and earlier, edit the file
‘/System/Library/StartupItems/SystemTuning/SystemTuning’
and change the values in the following commands:
"sysctl -w kern.sysv.shmmax=..." (shmmax, shmin, shmmni, shmseg, shmall)
---

Linux

The default max segment size is 32 MB. You may want to increase this value for larger PostgreSQL installations. The remaining defaults are quite generously sized, and usually do not require changes. The max segment size can be changed via the sysctl interface. For example, to allow 128 MB, and explicitly set the maximum total shared memory size to 2097152 pages (the default):

$ sysctl -w kernel.shmmax=134217728
$ sysctl -w kernel.shmall=2097152

In addition these settings can be saved between reboots in ‘/etc/sysctl.conf’.

---
http://www.google.se/search?q=mac+os+x+shmmax
http://www.caktusgroup.com/blog/2009/08/13/setting-postgresqls-shmmax-in-mac-os-x-105-leopard/
http://www.network-theory.co.uk/docs/postgresql/vol3/SharedMemoryandSemaphores.html
http://raam.splitbook.com/?p=13