ALTER TABLE でリストアが失敗、ハング、タイムアウトする

deargle · 2021 年 4 月 2 日午後 11:43

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       サーバーが予期せず接続を閉じました
これはおそらく、サーバーがリクエストの処理中、または処理前に異常終了したことを意味します
サーバーへの接続が失われました
EXCEPTION: psql failed: connection to server was lost

一貫して、復元プロセスはこの地点まで進み、最後の（85 番目の）ALTER TABLE でフリーズします。バックアップの dump.sql を抽出して確認したところ、85 番目は（私の数え方が合っていれば）この「public.reviewables」のものです。

上記のログから、何が問題か見当がつくでしょうか？

deargle · 2021 年 4 月 3 日午前 12:57

解決したと思います。新しいホストで ./discourse-setup を一度実行すべきでした。そうすれば、(1) スワップファイルが作成され、(2) 適切な db_shared_buffers が設定されます。この2つのうちのどちらかが誤っていたか、あるいは両方とも誤っていたことが、一貫してフリーズを引き起こしていた原因です。

pfaffman · 2021 年 4 月 3 日午前 1:03

見事な発見です！おかえりなさい、そして「自分の質問に答える」クラブへようこそ！私もそういうトピックをたくさん持っていますが、多くの場合は削除されているでしょうね。

もう一つの方法は app.yml をコピーすることですが、それでもスワップのことを覚えておく必要があります。スワップのひっかかりを除けば、S3 設定が yml 内に含まれていれば、yml ファイルさえあれば十分です。

再起動できてよかったですね！

トピック		返信	表示
Help restoring - system hung at midnight Self-hosting	26	2506	2022 年 6 月 13 日
Can't restore saved backup Self-hosting	3	1263	2017 年 3 月 7 日
Backup failed due to PG/SQL errors Self-hosting	9	2420	2020 年 12 月 17 日
Hangs discourse and discourse restore FAILED Self-hosting	24	2083	2020 年 8 月 26 日
Restore has failed. when i backup and restore to a new discourse Support	13	1678	2019 年 9 月 16 日

ALTER TABLE でリストアが失敗、ハング、タイムアウトする

関連トピック