PostgreSQL のメジャーバージョンアップ：logical replication（論理レプリケーション）を使って

Translated using Gemini 3.1 Pro. 人工添削あり.
依然として文面が硬い ORZ... やはり日本語は難しいです！

PostgreSQL のメジャーバージョンアップは今までずっと大きなツッコミどころでした。古いバージョンの PostgreSQL のデータファイルは、そのまま新しいメジャーバージョンの PostgreSQL で開くことができず、手動でアップグレードする必要があります。よく使われるアップグレード方法は以下の通りです：

pg_upgrade ツールを使用したアップグレード: 多くの場合、データファイルの変更はそれほど大きくなく、実際のファイルの大部分はそのまま流用して一部の構造をアップグレードするだけで済むため、pg_upgrade を使えば比較的素早くアップグレードを完了できます。ただしダウンタイムが発生し、また新旧両方のバージョンの postgres メインプログラムを同時に保持する必要があるため、コンテナデプロイでは問題になることがあります。最新の PostgreSQL 18 では何か変わっているようですが；
pg_dumpall と psql: まず前者を使って古いバージョンからデータベース全体をダンプし、後者を使って新しいバージョンで実行しますが、こちらもダウンタイムが発生し、比較的遅いです；
論理レプリケーション（logical replication）を使用する: PostgreSQL はバージョンをまたぐ論理レプリケーションをできるため、新しいデータベースを作成して古いデータベースの論理レプリケーションとして構成し、新しいデータベースが追いついた後に切り替えれば済みます；

今回、自身の Misskey と Matrix の PostgreSQL データベースをアップグレードしようと思い、それぞれ 3 と 2 の方法を使用しました。1 と 2 の方法は比較的簡単で、既に先人たちが詳しく書いているのでここでは割愛します。ここでは論理レプリケーションを用いた経験についてだけ書いてみます。また、この例はメジャーバージョンアップだけでなく、PostgreSQL のマイグレーションにも適用できますが、後者の場合はトンネルを使うなど、ネットワーク安全性を確保する必要があります。

環境

筆者の Misskey のインストールは、Misskey 公式の構成をそのまま流用しているため、PostgreSQL は Docker Compose プロジェクト内の1つのコンテナとしてデプロイされています。古い PostgreSQL のバージョンは 16 で、アップグレード目標バージョンは 18 です。

アップグレードするデータ量は大体 10 GiB 弱で、一番大きなテーブルは note と drive_file のいくつかで、いずれも数百万行、GB レベルです。

概要

Logical WAL Level を有効にする
一時的な新しいバージョンの DB コンテナを起動する
古い DB から schema などの DDL をエクスポートし、新しい DB にインポートする
Publication / Subscription を構成する
初期のテーブル同期が完了するのを待つ
新旧 DB を切り替える
- 古い DB を読み取り専用にするか、アプリケーションサーバーを停止する
- 新しい DB が古い DB に追いつくのを待つ
- 古い DB の sequence 情報を新しい DB にインポートする
- 新旧サーバーを停止し、両者のデータディレクトリを交換する
- compose ファイルを修正し、その中の DB バージョンをアップグレードする
- compose から DB を起動し、正常に開けるかチェックする
- アプリケーションサーバーを再起動する
アプリケーションが正常に動作しているか確認する
Subscription を削除し、古い DB ファイルを削除またはアーカイブする

手順

まずは postgres.conf ファイルを編集し、WAL レベルを logical に設定します。これで後から論理レプリケーションを有効にできるようになります。もし元々設定ファイルを何も使っていなかった場合は、コンテナの中から自分でこのファイルを取り出し、マウントして postgres -c config_file=マウントしたパス のような方法で指定する必要があります。

wal_level = logical

(重启后在 psql 中)
misskey=# show wal_level;
 wal_level 
-----------
 logical
(1 row)

一時的な PostgreSQL 18 インスタンスを起動し、新しいデータディレクトリをマウントし、同じ postgres.conf を使用します。

docker container run \
--volume ./db2:/var/lib/postgresql/18/docker \
--volume ./postgres.conf:/etc/postgres.conf \
--rm \
-e POSTGRES_PASSWORD=Misskey设置里面的数据库密码 \
-e POSTGRES_USER=misskey \
-e POSTGRES_DB=misskey \
--network=misskey_internal_network \
postgres:18 postgres -c config_file=/etc/postgres.conf

ここでいくつか注意点があります：

マウントする新しいデータディレクトリは、以前のバージョンのものと同じディレクトリであってはいけません；
マウントと -c によって設定ファイルを指定しました；
ネットワークは misskey_internal_network を指定しています。この例でのネットワーク名は docker network ls で取得したもので、Misskey の compose.yml で指定されている内部ネットワーク（つまり古いデータベースがあるネットワーク）と一致しているはずです。ただし、新コンテナ内の PostgreSQL が古いデータベースにアクセスできるように、ご自身の状況に合わせてネットワーク構成を調整してください。

別のターミナルウィンドウを開き、まず古いデータベースからデータベース構造 DDL をコピーして新しいデータベースにインポートします：

# 需要修改默认用户名与容器名
docker container exec misskey-db-1 pg_dumpall --globals-only -U misskey > roles.sql  # 对整个实例执行一次
docker container exec misskey-db-1 pg_dumpall --schema-only -U misskey > schema.sql  # 对每个 schema 执行
docker container exec -i 新数据库容器psql -U misskey < roles.sql
docker container exec -i 新数据库容器 psql -U misskey < schema.sql

次に、docker exec -it misskey-db-1 psql -U misskey を使用して古いコンテナの psql に入ります。同様に、ご自身の状況に合わせてコンテナ名とデータベース名を調整してください。psql で、マイグレーション専用のユーザー migrator とデータの PUBLICATION を作成します：

CREATE USER migrator WITH PASSWORD 'misskey' REPLICATION;
GRANT pg_read_all_data TO migrator;
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
\q

そして docker exec -it 先ほど作成した新しいコンテナ名 psql -U misskey で新しいコンテナに入ります。こちらも状況に合わせてコマンドを調整してください。psql で、古いデータベースを指す SUBSCRIPTION を作成します：

CREATE SUBSCRIPTION upgrade_sub_postgres
CONNECTION 'host=db user=migrator dbname=misskey password=misskey'
PUBLICATION upgrade_pub WITH (disable_on_error = true);

もちろん、要件に合わせて host、dbname、password などの値を修正してください。このとき、先ほどの docker run のログを観察してください。エラーが出ていなければ、新しいデータベースが古いデータベースからプルを開始しているはずで、ログからテーブルが1つずつ同期されてきているのが見えるはずです。

同期が正常に行われているか、完了したかをチェックする方法はいくつかあります。まずは以下のようにして現在進行中の同期を確認できます：

misskey=# SELECT datname, relid::REGCLASS AS table_name, bytes_processed, bytes_total FROM pg_stat_progress_copy;
 datname | table_name | bytes_processed | bytes_total 
---------+------------+-----------------+-------------
 misskey | note       |       472128681 |           0
 misskey | drive_file |       582139504 |           0                         
(2 rows)

同期は2つのフェーズに分かれています。第1フェーズでは、SUBSCRIPTION 作成前のデータを COPY 文のような仕組みで新しいデータベースにコピーします。そのため、上記のコマンドは戻り値を返し、初期転送がまだ完了していないことを示します。第2フェーズは、その後継続的に publisher に書き込まれた新しいデータを subscriber に同期させるプロセスです。第1フェーズが完了すると、上記の操作はゼロ行 0 row(s) の出力を示すはずです。もう一つの方法は pg_stat_subscription を読み取ることです：

misskey=# \x on    -- 修改结果展示方式
misskey=# select * from pg_stat_subscription;        
-[ RECORD 1 ]---------+------------------------------
subid                 | 19741
subname               | upgrade_sub_postgres
worker_type           | table synchronization
pid                   | 212
leader_pid            | 
relid                 | 17926
received_lsn          | 
last_msg_send_time    | 2025-10-22 09:03:55.573832+00
last_msg_receipt_time | 2025-10-22 09:03:55.573832+00
latest_end_lsn        | 
latest_end_time       | 2025-10-22 09:03:55.573832+00
-[ RECORD 2 ]---------+------------------------------
subid                 | 19741
subname               | upgrade_sub_postgres
worker_type           | apply
pid                   | 139
leader_pid            | 
relid                 | 
received_lsn          | F4/80E05ED0
last_msg_send_time    | 2025-10-22 09:27:31.514561+00
last_msg_receipt_time | 2025-10-22 09:27:31.515095+00
latest_end_lsn        | F4/80E05ED0
latest_end_time       | 2025-10-22 09:27:31.514561+00

table synchronization 状態の worker がまだ存在することにお気づきかと思いますが、これはテーブルの第1フェーズの同期がまだ完了していないことを説明しています。

テーブルの第1フェーズの同期がすべて完了し、同時に第2フェーズの同期も大体追いついてきたら、いよいよ switchover を行うタイミングです。これもこのシステムにおけるごくわずかなダウンタイムの瞬間です。実際には自動化 + pgBouncer を使うことでこのプロセスのダウンタイムを最小限に抑えることができますが、筆者は手動で行いました。このステップでやることの概要は既に前文で書いてあり、pgBouncer を使うやり方は参考文献に書いてあります。

まずは古いデータベースを読み取り専用にするか、アプリケーションサーバーを終了するか、または pgBouncer を設定して書き込みを停止させます。ここからダウンタイムに入ります。次に新しいデータベースが追いつくのを待ちます。ここで筆者は出力を保存するのを忘れてしまったのでここに載せられません。

-- 首先在旧数据库（publish 端）查看最后一个 WAL LSN
misskey=# select pg_current_wal_lsn();

-- 再在新数据库（subscribe 端）查看当前的 WAL LSN 是否一致
misskey=# select * from pg_stat_subscription;

-[ RECORD 1 ]---------+------------------------------
subid                 | 19741
subname               | upgrade_sub_postgres
worker_type           | apply
pid                   | 139
leader_pid            | 
relid                 | 
received_lsn          | F4/80EB8758
last_msg_send_time    | 2025-10-22 09:27:56.641478+00
last_msg_receipt_time | 2025-10-22 09:27:56.648834+00
latest_end_lsn        | F4/80EB8758
latest_end_time       | 2025-10-22 09:27:56.641478+00

読み書き量が大きくないデータベースなら、すぐに同期が完了するはずです。次に、古いデータベース内のシーケンスデータ（自動インクリメントのフィールドなどに使用）をインポートします。この部分は replicate できず、最新の sequence は最初の DDL にも含まれていません：

-- 在旧数据库的 psql 上
misskey=# COPY (
  SELECT format('SELECT setval(''%I.%I'', %s);',
           schemaname, sequencename, last_value + 1000
         )
    FROM pg_sequences where last_value is not null
) TO '/tmp/sequences-postgres.sql';

再退到主机上将其拷出来并应用在新数据中（注意容器名）
docker container cp misskey-db-1:/tmp/sequences-postgres.sql .
docker container exec -i 新数据库容器psql -U misskey < sequences-postgres.sql # 当然这步也能直接 copy paste 到新数据库的 psql 窗口

新旧2つのデータベースコンテナを停止し（docker container stop などを使用し、強制停止はしてはいけない）、新旧のデータファイル名を交換します（例えば mv db db_old; mv db2 db）。その後、docker compose などの「正式な構成」の中のサーバーバージョンを新しいものに修正し、最後に docker compose up db を単独使用して新しいバージョンのデータベースを作成・起動し、正常に起動できるかチェックします。このステップを pgBouncer で行うと compose とそこまで「エレガント」に統合できないかもしれませんが、伝統的な、OS に直接 DB をインストールしているケースにおいては、pgBouncer ならデータベースのアドレスとポートを更新して書き込みを再有効化するだけで、一瞬で切り替えが完了します。

最後に、アプリケーションサーバーを再起動し、データベースの読み書きが正常におこなえるかチェックします。成功したら、正式なデータベースのコンテナに入り、subscription を削除します。

ALTER SUBSCRIPTION upgrade_sub_postgres DISABLE;
ALTER SUBSCRIPTION upgrade_sub_postgres SET (slot_name = NONE);
DROP SUBSCRIPTION upgrade_sub_postgres;

これでデータベースのアップグレードは完了です。