Восстановление не удаётся: зависание и тайм-ауты при ALTER TABLE

deargle · 02.Апрель.2021 23:43:11

На сервере Discourse произошёл сбой диска, и я пытаюсь восстановить резервную копию из S3 на экземпляр AWS EC2 типа t2.small. Постоянно получаю следующую ошибку.

Использую такой порядок действий:

git clone https://github.com/discourse/discourse_docker /var/discourse

Использую этот app.yml

./launcher bootstrap app
./launcher start app

Затем копирую резервную копию через scp, ./launcher enter app, discourse enable_restore, discourse restore <файл>.

После этого происходит следующее (начало обрезано):

14
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 #<Thread:0x0000562604925448 /var/www/discourse/lib/sidekiq/pausable.rb:79 run> terminated with exception (report_on_exception is true):
Traceback (most recent call last):
        33: from /var/www/discourse/lib/sidekiq/pausable.rb:83:in `block (2 levels) in extend_lease_thread'
        32: from /var/www/discourse/lib/sidekiq/pausable.rb:83:in `synchronize'
        31: from /var/www/discourse/lib/sidekiq/pausable.rb:84:in `block (3 levels) in extend_lease_thread'
        30: from /usr/local/lib/ruby/2.7.0/set.rb:328:in `each'
        29: from /usr/local/lib/ruby/2.7.0/set.rb:328:in `each_key'
        28: from /var/www/discourse/lib/sidekiq/pausable.rb:85:in `block (4 levels) in extend_lease_thread'
        27: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rails_multisite-2.5.0/lib/rails_multisite/connection_management.rb:76:in `with_connection'
        26: from /var/www/discourse/lib/sidekiq/pausable.rb:86:in `block (5 levels) in extend_lease_thread'
        25: from /var/www/discourse/lib/discourse_redis.rb:59:in `block (2 levels) in <class:DiscourseRedis>'
        24: from /var/www/discourse/lib/discourse_redis.rb:29:in `ignore_readonly'
        23: from /var/www/discourse/lib/discourse_redis.rb:59:in `block (3 levels) in <class:DiscourseRedis>'
        22: from /var/www/discourse/lib/discourse_redis.rb:59:in `public_send'
        21: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:425:in `expire'
        20: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:69:in `synchronize'
        19: from /usr/local/lib/ruby/2.7.0/monitor.rb:202:in `mon_synchronize'
        18: from /usr/local/lib/ruby/2.7.0/monitor.rb:202:in `synchronize'
        17: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:69:in `block in synchronize'
        16: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:426:in `block in expire'
        15: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-2.3.1/lib/mini_profiler/profiling_methods.rb:85:in `block in profile_method'
        14: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:131:in `call'
        13: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:237:in `process'
        12: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:325:in `logging'
        11: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:238:in `block in process'
        10: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:389:in `ensure_connected'
         9: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:248:in `block (2 levels) in process'
         8: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:131:in `block in call'
         7: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:279:in `read'
         6: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:268:in `io'
         5: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:280:in `block in read'
         4: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:378:in `read'
         3: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:45:in `gets'
         2: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:52:in `_read_from_socket'
         1: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:52:in `loop'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:56:in `block in _read_from_socket': Redis::TimeoutError (Redis::TimeoutError)
        33: from /var/www/discourse/lib/sidekiq/pausable.rb:83:in `block (2 levels) in extend_lease_thread'
        32: from /var/www/discourse/lib/sidekiq/pausable.rb:83:in `synchronize'
        31: from /var/www/discourse/lib/sidekiq/pausable.rb:84:in `block (3 levels) in extend_lease_thread'
        30: from /usr/local/lib/ruby/2.7.0/set.rb:328:in `each'
        29: from /usr/local/lib/ruby/2.7.0/set.rb:328:in `each_key'
        28: from /var/www/discourse/lib/sidekiq/pausable.rb:85:in `block (4 levels) in extend_lease_thread'
        27: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rails_multisite-2.5.0/lib/rails_multisite/connection_management.rb:76:in `with_connection'
        26: from /var/www/discourse/lib/sidekiq/pausable.rb:86:in `block (5 levels) in extend_lease_thread'
        25: from /var/www/discourse/lib/discourse_redis.rb:59:in `block (2 levels) in <class:DiscourseRedis>'
        24: from /var/www/discourse/lib/discourse_redis.rb:29:in `ignore_readonly'
        23: from /var/www/discourse/lib/discourse_redis.rb:59:in `block (3 levels) in <class:DiscourseRedis>'
        22: from /var/www/discourse/lib/discourse_redis.rb:59:in `public_send'
        21: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:425:in `expire'
        20: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:69:in `synchronize'
        19: from /usr/local/lib/ruby/2.7.0/monitor.rb:202:in `mon_synchronize'
        18: from /usr/local/lib/ruby/2.7.0/monitor.rb:202:in `synchronize'
        17: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:69:in `block in synchronize'
        16: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis.rb:426:in `block in expire'
        15: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-2.3.1/lib/mini_profiler/profiling_methods.rb:85:in `block in profile_method'
        14: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:131:in `call'
        13: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:237:in `process'
        12: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:325:in `logging'
        11: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:238:in `block in process'
        10: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:389:in `ensure_connected'
         9: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:248:in `block (2 levels) in process'
         8: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:131:in `block in call'
         7: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:279:in `read'
         6: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:268:in `io'
         5: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/client.rb:280:in `block in read'
         4: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:378:in `read'
         3: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:45:in `gets'
         2: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:52:in `_read_from_socket'
         1: from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:52:in `loop'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/redis-4.2.5/lib/redis/connection/ruby.rb:56:in `block in _read_from_socket': Connection timed out (Redis::TimeoutError)
^[[A  ^[[A^[[B       server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
EXCEPTION: psql failed: connection to server was lost

Постоянно процесс доходит до этого этапа восстановления, а затем зависает на последнем (85-м) ALTER TABLE. Я извлёк дамп.sql из резервной копии, и 85-й запрос, если я правильно считаю, — это «public.reviewables»:

Есть ли у вас идеи, что может быть не так, исходя из приведённой информации?

deargle · 03.Апрель.2021 00:57:15

Думаю, я разобрался. Мне следовало запустить ./discourse-setup один раз на новом хосте, чтобы (1) создать файл подкачки и (2) установить разумное значение db_shared_buffers. Ошибка в одном из этих пунктов, а возможно, в обоих, вызывала постоянные зависания.

pfaffman · 03.Апрель.2021 01:03:19

Отлично подмечено! Добро пожаловать назад и добро пожаловать в клуб «отвечай на свой собственный вопрос»! У меня есть целый ворох таких тем, хотя, уверен, многие уже удалены.

Другой вариант — скопировать app.yml, но всё равно пришлось бы помнить о замене. Если бы не эта загвоздка с заменой, файла yml было бы достаточно, если конфигурация S3 уже в нём.

Рады, что вы снова в строю!

Тема		Ответов	Просм.
Help restoring - system hung at midnight Self-hosting	26	2482	13.06.2022
Can't restore saved backup Self-hosting	3	1262	07.03.2017
Backup failed due to PG/SQL errors Self-hosting	9	2400	17.12.2020
Hangs discourse and discourse restore FAILED Self-hosting	24	2071	26.08.2020
Restore has failed. when i backup and restore to a new discourse Support	14	1665	15.11.2019

Восстановление не удаётся: зависание и тайм-ауты при ALTER TABLE

Связанные темы