Arval SQLException: FATAL: sorry, too many clients already in postgres

Question

I am using a database in PostgreSQL 9.1,in which entry are coming continuously from another program . I am sending request from Ajax after 6 sec to fetch the latest entry.tomcat output window shows exception---

Arval SQLException: FATAL: sorry, too many clients already

and program is working correctly also after this. When i check my postgres with query---

select count(*) from pg_stat_activity;

it shows that connection are increasing continuously but I close the connection after each request.I am using netbeans and struts 1.3.

     long previousSNO = Long.parseLong(request.getParameter("previousSNO"));
    if(previousSNO == 0)
    {
        sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp ORDER BY search_date DESC LIMIT 20");
        prest = cb.executeSQLQuery(sb.toString());
        rs = prest.executeQuery();
    }
    else
    {
        sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp WHERE sno > ? ORDER BY search_date DESC");
        prest = cb.executeSQLQuery(sb.toString());    
        prest.setLong(1, previousSNO);
        rs = prest.executeQuery();
    }
    rs.last();
    int c = rs.getRow();
    rs.beforeFirst();

    if(rs!=null && c>0)
    {    
    //code for making json resultsb from resultset here    
    rs.close();
    }
    cb.closeConnection();
    response.setContentType("text/plain");
    response.getWriter().print(resultsb.toString());

//and close method in connection bean is

    public void closeConnection() {
    try {
        // st.close();
        conn.close();
        System.out.println("con is closed");
        conn = null;

    } catch (SQLException e) {
        e.getMessage();
        System.out.println(e.getMessage());
        System.out.println("con is not closed");
    }
}

Every time its print on console " con is closed";

Answer 1

To increase the connection limit you may like the following document.

This solution is tested on ubuntu 12.04.

1. Make following changes in postgresql.conf file :

Open /etc/postgresql/9.1/main/postgresql.conf

max_connections = 200
shared_buffers = 100MB
max_files_per_process = 100

Reference: shared_buffers size should be less than shmmax size.

2. Commands to check shmmax:

$ sysctl -e kernel.shmmax
$ ipcs -l

Reference: Adjusting shmmax and shmall

3. Increase the size of shmmax:

Run the following command:

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

and write on top in /etc/sysctl.conf file:

kernel.shmmax=134217728
kernel.shmall=2097152

Reference : SHMMAX in Ubuntu

4. Restart postgresql

$ service postgresql restart

Links:

http://www.varlena.com/GeneralBits/Tidbits/perf.html

http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html