如何自动重新连接Rails 6 PostgreSQL连接

Question

我有一个rails 6应用程序，其中包含一些辅助进程。该应用程序使用PostgreSQL作为数据库。有时数据库会重新启动(例如小版本升级(，工作人员会失去连接。我希望他们能自动重新连接，但这并没有发生。

我试着在database.yml中使用reconnect: true标志。同样的情况。我仍然收到PG::UnableToSend: no connection to the server。该选项甚至在PostgresqlAdapter中都不可用。我想这只是MySQL适配器选项。

工人是我用rails runner运行的简单服务类

可以做些什么？我相信答案一定很简单。

Answer 1

我为PG自动重新连接制作了一个ActiveRecord补丁。

处理异常可以进行优化，但我有一些奇怪的PG::UnableToSend、PG::ConnectionBad和PG::Error的混合，所以我比较了异常名称。

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)

灵感来自

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