インポート失敗：`could not create unique index`

サポート インストール
1

TL;DR：アップグレードでミスがあり、支援を求めています

Home Assistant では、コミュニティ運営のために Discourse を利用しています。AWS の EC2 インスタンス上で discourse_docker 方式で運用しています。

オープンソースプロジェクトであるため、フォーラムのメンテナンスがおろそかになり、最終的に 2019 年初頭に最後に更新された古いバージョンのままになってしまいました。

さらに悪いことに、以前のアップグレードで、Postgres 10 へのアップグレードに必要なディスク容量が不足していたため、Postgres を 9.5 に固定してしまいました。この問題は未解決のまま放置されていました。

また、過去に Cloudflare のテンプレートに変更を加え、それをリポジトリにコミットしてしまいました。これにより、docker_discourse ブランチが最新バージョンに自動更新されなくなっていました。

昨日、アップグレードを実行することにしました…

データベースのマイグレーション中に、9.5 と互換性のない構文が使用されているという問題に遭遇しました。

== 20200429095034 AddTopicThumbnailInformation: migrating =====================
-- execute("ALTER TABLE posts\nADD COLUMN IF NOT EXISTS image_upload_id bigint\n")

9.5 が固定されている問題をすぐに気づき、Postgres 10 への移行を試みました。しかし、それは失敗し、以下のエラーが発生しました。

I, [2020-06-12T00:30:55.448351 #1]  INFO -- : Upgrading PostgreSQL from version 9.5 to 10
WARNING: Upgrading PostgresSQL would require an addtional 89M of disk space
Please free up some space, or expand your disk, before continuing.

利用可能な容量は 47GB あり、これは奇妙でした。その後、discourse_docker が古かったことに気づき、最新バージョンに更新しました。驚いたことに、Postgres 12 が直ちにリリースされていました。

再度 rebuild を実行すると、今度は以下のエラーが発生しました。

I, [2020-06-12T00:41:17.378129 #1]  INFO -- : Upgrading PostgreSQL from version 9.5 to 12
WARNING: Upgrading PostgresSQL would require an addtional 92G of disk space
Please free up some space, or expand your disk, before continuing.

これは少し多くの容量が必要ですが、まあ仕方ありません。ディスク容量を 300GB に増やして、再度実行してみましょう。

今回は pg_upgrade がマイグレーション中に失敗しました。

Restoring database schemas in the new cluster
  template1
  discourse

*failure* Consult the last few lines of "pg_upgrade_dump_16384.log" for the probable cause of the failure. Failure, exiting

pg_upgrade_dump_16384.log ファイルを確認すると、以下のエラーが表示されていました。

pg_restore: creating VIEW "postgres_exporter.pg_stat_activity"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 721; 1259 678554 VIEW pg_stat_activity postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_activity.waiting does not exist
LINE 27:     "pg_stat_activity"."waiting",
             ^
    Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('678556'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('678555'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('678554'::pg_catalog.oid);

CREATE VIEW "postgres_exporter"."pg_stat_activity" AS
 SELECT "pg_stat_activity"."datid",
    "pg_stat_activity"."datname",
    "pg_stat_activity"."pid",
    "pg_stat_activity"."usesysid",
    "pg_stat_activity"."usename",
    "pg_stat_activity"."application_name",
    "pg_stat_activity"."client_addr",
    "pg_stat_activity"."client_hostname",
    "pg_stat_activity"."client_port",
    "pg_stat_activity"."backend_start",
    "pg_stat_activity"."xact_start",
    "pg_stat_activity"."query_start",
    "pg_stat_activity"."state_change",
    "pg_stat_activity"."waiting",
    "pg_stat_activity"."state",
    "pg_stat_activity"."backend_xid",
    "pg_stat_activity"."backend_xmin",
    "pg_stat_activity"."query"
   FROM "pg_stat_activity";

あーあ。

ここで、いくつかの手順を戻すことにしました。バックアップの問題を解決する間、フォーラムを再起動し、読み取り専用モードにすることはできないでしょうか。postgres と redis の権限問題を修正することでこれを達成し、フォーラムは古いバージョンでオンラインに戻りました。すべてが動作するわけではありません。例えば、管理画面からユーザー → グループに移動すると、以下のエラーが発生します。

NoMethodError (undefined method `automatic_membership_retroactive' for #<Group:0x00007fcaca3045e8>)
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activemodel-6.0.1/lib/active_model/attribute_methods.rb:431:in `method_missing'

しかし、残りは動作しているようです。

この時点で、動作するインスタンスに戻るために chown を実行する必要があったため、新しいインスタンスを開始し、バックアップをインポートすることにしました。

新しい EC2 インスタンスを開始し、discourse_docker のセットアップ手順に従ってインポートを開始しました。しかし、奇妙な問題に遭遇しました。データがインデックスの一意性の要件に一致しないため、インデックスを作成できないというエラーです。

ERROR:  could not create unique index "index_incoming_domains_on_name_and_https_and_port"
DETAIL:  Key (name, https, port)=(homeassistant.home, f, 8123) is duplicated.
EXCEPTION: psql failed: DETAIL:  Key (name, https, port)=(homeassistant.home, f, 8123) is duplicated.
/var/www/discourse/lib/backup_restore/database_restorer.rb:95:in `restore_dump'

しかし、実行中のインスタンスの Rails コンソールに移動すると、重複はありませんでした。

[7] pry(main)> IncomingDomain.where(name: "homeassistant.home")
=> [#<IncomingDomain:0x000055e5cabc3760 id: 8648, name: "homeassistant.home", https: false, port: 8123>]

これが現在の状況です。私たちはかなり行き詰まっています。

  • Ruby コードに対して不良な DB を持つ実行中のインスタンスがあり、新しい Postgres へのマイグレーションができません
  • 新しいインスタンスにインポートできないバックアップがあります

有料のホスト型 Discourse への移行を検討しましたが、月間 300 万ページビューと 100 万件の投稿があるため、エンタープライズ価格ではコミットメントが大きすぎます。

そのため、何らかの解決策を見つける必要があります。できればバックアップをインポートしたいですが、古いインスタンスをマイグレーションすることも可能です。

何かアイデアはありますか？有料で誰かに手伝ってもらうことも構いません。

2

最も簡単な解決策は、前回試したように、動作するバックアップを取得して新しいインスタンスにインポートすることだと考えます。

重複したデータの修正を試みましょう。

# サーバーに SSH で接続
cd /var/discourse
./launcher enter app
su postgres
psql
\connect discourse
SELECT * FROM incoming_domains WHERE name LIKE '%homeassistant.home%';

# 複数の行が出力されるはずです
# SQL の DELETE 文を使用して修正
# \q で終了

上記を試して、もしつまずいたらさらにサポートを求めてください。

3

IncomingLink と IncoingReferrer も整理する必要がありますか？referrer は IncomingDomain を指し、IncomingLink は IncomingReferrer を指しているためです。

今すぐクエリを実行し、別のバックアップをインポートしてみます。Postgres 経由でクエリを実行すると、Rails を使用した場合とは異なる結果が返ってきますが、これはデフォルトスコープの影響かもしれません。

4

別のバックアップのインポートを試みましたが、別の破損したインデックスが原因で失敗しました。元のインスタンスのすべての一意のインデックスを再インデックスしましたが、少数のユーザーでこの問題が発生しています。

引き続き状況をお知らせします。

6

よし、無事に復旧してオンラインに戻れました。@Falco さん、ヒントをありがとうございます。

他の人の問題解決の参考になればと思い、今回行った対応をまとめました。

まず、破損したインデックスがいくつかあり、それがインポートの失敗を引き起こしていました。重複を手動で削除することで修正することができました。また、username_lower が重複しているユーザーが8名いました（Mike や Marco といった名前が多すぎたためです）。これらは username と username_lower の両方を更新して名前を変更しました。ユーザーデータを確認したところ、最初の破損は2019年12月に発生していたことが分かりました。

「バックアップ作成」→「バックアップ復元」→「重複エラー発生」→「修正」というサイクルを繰り返す代わりに、すべてのインデックスを再構築することにしました。以下のクエリで一意制約を持つすべてのインデックスを検出しました。

select idx.relname as index_name, 
       insp.nspname as index_schema,
       tbl.relname as table_name,
       tnsp.nspname as table_schema
from pg_index pgi
  join pg_class idx on idx.oid = pgi.indexrelid
  join pg_namespace insp on insp.oid = idx.relnamespace
  join pg_class tbl on tbl.oid = pgi.indrelid
  join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique -- << 一意インデックスのみ
  and tnsp.nspname = 'public'

すべてのインデックスが正常に動作するようになった後、バックアップを作成し、新しいインスタンスに正しくインポートできました。マイグレーションも期待通りに実行され、インスタンスを切り替えてサービスを開始することができました :+1: Discourse の堅牢さに乾杯です :beers:

@Falco さん、再度ありがとうございます。
良い週末を :slight_smile:

7

データ破損の問題をデバッグする人向けの最後のヒントです。当初、重複データによるインポートに失敗した際、Rails コンソールに入り、インデックスの作成に失敗した原因となったデータで検索しました。

しかし、インデックス付きフィールドを使用してクエリを実行すると、Postgres が壊れたインデックスを使って結果を生成してしまったのです！そのため、最初のクエリでは 1 件の結果が表示され、そのエントリを削除した後のクエリでは 0 件の結果が表示されました。

フルテーブルスキャンを行うクエリが勝利です :slight_smile: