Sharkey 마이그레이션 롤백

환경

얼마 전 Firefish에서 Sharkey로 마이그레이션한 다음, Iceshrimp로 다시 이전하려는 상황이다. Sharkey 2024.9.0

일부 컬럼 백업

movedToUri, alsoKnownAs, (isIndexable,) speakAsCat, preventAiLearning, silencedHosts 는 iceshrimp(/firefish)에서 사용할 수 있지만, 마이그레이션 롤백 중에 소실된다. 이를 미리 백업한다.

ALTER TABLE "user" ADD "ices_movedToUri" character varying(512);
ALTER TABLE "user" ADD "ices_alsoKnownAs" TEXT;
COMMENT ON COLUMN "user"."ices_movedToUri" IS 'The URI of the new account of the User';
COMMENT ON COLUMN "user"."ices_alsoKnownAs" IS 'URIs the user is known as too';
ALTER TABLE "user" ADD "ices_speakAsCat" boolean NOT NULL DEFAULT true;
COMMENT ON COLUMN "user"."ices_speakAsCat" IS 'Whether to speak as a cat if isCat.';
ALTER TABLE "user_profile" ADD "ices_preventAiLearning" boolean NOT NULL DEFAULT true;
ALTER TABLE "meta" ADD "ices_silencedHosts" character varying(1024) array NOT NULL DEFAULT '{}';
ALTER TABLE "meta" ADD "ices_ToSUrl" character varying(512);

UPDATE "user" SET "ices_movedToUri" = "movedToUri" WHERE "movedToUri" IS NOT NULL;
UPDATE "user" SET "ices_alsoKnownAs" = "alsoKnownAs" WHERE "alsoKnownAs" IS NOT NULL;
UPDATE "user" SET "ices_speakAsCat" = COALESCE("speakAsCat", false);
UPDATE "user_profile" SET "ices_preventAiLearning" = COALESCE("preventAiLearning", true);
UPDATE "meta" SET "ices_silencedHosts" = COALESCE("silencedHosts",'{}');
UPDATE "meta" SET "ices_ToSUrl" = COALESCE("termsOfServiceUrl",'{}');

마이그레이션 롤백

Firefish가 분기되는 1657346559800-active-email-validation.ts 직후의 Sharkey 마이그레이션, 1664694635394-turnstile.js 까지의 마이그레이션에 대해 롤백을 진행한다. 일부 롤백 스크립트가 정상적으로 작동하지 않는데, 그런 경우 아래 각 문단의 SQL을 수동으로 실행하여야 한다. 상당히 긴 노가다 작업이다...

docker compose run -i web sh
pnpm run revert  # 1664694635394-turnstile.js가 revert될 때 까지 반복

RemoveAntennaNotify1716450883149

ALTER TABLE "antenna" ADD "notify" boolean NOT NULL DEFAULT FALSE;
DELETE FROM "migrations" WHERE name = 'RemoveAntennaNotify1716450883149';

DeleteCreatedAt1697420555911

일부 케이스에서 날짜가 정상 변환되지 않는 문제가 발생한다. 답글에서 Hoto-Cocoa 씨가 제시한 코드로 치환한 뒤 실행해야 한다. https://gist.github.com/u1-liquid/cf1774c8c891443132bdcf02ca3ab083

Clean1696332072038

롤백 과정에서 엄한 Index를 지우려고 하는데 의도를 알 수 없음...

ALTER TABLE "meta" ALTER COLUMN "preservedUsernames" SET DEFAULT '{admin,administrator,root,system,maintainer,host,mod,moderator,owner,superuser,staff,auth,i,me,everyone,all,mention,mentions,example,user,users,account,accounts,official,help,helps,support,supports,info,information,informations,announce,announces,announcement,announcements,notice,notification,notifications,dev,developer,developers,tech,misskey}';
DELETE FROM "migrations" WHERE name = 'Clean1696332072038';

NotificationRecieveConfig1695944637565

ALTER TABLE "user_profile" DROP COLUMN "notificationRecieveConfig";
ALTER TABLE "user_profile" ADD "mutingNotificationTypes" "public"."user_profile_mutingnotificationtypes_enum" array NOT NULL DEFAULT '{}';
ALTER TABLE "user_profile" ALTER COLUMN "mutingNotificationTypes" TYPE "public"."user_profile_mutingnotificationtypes_enum"[] USING "mutingNotificationTypes"::"text"::"public"."user_profile_mutingnotificationtypes_enum"[];
DELETE FROM "migrations" WHERE name = 'NotificationRecieveConfig1695944637565';

largerImageComment1680969937000

미디어 설명에 할당하는 512글자를 8192글자로 확장하는 비가역 마이그레이션이다.

DELETE FROM "migrations" WHERE name = 'largerImageComment1680969937000';

dropGroup1676434944993

related with cleanup1680582195041 sibal https://gist.github.com/ltlapy/f7219bc87b019de0682ff58a6c985090

removeLastCommunicatedAt1672704017999

related with DeleteCreatedAt1697420555911

ALTER TABLE "instance" ADD "lastCommunicatedAt" TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE '1980-01-01 00:00:00+00';
DELETE FROM "migrations" WHERE name = 'removeLastCommunicatedAt1672704017999';

CleanUp1696581429196

당장 롤백 과정에서 오류는 나지 않겠지만, Iceshrimp 사용 시 오류가 발생하므로 수동으로 복원한다.

-- wordMute1595771249699
CREATE TABLE "muted_note" ("id" character varying(32) NOT NULL, "noteId" character varying(32) NOT NULL, "userId" character varying(32) NOT NULL, CONSTRAINT "PK_897e2eff1c0b9b64e55ca1418a4" PRIMARY KEY ("id"));
CREATE INDEX "IDX_70ab9786313d78e4201d81cdb8" ON "muted_note" ("noteId") ;
CREATE INDEX "IDX_d8e07aa18c2d64e86201601aec" ON "muted_note" ("userId") ;
CREATE UNIQUE INDEX "IDX_a8c6bfd637d3f1d67a27c48e27" ON "muted_note" ("noteId", "userId") ;
--ALTER TABLE "user_profile" ADD "enableWordMute" boolean NOT NULL DEFAULT false;
--ALTER TABLE "user_profile" ADD "mutedWords" jsonb NOT NULL DEFAULT '[]';
--CREATE INDEX "IDX_3befe6f999c86aff06eb0257b4" ON "user_profile" ("enableWordMute") ;
ALTER TABLE "muted_note" ADD CONSTRAINT "FK_70ab9786313d78e4201d81cdb89" FOREIGN KEY ("noteId") REFERENCES "note"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "muted_note" ADD CONSTRAINT "FK_d8e07aa18c2d64e86201601aec1" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
-- wordMute21595782306083
--CREATE TYPE "muted_note_reason_enum" AS ENUM('word', 'manual', 'spam', 'other');
ALTER TABLE "muted_note" ADD "reason" "muted_note_reason_enum" NOT NULL;
CREATE INDEX "IDX_636e977ff90b23676fb5624b25" ON "muted_note" ("reason") ;
-- comments1605408971051
COMMENT ON COLUMN "muted_note"."noteId" IS 'The note ID.';
COMMENT ON COLUMN "muted_note"."userId" IS 'The user ID.';
COMMENT ON COLUMN "muted_note"."reason" IS 'The reason of the MutedNote.';

Iceshrimp 마이그레이션 확인

우선 firefish 로 되돌린 다음, Iceshrimp 마이그레이션 도움말에 적힌 사항을 수행한다.

Iceshrimp를 시작하기 전, Postgresql에서 다음 쿼리를 수행하여 앞서 백업한 정보를 가져온다.

ALTER TABLE "user" RENAME COLUMN "ices_movedToUri" TO "movedToUri";
ALTER TABLE "user" RENAME COLUMN "ices_alsoKnownAs" TO "alsoKnownAs";
ALTER TABLE "user" RENAME COLUMN "ices_speakAsCat" TO "speakAsCat";
ALTER TABLE "user_profile" RENAME COLUMN "ices_preventAiLearning" TO "preventAiLearning";
ALTER TABLE "meta" RENAME COLUMN "ices_silencedHosts" TO "silencedHosts";
ALTER TABLE "meta" RENAME COLUMN "ices_ToSUrl" TO "ToSUrl";

그외

Firefish - Sharkey - Iceshrimp 의 경로를 거치면서 특수 마이그레이션 SQL을 실행하지 않아 발생한 문제를 해결하기 위한 쿼리. 이 문서에서 설명한 설정 백업-복원을 거쳤다면 실행하지 않아도 된다.

ALTER TABLE "meta" ADD "silencedHosts" character varying(1024) array NOT NULL DEFAULT '{}';
-- * --
ALTER TABLE "meta" ADD "ToSUrl" character varying(512);
ALTER TABLE "user" ADD "movedToUri" character varying(512);
ALTER TABLE "user" ADD "alsoKnownAs" TEXT;
COMMENT ON COLUMN "user"."movedToUri" IS 'The URI of the new account of the User';
COMMENT ON COLUMN "user"."alsoKnownAs" IS 'URIs the user is known as too';
--
ALTER TABLE "user" ADD "speakAsCat" boolean NOT NULL DEFAULT true;
COMMENT ON COLUMN "user"."speakAsCat" IS 'Whether to speak as a cat if isCat.';
--
ALTER TABLE "user_profile" ADD "preventAiLearning" boolean NOT NULL DEFAULT true;

ltlapy/Migrating-Sharkey-to-Iceshrimp.md