バックアップをしたい

自分がメインに使っているほぼお一人様Misskeyインスタンスのsns.copi.pe、バックアップを取っていないなど
相当管理が杜撰です。
ほぼ自分しか使っていないのでええかスタンスです。

ただまあ、運用して1年以上が経過しており流石にバックアップぐらいはちゃんと取得できるようにするか・・・。
と思ったため、バックアップ方法を模索してみることにしました。

画像や動画などのドライブのファイル達はrsyncなどを使用して別のデバイスにバックアップを取れば良いので楽ですが、
ノートやユーザーなど色々なデーターを保持しているデータベースに関しては、rsyncで雑にバックアップを取れないので、別の方法を使用するしかありません。

pg_dump

Misskeyで使用されているDBはPostgreSQLで、PostgreSQLにはpg_dumpと呼ばれるツールが用意されています。
pg_dump - 日本PostgreSQLユーザ会

Dockerのコンテナイメージにもpg_dumpは含まれており、以下のように動いているコンテナにexecを使うことでコンテナ内でも実行できます。

docker exec コンテナ名 pg_dumpall -c -U user > dump.sql

ダンプするとSEGVる

しかし、自分の環境では以下のようなエラーが発生し、ダンプが正常終了しません。

$ docker exec [container] pg_dumpall -c -U misskey > dump.sql
pg_dump: error: Dumping the contents of table "drive_file" failed: PQgetCopyData() failed.
pg_dump: detail: Error message from server: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: detail: Command was: COPY public.drive_file (id, "userId", "userHost", md5, name, type, size, comment, properties, "storedInternal", url, "thumbnailUrl", "webpublicUrl", "accessKey", "thumbnailAccessKey", "webpublicAccessKey", uri, src, "folderId", "isSensitive", "isLink", blurhash, "webpublicType", "requestHeaders", "requestIp", "maybeSensitive", "maybePorn") TO stdout;
pg_dumpall: error: pg_dump failed on database "misskey", exiting

$

「Error message from server: server closed the connection unexpectedly」、「detail: Command was: COPY public.drive_file」といったエラー文からpublic.drive_fileというテーブルのダンプ中にサーバーから接続断されてダンプが異常終了したという事がわかります。

PostgreSQLサーバー側のエラーは以下の通り。

db-1  | 2024-08-04 12:24:04.578 UTC [1] LOG:  server process (PID 10955) was terminated by signal 11: Segmentation fault
db-1  | 2024-08-04 12:24:04.578 UTC [1] DETAIL:  Failed process was running: COPY public.drive_file (id, "userId", "userHost", md5, name, type, size, comment, properties, "storedInternal", url, "thumbnailUrl", "webpublicUrl", "accessKey", "thumbnailAccessKey", "webpublicAccessKey", uri, src, "folderId", "isSensitive", "isLink", blurhash, "webpublicType", "requestHeaders", "requestIp", "maybeSensitive", "maybePorn") TO stdout;

どうやらSEGVってDBが異常終了しているようです。

SEGVの原因を探る

データベースの操作を専門としてやってきていなかったので、イロハのイぐらいしかわかっていません。
PostgreSQLも何度か運用はしていますが、ゴリゴリSQL書いたりログを読んでなにかしたといったような経験もありません。

とはいえ、ダンプが失敗する異常なDBのバックアップをせずに放置するもの流石に気持ち悪いので、原因調査や解決方法を考えるしかありません。

2日程度考えたところ、特定レコードに不正な値が代入されているのでは？ということに考えつきました。

ダンプは途中で異常終了していますが、終了直前まではファイルに書き込まれているため、tailして最後の行を調べます。

idが9fz8y84ujnのレコードまでは正常にダンプできていそうです。順当に考えるとその次のレコードは適切に参照できないはずです。

次のレコードを知りたいのですが、PostgreSQLのSQL文は詳しくないためChatGPTに聞くことにしました。

答えとして以下のSQL文が返ってきました。

SELECT next_id
FROM (
  SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
  FROM drive_file
) sub
WHERE id = '9fz8y84ujn';

各命令をさらーっと確認したところ問題なさそうだったため、実行しました。(結構冗長な気はするけど・・・。)

  next_id
------------
 9fz8yif3k5
(1 row)

idが9fz8yif3k5のレコードが良くないということに。

レコードの調査

当該レコードの各カラムの値を参照するにはSELECT カラム名 FROM テーブル名 WHERE id = 'ID';というSQL文を使います。

テーブルのカラム一覧は\d+ テーブル名で取得できます。

ファイルのURLが格納されるカラムに変な文字列が格納されているため、十中八九このIDのレコードが悪そうです。

misskey=# select id, url from drive_file WHERE id = '9fz8yif3k5';
     id     |    url
------------+------------
 9fz8yif3k5 | 9fys5gntjw
(1 row)

このレコードの他のカラムの値を参照してみてもinvalid memory alloc request sizeとエラーが発生したり、
blurhashの値に関してはDBが異常終了しています。

misskey=# select id, "accessKey" from drive_file WHERE id = '9fz8yif3k5';
ERROR:  invalid memory alloc request size 18446744073709551613
misskey=# select id, "thumbnailAccessKey" from drive_file WHERE id = '9fz8yif3k5';
ERROR:  invalid memory alloc request size 18446744073709551613
misskey=# select id, "uri" from drive_file WHERE id = '9fz8yif3k5';
ERROR:  invalid memory alloc request size 18446744073709551613
misskey=# select id, "src" from drive_file WHERE id = '9fz8yif3k5';
ERROR:  invalid memory alloc request size 18446744073709551613
misskey=# select id, "blurhash" from drive_file WHERE id = '9fz8yif3k5';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>

このレコードのホストは.cfのようです。

misskey=# select id, "userHost" from drive_file WHERE id = '9fz8yif3k5';
     id     |  userHost
------------+------------
 9fz8yif3k5 | misskey.cf
(1 row)

このmisskey.cfはドメイン停止されやむなくサービス終了してしまったMisskeyインスタンスです。
このインスタンスの他のファイルも閲覧不可能なため、このレコードを消してしまっても問題なさそうです。

レコード削除

レコードを削除するにはDELETE文を使います。
SELECT文と同じようにFROMとWHEREでテーブルとレコード指定して削除します。

misskey=# DELETE FROM drive_file WHERE id = '9fz8yif3k5';
DELETE 1
misskey=#

おわり

これでdrive_fileテーブルは問題なくダンプを完了することができました。
残念ながら、他のテーブルも何かしらが壊れているらしくフルダンプすることは叶っていないのでまた原因を探す必要がありそうです。

$ docker exec [container] pg_dumpall -c -U misskey > dump.sql
pg_dump: error: Dumping the contents of table "note" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  cache lookup failed for type 1075
pg_dump: detail: Command was: COPY public.note (id, "replyId", "renoteId", text, name, cw, "userId", "localOnly", "renoteCount", "repliesCount", reactions, visibility, uri, "fileIds", "attachedFileTypes", "visibleUserIds", mentions, "mentionedRemoteUsers", emojis, tags, "hasPoll", "userHost", "replyUserId", "replyUserHost", "renoteUserId", "renoteUserHost", url, "channelId", "threadId", "reactionAcceptance", "clippedCount", "reactionAndUserPairCache") TO stdout;
pg_dumpall: error: pg_dump failed on database "misskey", exiting
$
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則 Software Design plus

［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則 Software Design plus