On Wednesday, November 7, 2012 6:36:15 PM UTC-8, CaduceusAbode wrote: > Hi there! > > I am using Ruby 1.9.3 in my app server that is running on an AWS EC2 > instance. I have my Postgres DB running on a separate EC2 instance but both > instances are in the same security group. When m Ruby code connects to the > DB, it uses the Sequel ORM gem ( http://sequel.rubyforge.org/ ) > > Now, I have configured that Postgres 9.1.4 DB to be able to accept > connections properly from the app server instance. > > However, every now and then, I noticed on the app server's logs that it > will have trouble connecting to the Postgres DB instance and I would see > error messages like these : > > * PG::Error: could not receive data from server: Connection timed out* > or > * PG::Error: connection not open* > > > So I went to the Postgres DB instance and looked in > /var/log/postgresql/postgresql-9.1-main.log and I see a bunch of messages > like these: > > * 2012-11-07 08:15:17 UTC LOG: could not receive data from client: > Connection timed out* > * 2012-11-07 08:15:17 UTC LOG: unexpected EOF on client connection* > > I did searching around the web including stack overflow, and have made > sure that my PostgreSQL does not have SSL enabled ( I have "ssl=off" inside > my postgresql.conf file ) > > At this point, I am not exactly sure what exactly is the issue in the > Postgres configuration. I do not what to mess with the maximum # of > connections or maximum timeout values on my production server without a > good proven reason. > > The app server can connect to the DB most of the time, and this problem > only shows up intermittently. > > On the Ruby-side, this is the error trace for "connection not open" when > making the Postgres call : > > PG::Error: connection not open > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in > `async_exec' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in > `block in execute_query' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/database/logging.rb:33:in > `log_yield' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:145:in > `execute_query' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:132:in > `block in execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:111:in > `check_disconnect_errors' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:132:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:372:in > `_execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `block (2 levels) in execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:379:in > `check_database_errors' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `block in execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in > > `block in synchronize' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/connection_pool/threaded.rb:105:in > > `hold' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in > > `synchronize' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/dataset/actions.rb:744:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:483:in > `fetch_rows' > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/model/base.rb:785:in > `primary_key_lookup' > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/model/base.rb:124:in > `[]' > > Similarly, this is the trace for "could not receive data from server" : > > PG::Error: could not receive data from server: Connection timed out > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:124:in > `block' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:124:in > `ensure in check_disconnect_errors' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:124:in > `check_disconnect_errors' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:132:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:372:in > `_execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `block (2 levels) in execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:379:in > `check_database_errors' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `block in execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in > > `block in synchronize' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/connection_pool/threaded.rb:105:in > > `hold' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/database/connecting.rb:229:in > > `synchronize' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:234:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/dataset/actions.rb:744:in > `execute' > > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/adapters/postgres.rb:483:in > `fetch_rows' > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/model/base.rb:785:in > `primary_key_lookup' > /var/lib/gems/1.9.1/gems/sequel-3.38.0/lib/sequel/model/base.rb:124:in > `[]' > > Please let me know what I might have missed, I appreciate it! > > IS >
This is a problem with your connection to the PostgreSQL server, and doesn't really have anything to do with Sequel (it's likely you would see similar issues with other libraries). If the PostgreSQL server you are connecting to is disconnecting the connections because they are idle, you can set up a cron job or something similar to make sure the connection is used and doesn't time out. Alternatively, if you have control over the server, make sure it is not configured to disconnect idle connections (though it seems you don't want to mess with those). If you haven't already done so, you should try upgrading to Sequel 3.41. Sequel should be disconnecting after it sees one of those disconnect errors, and establishing a new connection the next time it needs to execute a query, so at most this should only be causing a single error per disconnect. You can also try the new connection_validator extension in Sequel 3.41, it may solve your issue transparently (http://sequel.rubyforge.org/rdoc-plugins/files/lib/sequel/extensions/connection_validator_rb.html). Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/m7Gi49W7Z-IJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
