`could not create unique index` 오류로 가져오기 실패

제품 지원 자체 호스팅
1

요약: 업그레이드를 엉망으로 만들어서 도움을 구합니다

Home Assistant에서는 커뮤니티를 위해 Discourse를 사용합니다. AWS의 EC2 인스턴스에서 discourse_docker 방식으로 운영하고 있습니다.

오픈 소스 프로젝트인 만큼 포럼 유지보수가 소홀해져서 2019년 초에 마지막으로 업데이트된 구버전을 사용하게 되었습니다.

더 나쁜 것은, 이전 업그레이드 당시 Postgres를 9.5로 고정(Pin)해 두었습니다. Postgres 10으로 업그레이드할 디스크 공간이 충분하지 않았기 때문이죠. 이 문제는 끝까지 해결하지 못했습니다.

또한 이전에 Cloudflare 템플릿을 수정해서 해당 변경 사항을 저장소(Repo)에 커밋한 적이 있는데, 이로 인해 docker_discourse 브랜치가 최신 버전으로 자체 업데이트되지 못했습니다.

어제 우리는 업그레이드를 진행하기로 결정했습니다…

데이터베이스 마이그레이션 중 9.5와 호환되지 않는 구문이 사용되고 있다는 문제를 발견했습니다:

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

Postgres 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";

아니, 이게 뭐죠.

그래서 우리는 한 발 물러서기로 결정했습니다. 포럼을 다시 실행하고, 백업 문제를 해결하는 동안 읽기 전용(Read-only) 모드로 두면 안 될까요? 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 코드와 비교했을 때 DB가 손상되어 있어 더 새로운 Postgres로 마이그레이션할 수 없는 실행 중인 인스턴스가 있습니다.
  • 새 인스턴스에 가져올 수 없는 백업이 있습니다.

유료 호스팅 Discourse로 이전할 수 있는지 조사해 보았지만, 페이지뷰가 300만 회, 게시물이 100만 개나 되는 우리에게 엔터프라이즈 요금제는 부담이 너무 큽니다.

그래서 출구를 찾아야 합니다. 가능하면 백업을 가져오는 것이 좋지만, 기존 인스턴스를 마이그레이션하는 것도 괜찮습니다.

혹시 아이디어가 있는 분 계신가요? 도움을 주시는 분께 비용을 지불하는 것도 마다하지 않습니다.

2

가장 쉬운 해결 방법은 기존에 시도하셨던 것처럼 정상적으로 작동하는 백업을 확보한 후, 새 인스턴스에 가져오는 것입니다.

중복된 데이터를 수정해 보겠습니다:

# ssh into the machine
cd /var/discourse
./launcher enter app
su postgres
psql
\connect discourse
SELECT * FROM incoming_domains WHERE name LIKE '%homeassistant.home%';

# this should print multiple lines
# use SQL DELETE statements to fix it
# and exit with \q

위 내용을 시도해 보시고, 막히면 추가 도움을 요청해 주시겠습니까?

3

referrer가 IncomingDomain을 가리키고 IncomingLink가 IncomingReferrer를 가리키기 때문에 IncomingLink와 IncoingReferrer도 정리해야 하나요?

지금 쿼리를 실행 중이며 다른 백업을 가져오려고 시도해 볼 것입니다. postgres를 통해 쿼리를 실행하면 Rails를 사용할 때와 다른 결과가 나옵니다. 아마도 기본 범위(default scope) 때문일 것 같습니다.

4

다른 백업을 가져오려고 시도했으나, 또 다른 손상된 인덱스 때문에 실패했습니다. 원래 인스턴스에서 모든 고유 인덱스를 다시 생성했으며, 현재 일부 사용자와 관련하여 이 문제를 겪고 있습니다.

상황이 업데이트되면 알려드리겠습니다.

6

드디어 문제를 해결하고 다시 온라인으로 복귀했습니다. 힌트를 주신 @Falco 님께 감사드립니다.

다른 분들의 문제 해결에 도움이 되고자, 우리가 취한 조치들을 정리해 보았습니다.

수정되지 않은(손상된) 인덱스 몇 개가 가져오기(import) 실패를 유발하고 있었습니다. 중복 항목을 수동으로 삭제하여 문제를 해결할 수 있었습니다. 또한 username_lower 값이 중복된 사용자 8명이 있었습니다(마이크나 마르코 같은 이름이 너무 많았기 때문이죠). username과 username_lower를 모두 업데이트하여 해당 사용자들을 수정했습니다. 사용자 데이터를 확인한 결과, 첫 번째 데이터 손상은 2019년 12월에 발생한 것으로 파악되었습니다.

“백업 생성” → “백업 복원” → “중복 오류 발생” → "수정"이라는 사이클을 반복하는 대신, 모든 인덱스를 다시 인덱싱(reindex)하기로 결정했습니다. 다음 쿼리를 사용하여 고유 제약(unique constraint)이 있는 모든 인덱스를 찾았습니다:

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

데이터 손상 문제를 디버깅하는 사람들을 위한 마지막 팁을 드리겠습니다. 처음에 데이터가 중복되어 가져오기(import)가 실패했을 때, 저는 Rails 콘솔로 들어가서 인덱스 생성 실패의 원인이 된 데이터를 검색했습니다.

하지만 인덱스된 필드를 기준으로 쿼리를 실행하면 Postgres가 결과를 생성하는 데 손상된 인덱스를 사용했습니다! 그래서 초기 쿼리에서는 결과가 1건으로 표시되었고, 나중에 해당 항목을 삭제했을 때 0건으로 표시되었습니다.

전체 테이블 스캔(full table scan)을 수행하는 쿼리가 이긴 겁니다 :slight_smile: