Restore fails because of non working database reconnect

Contribute Bug
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.

1 Like