How to auto reconnect Rails 6 PostgreSQL connection?

Question

I have a rails 6 application with some worker processes. The app uses PostgreSQL as a DB. Sometimes the DB reboots (e.g. minor version upgrade) and workers lose their connection. I'd expect them to auto-reconnect but it doesn't happen.

I tried to use reconnect: true flag in database.yml. The same story. I still receive PG::UnableToSend: no connection to the server. The option is not even available in PostgresqlAdapter. I guess it's only the MySQL adapter option.

The workers are simple service classes I run with rails runner

What could be done? I believe the answer must be simple.

Answer 1

Slight improvisation to @AlexeySchepin's answer.

@CareunoMerchan - This fixes "(wrong number of arguments (given 4, expected 1..3): config/initializers/postgresql_auto_reconnect.rb:14:in `exec_query'" error

Included the ** double splat operator while calling super method

Source code reference: https://github.com/rails/rails/blob/94b5cd3a20edadd6f6b8cf0bdf1a4d4919df86cb/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb#L80

module PSQLAutoReconnectPatch
  QUERY_EXCEPTIONS = [
    "SSL connection has been closed unexpectedly",
    "server closed the connection unexpectedly",
    "no connection to the server",
  ].freeze

  CONNECTION_EXCEPTIONS = [
    "could not connect to server",
    "the database system is starting up",
  ].freeze

  def exec_query(*args, **params)
    super(*args, **params)
  rescue ActiveRecord::StatementInvalid => e
    raise unless recoverable_query?(e.message)

    in_transaction = transaction_manager.current_transaction.open?
    try_reconnect
    in_transaction ? raise : retry
  end

  private

  def recoverable_query?(error_message)
    QUERY_EXCEPTIONS.any? { |e| error_message.include?(e) }
  end

  def recoverable_connection?(error_message)
    CONNECTION_EXCEPTIONS.any? { |e| error_message.include?(e) }
  end

  def try_reconnect
    sleep_times = [0.1, 0.5, 1]

    begin
      reconnect!
    rescue PG::Error => e
      sleep_time = sleep_times.shift
      if sleep_time && recoverable_connection?(e.message)
        Rails.logger.error("DB Server timed out, retrying in #{sleep_time} sec")
        sleep sleep_time
        retry
      else
        Rails.logger.error(e)
        raise
      end
    end
  end
end

require "active_record/connection_adapters/postgresql_adapter"
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PSQLAutoReconnectPatch)

Answer 2

I made an ActiveRecord patch for PG auto-reconnection.

Work with exceptions could be optimised but I had some weird mixed of PG::UnableToSend, PG::ConnectionBad and PG::Error, so I compare exception names instead.

module PostgreSQLAdapterAutoReconnectPatch
  QUERY_EXCEPTIONS = [
    "SSL connection has been closed unexpectedly",
    "server closed the connection unexpectedly",
    "no connection to the server",
  ].freeze

  CONNECTION_EXCEPTIONS = [
    "could not connect to server",
    "the database system is starting up",
  ].freeze

  def exec_query(*args)
    super(*args)
  rescue ActiveRecord::StatementInvalid => e
    raise unless recoverable_query?(e.message)

    in_transaction = transaction_manager.current_transaction.open?
    try_reconnect
    in_transaction ? raise : retry
  end

  private

  def recoverable_query?(error_message)
    QUERY_EXCEPTIONS.any? { |e| error_message.include?(e) }
  end

  def recoverable_connection?(error_message)
    CONNECTION_EXCEPTIONS.any? { |e| error_message.include?(e) }
  end

  def try_reconnect
    sleep_times = [0.1, 0.5, 1, 2, 4, 8, 16, 32]

    begin
      reconnect!
    rescue PG::Error => e
      sleep_time = sleep_times.shift
      if sleep_time && recoverable_connection?(e.message)
        logger.warn("DB Server timed out, retrying in #{sleep_time} sec")
        sleep sleep_time
        retry
      else
        logger.error(e)
        raise
      end
    end
  end
end

require "active_record/connection_adapters/postgresql_adapter"
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterAutoReconnectPatch)

Inspired by

• https://dalibornasevic.com/posts/77-auto-reconnect-for-activerecord-connections
• https://github.com/kostya/pg_reconnect