idle connection in postgres causing process to stuck or result in error

Question

We have Postgres as our backend database . Our process runs some job(i.e it does some insert/update in DB) and then sleep for an hour.

This what we have noticed. While the time our process is sleeping our Postgres connection status is seen as idle.

postgres  5045  0.3  0.4 231220 33780 ?        Ss   12:13   0:16 postgres: scp scp_test x.x.x.x(60400) idle    

Now my question is?

If, I have a process that sleep for an hour .

Does Postgres close the idle connection after some time?

Because on the next run the process is not able to insert/update any record in DB.

Here how my code looks like.

  $logger  = Logger.new('log/checker.log')
  last_modified = Time.now
  while
    if (last_modified == File.mtime(file_path))
      $logger.info "Sleeping for 1 hour"
      sleep 3600
    else
      $logger.info "Inserting the file mtime changed .."
      last_modified = File.mtime(file_path)
      $logger.info "File modified ....."
      attributes = Test.testFile(file_path)
      index = 0 
      $logger.info "........Index set......"
      header_attributes = attributes.shift
      $logger.info "...........Header removed..........."
      trailer_attributes = attributes.pop
      $logger.info "...Trailer removed......."
      count = attributes.count
      $logger.info "............Count calculated #{count} ........."
      attributes.each_slice(50000) { |records|
        _records = initialize_records(records)
        _records.each { |record| 
          record.save 
          index += 1 
          $logger.info "Inserting ...... [#{index}/#{count}]"
       }
     }
     $logger.info "Completed insertion of #{count}" 
   end
 end

Tested this with

Ruby-2.2.2  -  ActiveRecord-4.2.6  - pg-0.18.0 
Ruby-2.3.0  -  ActiveRecord-4.2.6  - pg-0.18.0  
Jruby-9.0.5.0 - ActiveRecord-4.2.6 - activerecord-jdbc-adapter

Postgres version.

 PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

There is 1 difference between Ruby and JRuby output?

While both processes get stuck after they wake up from sleep

The Ruby process dies with PG::UnableToSend: SSL SYSCALL error: EOF detected error

but JRuby process gets stuck forever(doesn't die).

Not sure where the issue is, since I can't pin point at any specific library or code at this point.

Note: Works perfectly fine on a loopback interface. The postgres server in question is remote one..

Answer 1

Inside the data folder there is a file called postgresql.conf you have to configure it to send keepalive message to the client. Read this for more info.

Configuring postgresql.conf

Your postgresql.conf file should have a line like this tcp_keepalives_idle. If you want to send keepalive message to the client computer for every 5 minutes; Update tcp_keepalives_idle line like this

tcp_keepalives_idle = 300

Make sure to uncomment that line by removing the # mark.