1台のPostgreSQLインスタンス内で、データベースaaaから別のデータベースbbbへのロジカルレプリケーションを試してみます。
PostgreSQLの準備と起動
❶ PostgreSQLバージョン10をインストールします。
❷ DBクラスタを作成します。
$ initdb -D data --locale=C --encoding=UTF-8
❸ postgresql.confに以下の設定変更をします。これは、ロジカルレプリケーションを有効にするための設定です。設定パラメータwal_levelの詳細はこちらを参照してください。
$ emacs data/postgresql.conf
wal_level = logical
❹ PostgreSQLインスタンスを起動します。
$ pg_ctl -D data start
ロジカルレプリケーション対象のデータベースとテーブルの作成
❶ ロジカルレプリケーションの元と先になるデータベースaaaとbbbを作成します。今回は、データベースaaaからbbbにロジカルレプリケーションします。
$ psql
CREATE DATABASE aaa;
CREATE DATABASE bbb;
❷ 各データベースにロジカルレプリケーション対象のテーブルtestを作成します。以降、実行例に\c aaaと\c bbbが出てきますが、これらはそれぞれデータベースaaaとbbbに接続するpsqlメタコマンドです。データベースaaaにログイン中に\c bbbを実行することで、データベースbbbにログインできます。
\c aaa
CREATE TABLE test (id INT PRIMARY KEY);
ALTER TABLE test REPLICA IDENTITY DEFAULT;
\c bbb
CREATE TABLE test (id INT PRIMARY KEY);
ALTER TABLE test REPLICA IDENTITY DEFAULT;
ロジカルレプリケーションの設定
❶ データベースaaaで、テーブルtestをレプリケーション対象とするPUBLICATION(名前はaaapub)を作成します。
\c aaa
CREATE PUBLICATION aaapub FOR TABLE test;
❷ データベースaaaで、ロジカルレプリケーション用のレプリケーションスロット(名前はaaaslot)を作成します。スロットのプラグインには、ロジカルレプリケーションが使うpgoutputを指定します。
\c aaa
SELECT pg_create_logical_replication_slot('aaaslot', 'pgoutput');
❸ データベースbbbでSUBSCRIPTION(名前はbbbsub)を作成して、データベースaaaからbbbへのテーブルtestのロジカルレプリケーションを開始します。slot_nameには先ほど作成したレプリケーションスロットaaaslotを指定し、この時点でスロットは作成済のためcreate_slotにはfalseを指定します。
\c bbb
CREATE SUBSCRIPTION bbbsub CONNECTION 'dbname=aaa' PUBLICATION aaapub WITH (slot_name=aaaslot, create_slot=false);
注意
同一インスタンス内でロジカルレプリケーションを行う場合は、レプリケーションスロットとSUBSCRIPTIONを別々に作成してください。CREATE SUBSCRIPTIONコマンドでSUBSCRIPTIONと同時にレプリケーションスロットを作成しようとすると、同一インスタンスの場合、CREATE SUBSCRIPTIONがハングアップします。CREATE SUBSCRIPTIONとそのコマンドによって起動されるwalsenderとが互いに処理の完了を待ち合わせてしまいハングアップするようです。この問題があることは、PostgreSQL10.0で確認しています。ただし、今後のバージョンアップでこの問題は解決される可能性があります。
ロジカルレプリケーションの確認
❶ データベースaaaでテーブルtestに1件レコードを挿入します。
\c aaa
INSERT INTO test VALUES (1);
❷ データベースbbbでテーブルtestに1件レコードが存在するのを確認します。
\c bbb
SELECT * FROM test;
このSELECTの結果は以下となるはずです。
id
----
1
(1 row)
【付録】循環するロジカルレプリケーション
これまでの設定でデータベースaaaからbbbにロジカルレプリケーションしている中、bbbからaaaへのロジカルレプリケーションも設定して、レプリケーションの循環を試してみます。
❶ データベースbbbで、テーブルtestをレプリケーション対象とするPUBLICATION(名前はbbbpub)を作成します。
\c bbb
CREATE PUBLICATION bbbpub FOR TABLE test;
❷ データベースbbbで、ロジカルレプリケーション用のレプリケーションスロット(名前はbbbslot)を作成します。
\c bbb
SELECT pg_create_logical_replication_slot('bbbslot', 'pgoutput');
❸ データベースaaaとbbbで、テーブルtestを空にします。これは、データベースbbbからaaaへのロジカルレプリケーションの開始時に、データの制約違反が発生するのを避けるためです。
\c aaa
TRUNCATE test;
\c bbb
TRUNCATE test;
❹ データベースaaaでSUBSCRIPTION(名前はaaasub)を作成して、データベースbbbからaaaへのテーブルtestのロジカルレプリケーションを開始します。
\c aaa
CREATE SUBSCRIPTION aaasub CONNECTION 'dbname=bbb' PUBLICATION bbbpub WITH (slot_name=bbbslot, create_slot=false);
❺ データベースaaaでテーブルtestに1件レコードを挿入します。
\c aaa
INSERT INTO test VALUES (99);
挿入されたレコードは、ロジカルレプリケーションにより、次はデータベースbbbのテーブルtestに挿入されます。さらに、ロジカルレプリケーションの循環設定により、このレコードは再びデータベースaaaのテーブルtestに挿入されようとします。しかし、テーブルtestにはPRIMARY KEYが設定されているため、以下のとおりPRIMARY KEY制約違反が発生してレコード挿入は失敗します。
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (id)=(99) already exists.
LOG: worker process: logical replication worker for subscription 16405 (PID 64701) exited with exit code 1
❻ PRIMARY KEY制約違反を回避するため、データベースaaaとbbbのテーブルtestからPRIMARY KEY制約を削除します。
\c aaa
ALTER TABLE test DROP CONSTRAINT test_pkey;
\c bbb
ALTER TABLE test DROP CONSTRAINT test_pkey;
注意
PRIMARY KEY制約の削除により、ロジカルレプリケーションは循環し続けて、データベースaaaとbbbのテーブルtestには無限にレコードが挿入され続けます。レコードの無限挿入で、過負荷やディスクフルなどが発生する可能性があることに注意してください。
また、PRIMARY KEY制約の削除により、REPLICA IDENTITYの設定も失われ、UPDATEやDELETEのロジカルレプリケーションができなくなっていることに注意してください。