PostgreSQL limiting connections to fewer than max_connections?

Question

I have a PostgreSQL 9.4 server that seems to be limiting connections to fewer than what I have defined in max_connections.

I have max_connections set to 300.

SHOW max_connections; max_connections ----------------- 300 

However, I have discovered that I start getting new connections timing out as soon as my active connections hits 100, as measured by SELECT count(*) from pg_stat_activity;

So long as that number is below 100, new connections work. Once it hits 100, new connections fail: PG::ConnectionBad: could not connect to server: Connection timed out (Ruby pg library)

There seems to be an exception for superuser connections (ie. me connecting via PgAdmin), as I can exceed the 100 threshold that way, though apps not using superusers continue to time out.

I am at a loss as to figuring out exactly what the root cause may be.

Some config settings:

max_connections = 300 superuser_reserved_connections = 2 shared_buffers = 1024MB effective_cache_size = 2048MB work_mem = 8MB maintenance_work_mem = 256MB 

I have tried tinkering with these values a bit, to no useful effect.

Other details:

• Ubuntu 14.04 64-bit
• 4GB Linode VPS
• PostgreSQL 9.4.1
• Did not have this problem with essentially the same configuration on PostgreSQL 9.2.
• Databases were migrated from Postgres 9.2 via pg_dumpall
• Memory usage doesn't seem to be an issue, free shows 2.1 GB free, 3.5 GB including buffers/cache.
• The connections are for many different roles to many different databases, no individual DB has more than 5 or 6 active connections, and each DB is being accessed by its own separate role. Does not seem to be running afoul of any connection limits on individual DBs or roles.

Answer 1

Have you raised the kernel max segment size too?

Try:

sysctl -w kernel.shmmax=1258291200 

Answer 2

Is it possible that you have limited the max user connection?Role privileges

You can change this with

ALTER ROLE rolename CONNECTION LIMIT 300;