How to increase maximum database connections

Note: if you encounter max_locks_per_transaction error, this tutorial applies as well except you have to adjust max_locks_per_transaction and shared_buffers settings.








The standard 100 max database connections the TACTIC server can have with the database server may not be enough for production use. To increase this Postgres default in a Linux setup, you can follow these steps:

in /var/lib/pgsql/data/postgresql.conf, you would find these 2 settings
at 100, 24 MB respectively. You can change them to the following:

max_connections = 300
shared_buffers = 80MB

You would need to increase kernel max segment size to be slightly larger
than the shared_buffers,
The following line makes the kernel max to 96Mb ( If you
experience memory overrun, try a lower value and adjust shared_buffers accordingly).

You need to run this in a shell:

sysctl -w kernel.shmmax=100663296

/etc/init.d/postgresql restart
/etc/init.d/tactic restart

for a permanent change, you should also edit the file /etc/sysctl.conf and set the parameter as shown below. It will take effect when it reboots


Troubleshooting: if it fails to start, just open the startup log in /var/lib/pgsql/pgstartup.log and it will usually say the shmmax is less than the request size.. Just tweak the shared_buffers number lower and the shmmax higher so it doesn't give that error on start-up