导入失败，提示“无法创建唯一索引”

支持 安装
1

TL;DR：我们在升级中搞砸了，需要帮助

我们使用 Home Assistant 的 Discourse 来驱动我们的社区。我们在 AWS 的 EC2 实例上通过 discourse_docker 方法运行它。

作为一个开源项目，论坛维护被搁置了，我们最终停留在旧版本上，上次更新是在 2019 年初。

更糟糕的是，之前的升级中，由于没有足够的磁盘空间升级到 Postgres 10，我们将 Postgres 锁定在 9.5 版本。我们从未解决过这个问题。

此外，我们曾修改过 Cloudflare 模板并将其提交到代码库，这导致 discourse_docker 分支无法更新到最新版本。

昨天，我们决定进行升级……

在迁移数据库时，我们遇到了一个语法与 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.

我们还有 47G 的可用空间，这很奇怪。随后我们发现 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.

这需要的空间更多了，但没问题。我们将磁盘空间提升到 300G 并再次运行。

这次 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 的一些权限问题成功做到了这一点，论坛在旧版本上重新上线。不过并非所有功能都正常，例如访问 admin → user → groups 会报错：

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 代码不匹配，无法迁移到更新的 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 和 IncomingReferrer？因为 referrer 指向 IncomingDomain，而 IncomingLink 指向 IncomingReferrer。

我现在正在执行查询，并尝试导入另一个备份。通过 postgres 直接查询得到的结果与使用 Rails 时不同。不过我想这可能是因为默认作用域（default scope）导致的？

4

我们尝试导入另一个备份，但因另一个损坏的索引而失败。我们已在原始实例上对所有唯一索引重新建立了索引，但目前有少数用户遇到了此问题。

6

好的，我们终于挺过来了，现在已恢复在线。感谢 @Falco 的提示。

为了帮助其他人解决类似问题，以下是我们采取的措施总结。

我们遇到了几个损坏的索引，导致导入失败。通过手动删除重复项，我们成功修复了问题。此外，还有 8 个用户的 username_lower 字段重复（比如太多的 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: