第26回：PostgreSQLでサポートされている2つのレプリケーション機能の違いと使い分け

１．ストリーミングレプリケーションとロジカルレプリケーションの仕組み

ストリーミングレプリケーションとロジカルレプリケーションの仕組みについて解説します。

●ストリーミングレプリケーション
ストリーミングレプリケーションは、データベースクラスタ単位でWAL（更新履歴）を利用し、データの同期を実現します。
マスタ側で出力されたWALをスタンバイ側に転送し、スタンバイ側はWALをリカバリします。
スタンバイ側は設定により、参照用として利用することができます。

また、マスタとスタンバイでPostgreSQLのマイナーバージョンが違ってもレプリケーションできますが、メジャーバージョンが違うとレプリケーションができません。
OSやCPUアーキテクチャが異なる場合にもレプリケーションができません。

●ロジカルレプリケーション
ロジカルレプリケーションは、データベースやテーブル単位でWALを利用しデータの同期を実現します。
ストリーミングレプリケーションと同じくWALを利用したレプリケーションですが、WALに記載されている変更情報を論理的なレベルに変換（デコード）してから、転送する仕組みになります。

そして、パブリケーションとサブスクリプションにより、レプリケーションの対象を制御することができます。
ストリーミングレプリケーションと違った仕組みにすることで、自由度の高いレプリケーションを実装することができます。

・異なるメジャーバージョン間のレプリケーション
・テーブル単位でのレプリケーション
・スタンバイへの書き込み

一方、自由度が高くなることと引き換えに、ロジカルレプリケーションには制約事項と注意事項があります。

・DDLコマンドと一部のオブジェクトがレプリケーションされない
・スタンバイ側が書き込み可能であるため、マスタから転送されてきたデータが一意性制約などの制約に違反するとレプリケーションが停止する（コンフリクト）

２．ストリーミングレプリケーションとロジカルレプリケーションの特徴比較

仕組みの違いの中でもいくつか特徴を記載しましたが、表形式で比較すると下記のようになります。
堅牢なストリーミングレプリケーションと、自由度の高いロジカルレプリケーションの2つのレプリケーション機能を、利用ケースに応じて適切に使い分けることが重要です。

３．レプリケーション構築手順

PC上のVirtualBoxを使用し、CentOS8にPostgeSQL13.3をインストールし、同一サーバー内にポートを5432と5433の2インスタンス構築しました。
2つのレプリケーション機能について、最低限必要な設定で構築する例を紹介します。

＜補足＞

• PostgreSQLのインストールは、https://www.postgresql.org/でOSとPostgreSQLのバージョン情報を入力することで、インストール時に必要なコマンドが取得できます。
• 同一サーバー内に2インスタンス作成する方法は、別のPGDATAを指定してinitdbを実行し、postgresql.confにてポートを変えて起動することで構築できます。

3-1 ストリーミングレプリケーション

下記の手順でストリーミングレプリケーションの同期構成を構築します。

今回の例では、ストリーミングレプリケーションを構築した後に新規テーブルtbl_aの作成とデータ投入を行い、スタンバイ側にレプリケーションされることを確認します。

①マスタサーバのパラメータ設定

マスタサーバの設定ファイルを下記の通り変更し、再起動します。

[postgres@localhost ~]$ vi /data/pgsql-5432/postgresql.conf
synchronous_standby_names = 'pgsql5433'　　　　　←「3.スタンバイサーバのパラメータ設定」のapplication_nameで指定する名前を指定
[root@localhost ~]# systemctl restart postgresql-5432.service

②マスタサーバのベースバックアップをスタンバイ側のPGDATAに取得

マスタサーバのベースバックアップをスタンバイ側のPGDATAに取得することで、2つのPostgreSQLのデータの初期同期を行います。

[postgres@localhost ~]$ rm -rf /data/pgsql-5433
[postgres@localhost ~]$ mkdir /data/pgsql-5433
[postgres@localhost ~]$ chmod 700 /data/pgsql-5433
[postgres@localhost ~]$ pg_basebackup -p 5432 -R -D /data/pgsql-5433

③スタンバイサーバのパラメータ設定

スタンバイサーバの設定ファイルを下記の通り変更し、再起動します。

[postgres@localhost ~]$ vi /data/pgsql-5433/postgresql.auto.conf
primary_conninfo = 'user= ～省略～ application_name=pgsql5433'　　　　　←マスタのsynchronous_standby_namesと同じapplication_nameを追記
[postgres@localhost ~]$ vi /data/pgsql-5433/postgresql.conf
port = 5433 　　　　　←マスタのポートに設定されているので5433に変更
# synchronous_standby_names = 'pgsql5433'　　　　　←マスタに記載したsynchronous_standby_namesをコメントアウト

④スタンバイサーバの起動

[root@localhost data]# systemctl status postgresql-5433.service

⑤レプリケーションの確認

[postgres@localhost ~]$ psql test -p 5432
psql (13.3)
helpでヘルプを表示します。

test=# CREATE TABLE tbl_a (id int primary key);
CREATE TABLE
test=# INSERT INTO tbl_a SELECT generate_series(1,5);
INSERT 0 5
test=# SELECT * FROM tbl_a;

test=# ¥q
[postgres@localhost ~]$ psql test -p 5433
psql (13.3)	
helpでヘルプを表示します。

test=# SELECT * FROM tbl_a;　　　　　←スタンバイにテーブル定義とデータがレプリケーションされていることを確認

3-2 ロジカルレプリケーション

下記の手順でロジカルレプリケーションを構築します。

今回の例では、マスタ（パブリッシャー側）サーバに新規テーブルtbl_a、tbl_b、tbl_cを作成し、レプリケーション対象としてテーブルtbl_a、tbl_bのデータの投入と更新操作のみを指定します（今回は操作によるフィルタリングを確認するために意図的に削除は未指定とします）。
レプリケーションの確認としては、tbl_aに対してデータ投入がレプリケーションされることと、データ削除はレプリケーションされないことを確認します。

①マスタ（パブリッシャー側）サーバのパラメータ設定

マスタ（パブリッシャー側）サーバの設定ファイルを下記の通り変更し、再起動します。

[postgres@localhost ~]$ vi /data/pgsql-5432/postgresql.conf
wal_level = logical　　　　　←replicaがデフォルトになっているため
[root@localhost ~]# systemctl restart postgresql-5432.service

②パブリケーションの作成

マスタ（パブリッシャー側）サーバに対象テーブルと操作を指定して、パブリケーションを作成します。

[postgres@localhost ~]$ psql test -p 5432
psql (13.3)
helpでヘルプを表示します。

test=# CREATE TABLE tbl_a (id int primary key);
CREATE TABLE
test=# CREATE TABLE tbl_b (id int primary key);
CREATE TABLE
test=# CREATE TABLE tbl_c (id int primary key);
CREATE TABLE
test=# INSERT INTO tbl_a SELECT generate_series(1,5);
INSERT 0 5
test=# INSERT INTO tbl_b SELECT generate_series(1,5);
INSERT 0 5
test=# INSERT INTO tbl_c SELECT generate_series(1,5);
INSERT 0 5
test=# CREATE PUBLICATION mypub FOR TABLE tbl_a,tbl_b WITH (publish = 'insert,update');　　　　　←パブリケーション作成時にレプリケーション対象テーブルと操作を指定 ※今回は登録と更新を同期し、削除を同期しない設定
CREATE PUBLICATION
test=# SELECT * FROM pg_publication_tables;　　　　　←レプリケーション対象テーブルの確認

test=# SELECT * FROM pg_publication;　　　　　←レプリケーション対象操作の確認

③サブスクリプションの作成

スタンバイ（サブスクライバー側）サーバにレプリケーション対象テーブルとデータ登録を行い、サブスクリプションを定義します。

[postgres@localhost ~]$ psql -p 5433 -c "CREATE DATABASE test"
CREATE DATABASE
[postgres@localhost ~]$ psql -p 5433 test
psql (13.3)
helpでヘルプを表示します。

test=# CREATE TABLE tbl_a (id int primary key);     ←DDLはレプリケーションされないためテーブルをあらかじめ作成（データは不要）
CREATE TABLE
test=# CREATE TABLE tbl_b (id int primary key);
CREATE TABLE
test=# CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost port=5432 dbname=test' PUBLICATION mypub;     ←サブスクリプション作成時にパブリッシャーへの接続情報とパブリケーションを指定
NOTICE:  発行サーバでレプリケーションスロット"mysub"を作成しました
CREATE SUBSCRIPTION
test=# SELECT * FROM pg_subscription;     ←サブスクリプションが登録されていることを確認

④レプリケーションの確認

スタンバイ（サブスクライバー側）サーバに設定通り（登録・更新→同期対象、削除→同期対象外）にtbl_aとtbl_bがレプリケーションされているか、確認します。

[postgres@localhost ~]$ psql -p 5433 test
psql (13.3)	
helpでヘルプを表示します。
	
test=# select * from tbl_a;　　　　　←データがレプリケーションされていることを確認

test=# select * from tbl_b;

test=# select * from tbl_c;　　　　　←レプリケーション対象にしていないtbl_cはレプリケーションされていない
ERROR:  リレーション"tbl_c"は存在しません
行 1: select * from tbl_c;
test=# ¥q
[postgres@localhost ~]$ psql -p 5432 test
psql (13.3)
helpでヘルプを表示します。

test=# INSERT INTO tbl_a VALUES (10);　　　　　←マスタ（パブリッシャー側）サーバでデータ投入
INSERT 0 1
test=# SELECT * FROM tbl_a;

test=# ¥q
[postgres@localhost ~]$ psql -p 5433 test
psql (13.3)
helpでヘルプを表示します。

test=# SELECT * FROM tbl_a;　　　　　←スタンバイ（サブスクライバー側）サーバにレプリケーションされている

test=# ¥q
[postgres@localhost ~]$ psql -p 5432 test
psql (13.3)
helpでヘルプを表示します。

test=# DELETE FROM tbl_a WHERE id <= '5' ;　　　　　←マスタ（パブリッシャー側）サーバでデータ削除（パブリケーションに指定していない操作）
DELETE 5 
test=# SELECT * FROM tbl_a;

test=# ¥q
[postgres@localhost ~]$ psql -p 5433 test
psql (13.3)
helpでヘルプを表示します。

test=# SELECT * FROM tbl_a;　　　　　←スタンバイ（サブスクライバー側）サーバにレプリケーションされていない ※パブリケーションにdelete操作も同期対象と指定すれば同期可能

４．おわりに

2つのレプリケーション機能の仕組みと、特徴の違いによるレプリケーション機能の使い分けについて解説しました。
特徴の違う2つのレプリケーションを要件に合わせて使い分けや共存させることで、最適なPostgreSQL環境を実現することができます。