Restore fails because of non working database reconnect

Contribute Bug

AI-generated summary

RGJ reports a bug where a large database restore (22 GB) on a two-container Discourse installation fails after 45 minutes. The error occurs during the final reconnection step, specifically when attempting to update the last restore date in BackupMetadata. The failure is caused by PQconsumeInput() timing out because the primary database connection has been idle for too long and was not properly re-established.

The root cause is identified as the ConnectionHandler in rails_multisite caching the connection handler. After 45 minutes of inactivity, the underlying socket becomes invalid, but the code attempts to reuse the cached handler instead of creating a new one. This leads to errors like PQsocket() can't get socket descriptor and a failed rollback.

A workaround involves configuring PostgreSQL to send TCP keepalives more frequently (e.g., tcp_keepalives_idle = 60) to prevent the connection from being dropped by the OS. The suggested fix is to modify the reconnection code to clear all connections or add a reconnect parameter to establish_connection to bypass the cached handler.

david confirms the issue and provides a fix via pull request #44323.

1

Situation:

A two container discourse installation with web and data on different hosts.

Problem

A large, 22 GB, command line restore from a very old Discourse version (i.e. many lenghty migrations to run) fails after 45 minutes, right after the database restore.

Reconnecting to the database...
EXCEPTION: PQconsumeInput() could not receive data from server: Connection timed out
SSL SYSCALL error: Connection timed out
/var/www/discourse/vendor/bundle/ruby/3.4.0/gems/rack-mini-profiler-4.0.1/lib/patches/db/pg/alias_method.rb:109:in 'PG::Connection#exec'
/var/www/discourse/vendor/bundle/ruby/3.4.0/gems/rack-mini-profiler-4.0.1/lib/patches/db/pg/alias_method.rb:109:in 'PG::Connection#async_exec'

…

from /var/www/discourse/app/models/backup_metadata.rb:16:in 'BackupMetadata.update_last_restore_date'
from /var/www/discourse/lib/backup_restore/database_restorer.rb:31:in 'BackupRestore::DatabaseRestorer#restore'
from /var/www/discourse/lib/backup_restore/restorer.rb:61:in 'BackupRestore::Restorer#run'
from script/discourse:242:in 'DiscourseCLI#restore'

…

Trying to rollback...
Cleaning stuff up...
Dropping functions from the discourse_functions schema...
Something went wrong while dropping functions from the discourse_functions schema
PQsocket() can't get socket descriptor

Theory

Discourse uses a second database connection for the actual restore and another one for the migration.
When those are finished, it reconnects to the database on its primary connection and performs BackupMetadata.update_last_restore_date which immediately fails.

The reason for this failure seems to be that the database reconnect does not actually reconnect.
It reuses the cached ConnectionHandler. See here. And that connection is gone after 45 minutes.

handler = connection_handlers[handler_key(spec)]

unless handler
  handler = ActiveRecord::ConnectionAdapters::ConnectionHandler.new
  handler.establish_connection(spec.config)
  connection_handlers[handler_key(spec)] = handler
end

ActiveRecord::Base.connection_handler = handler

Workaround

Postgres has tcp_keepalives_idle = 0 which means fall back to the OS setting.
OS has net.ipv4.tcp_keepalive_time = 7200 (2 hours)

ALTER SYSTEM SET tcp_keepalives_idle = 60; 
ALTER SYSTEM SET tcp_keepalives_interval = 30; 
ALTER SYSTEM SET tcp_keepalives_count = 5;

Keeps the connection from getting closed and resolves the issue.

Suggested fix

Have the reconnection code do

ActiveRecord::Base.connection_handler.clear_all_connections! or similar before re-establishing the connection.

Or, more generic, add a reconnect parameter to establish_connection which bypasses the cached handler.

5 Likes
4

Thanks @RGJ, I think this should take care of it:

2 Likes
5

This topic was automatically closed after 4 days. New replies are no longer allowed.