Rails produces "PGError: server closed the connection unexpectedly" after some timeout

Question

I have the setup of my Rails app as following:

• Rails: 3.0.5 (under Apache proxy) running on RHEL 5.6
• Postgres: 8.4, running on Windows Server 2008 The 2 servers are on the same LAN.

The problem is, after some idle time, when I make a new request to the Rails app, it gives me the following error:

ActiveRecord::StatementInvalid (PGError: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

From what I have researched, it seems that the database connections are dropped after some timeout by Postgres. During this time, from the Rails side,

• if I make a request to Rails (1st request), it will display the connection error as above
• if I make another request to Rails (2nd request), Rails seems to reconnect to Postgres and functions correctly.

It means that I will always experience the first connection error then will have all normal operation again, which is very serious in my case since I'd like to deliver a non-error response to my client.

I have looked in following questions and answers, but they do not seem to be appropriate for my case:

• "PGError: no connection to the server" after idle (Ubuntu for DB server)
• How to use tcp_keepalives settings in Postgresql? (keepalives in MacOS)

Do you have any advice in order to make my app free from db connection errors? Thank you.

Answer 1

We had this problem on Heroku, a lot. As a hackish solution, here's what we did. Put the following in your ApplicationController:

prepend_before_filter :confirm_connection
def confirm_connection
  c = ActiveRecord::Base.connection
  begin
    c.select_all "SELECT 1"
  rescue ActiveRecord::StatementInvalid
    ActiveRecord::Base.logger.warn "Reconnecting to database"
    c.reconnect!
  end
end

Basically, tests the connection on each controller hit. Scalable? Not really. But it fixed the problem for us.

Answer 2

In database.yml, do you have the reconnect: true option set for the connection? ex:

production:
  adapter:   postgresql
  database:  myapp
  username:  deploy
  password:  password
  reconnect: true

I'm not sure about the specific error, but without this option you need to handle a class of expected db errors yourself.