Dockerによる、PostgreSQLのレプリケーション設定

先日、お客様から「PostgreSQLのレプリケーション設定」の問い合わせがあり、実際に設定してみたので、備忘録を兼ねて書いておこうかなと。

1.Dockerネットワークの作成

bash
docker network create pg-network

2.パブリッシャー(送信側)コンテナの作成

bash
docker run -d –name pg-publisher –network pg-network -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 postgres:13 -c wal_level=logical

3.サブスクライバー(受信側)コンテナの作成

bash
docker run -d –name pg-subscriber –network pg-network -e POSTGRES_PASSWORD=mysecretpassword -p 5433:5432 postgres:13

4.パブリッシャーの設定

bash
docker exec -it pg-publisher psql -U postgres

sql
CREATE DATABASE mydb;
\c mydb;
CREATE PUBLICATION mypub FOR ALL TABLES;
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD ‘replicatorpass’;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replicator;

5.サブスクライバーの設定

bash
docker exec -it pg-subscriber psql -U postgres

sql
CREATE DATABASE mydb;
\c mydb;
CREATE SUBSCRIPTION mysub CONNECTION ‘host=pg-publisher dbname=mydb user=replicator password=replicatorpass’ PUBLICATION mypub;

6.テーブルの追加と確認

パブリッシャー側で新しいテーブルを作成します

sql
CREATE TABLE newtable (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO newtable (data) VALUES (‘New table data’);

サブスクライバー側でも、同じテーブルを作成して、パブリケーションを更新します

sql
CREATE TABLE newtable (id SERIAL PRIMARY KEY, data TEXT);
ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

その後、新しいテーブルが存在し、データが反映されていることを確認します

sql
SELECT * FROM newtable;

この設定により、以下のような動作が可能になります。

1. パブリッシャー側でFOR ALL TABLESを指定しているため、新しく作成されたテーブルも自動的にパブリケーションに含まれます。
2. サブスクライバー側でALTER SUBSCRIPTION mysub REFRESH PUBLICATIONを実行することで、新しいテーブルの情報がサブスクライバーに反映されます。
3. ALTER DEFAULT PRIVILEGES の設定により、将来作成されるテーブルに対しても自動的にreplicatorロールにSELECT権限が付与されます。

注意点として、新しいテーブルが追加されるたびに、サブスクライバー側で

sql
ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

を実行する必要があります。完全に自動化するには、この操作をトリガーやスケジュールされたジョブで行う必要があります。

以上となりますが、レプリケーションはシステムの負荷分散、障害対策、情報基盤への受け渡しと、いろいろと便利ですね。また、Dockerについては、今回をはじめとした技術検証、開発など、これまた便利ですね。皆様もどうぞ！