PostgreSQL 13 でロジカルレプリケーションを設定する手順

　

★悩み★
・PostgreSQLのロジカルレプリケーションとは何か？仕組みも知りたい。
・PostgreSQLのロジカルレプリケーションの仕組みを知りたい。
・PostgreSQL 13 でロジカルレプリケーションを設定する手順は？

こういった「悩み」に答えます。

　

★本記事の内容★
① PostgreSQLのロジカルレプリケーションの仕組みを解説
② PostgreSQL 13 のロジカルレプリケーションを設定する為の準備
③ PostgreSQL 13 のロジカルレプリケーションを設定する手順を解説

これからご紹介する「PostgreSQL 13 のロジカルレプリケーションを設定する手順」を実践したことで、筆者は1時間以内でPostgreSQL 13 でロジカルレプリケーションを設定できました。

記事の前半では「PostgreSQLのロジカルレプリケーションの仕組み」を解説します。記事の後半では「PostgreSQL 13 でロジカルレプリケーションを設定する手順」を紹介します。

この記事を読み終えることで、「PostgreSQLのロジカルレプリケーションの仕組み」を把握できるだけでなく、「PostgreSQL 13 でロジカルレプリケーションを設定できる」状態になります。
　

ちなみに、「PostgreSQLのストリーミングレプリケーションの仕組み」を知りたい方は、以下の記事をご覧ください。
　

PostgreSQL 13 のストリーミングレプリケーションを構築する手順

「PostgreSQLのストリーミングレプリケーションの構築方法は？」で悩んでいませんか。本記事では、PostgreSQL 13 のストリーミングレプリケーションを構築する手順をご紹介します。また「ストリーミングレプリケーションって何？」も知りたい方は、ぜひ記事をご覧ください。

kakedashi-xx.com

2023.01.29

　

　

　

PostgreSQLのロジカルレプリケーションの仕組みを解説

「PostgreSQLのロジカルレプリケーションの仕組みを解説」に関してご紹介します。

★PostgreSQLのロジカルレプリケーションに関するご紹介事項★
・PostgreSQLのロジカルレプリケーションとは何か？をご紹介
・PostgreSQLのロジカルレプリケーションの仕組みをご紹介

以降で、上記「PostgreSQLのロジカルレプリケーションに関するご紹介事項」の各項目に関してご説明します。　
　

PostgreSQLのロジカルレプリケーションとは何か？

「PostgreSQLのロジカルレプリケーションとは何か？」に関してご紹介します。
　

PostgreSQLにおける「レプリケーション」とは、「データベースの複製(レプリカ)を作成すること」です。

PostgreSQLには、「レプリケーションを作成する」機能の1つとして、「ロジカルレプリケーション(別名：論理レプリケーション)」があります。PostgreSQLの「ロジカルレプリケーション」を使うことで、指定したテーブルをデータベース間で簡単に複製できます。(下図参照)
　

PostgreSQLの「ロジカルレプリケーション」の利用例は、以下となります。
　

★ロジカルレプリケーションの利用例★
・メジャーバージョンが異なるPostgreSQL間でのレプリケーションをしたい
・異なるOSのPostgreSQL間でのレプリケーションをしたい
・特定のテーブルのみをPostgreSQL間でレプリケーションをしたい

参照：ロジカルレプリケーションの利用用途(公式サイト)

以上が、「PostgreSQLのロジカルレプリケーションとは何か？」となります。
　

PostgreSQLのロジカルレプリケーションの仕組み

「PostgreSQLのロジカルレプリケーションの仕組み」に関して解説します。
　

PostgreSQLが「どのようにして、データを複製しているか」を説明しますね。

PostgreSQLのロジカルレプリケーションによるデータ複製は、WAL(Write Ahead Log)を「pgoutputプロセス」でデコードし、デーコードしたWALを「wal senderプロセス」と「bdworkerプロセス」で転送することで実現しています。(下図参照)
　

ちなみに、PostgreSQLのロジカルレプリケーションには、以下の制限事項があります。
　

★PostgreSQLのロジカルレプリケーションの制限事項★
・レプリケーションされないデータ操作がある
　→ TRUNCATEに関するデータ操作
・レプリケーションされないデータがある
　→ シーケンスのデータやビューなど

参照：ロジカルレプリケーションの制限事項(公式サイト)

上記制限事項に対するレプリケーションや「負荷分散や高可用性」を実現したい場合は、「ロジカルレプリケーション(論理レプリケーション)」ではなく、「ストリーミングレプリケーション」の利用を検討してください。
　

以上で、「PostgreSQLのロジカルレプリケーションの仕組み」は完了です。
　

「一部のテーブルをレプリケーションしたい」や「異なるPostgreSQLのバージョン間でデータを複製したい」場合、PostgreSQLのロジカルレプリケーションの利用を検討しましょう。

　

PostgreSQL 13 のロジカルレプリケーションを設定する為の準備

「PostgreSQL 13 のロジカルレプリケーションを設定する為の準備」に関してご紹介します。

★PostgreSQL 13 のロジカルレプリケーションを設定する為の準備★
手順1：PostgreSQL 13 をインストール
手順2：PostgreSQL 13 の動作確認

上記の流れで、「PostgreSQL 13 のロジカルレプリケーションを設定する為の準備」ができます。また、ロジカルレプリケーションを設定するために、以下のPostgreSQL環境を構築します。(下図参照)
　

　

上記の各手順は、以下の日時と環境で動作確認済みです。
動作確認済み日時：2021年9月17日
動作確認済み環境：CentOS Linux release 7.7.1908 (Core)

以降で、上記「PostgreSQL 13 のロジカルレプリケーションを設定する為の準備」の各手順に関してご説明します。
　

手順1：PostgreSQL 13 をインストール

「手順1：PostgreSQL 13 をインストール」に関してご説明します。
　

ロジカルレプリケーション環境を設定するためにも、PostgreSQLをインストールしましょう。

「PostgreSQLのインストール手順」に関しては、以下の記事「5ステップでPostgreSQL13をインストールする方法」をご覧ください。
　

【コピペOK】5ステップでPostgreSQL13をインストールする方法

「PostgreSQL 13をインストールする方法」で悩んでいませんか。本記事では、yumでPostgreSQL 13をインストールする方法を解説しつつ、データの登録と参照する方法もご紹介します。また「Amazon Linux 2にPostgreSQL 13をインストールするには？」も知りたい方はぜひ記事をご覧ください。

kakedashi-xx.com

2023.01.29

　

既にPostgreSQLをインストールされている方は、本手順を省略してください。

以上で、「手順1：PostgreSQL 13 をインストール」は完了です。
　

手順2：PostgreSQL 13 の動作確認

「手順2：PostgreSQL 13 の動作確認」に関してご説明します。
　

PostgreSQLが正常に動作するかを確認しましょう。

「PostgreSQLが起動できるか」を確認し、PostgreSQLが正常に動作することを確認してください。起動手順に関しては、以下の記事をご覧ください。
　

【コピペOK】5ステップでPostgreSQL13をインストールする方法

「PostgreSQL 13をインストールする方法」で悩んでいませんか。本記事では、yumでPostgreSQL 13をインストールする方法を解説しつつ、データの登録と参照する方法もご紹介します。また「Amazon Linux 2にPostgreSQL 13をインストールするには？」も知りたい方はぜひ記事をご覧ください。

kakedashi-xx.com

2023.01.29

以上で、「手順2：PostgreSQL 13 の動作確認」は完了です。
　

上記の流れで、PostgreSQL 13 のロジカルレプリケーションを設定する為の準備ができました。

　

PostgreSQL 13 のロジカルレプリケーションを設定する手順

「PostgreSQL 13 のロジカルレプリケーションを設定する手順」に関してご紹介します。

★PostgreSQL 13 のロジカルレプリケーションを設定する流れ★
手順1：複製元のPostgreSQLの設定ファイルを編集
手順2：複製元のPostgreSQLでロジカルレプリケーションの対象を定義
手順3：複製先のPostgreSQLで論理レプリケーション対象のスキーマを定義
手順4：複製先のPostgreSQLでロジカルレプリケーションの利用を宣言
手順5：ロジカルレプリケーションが設定されたことを確認
手順6：複製元のPostgreSQLにデータを登録
手順7：複製先のPostgreSQLにデータが登録されたことを確認

上記の流れで、「PostgreSQL 13 のロジカルレプリケーションを設定」できます。また、上記手順で以下の「PostgreSQLのロジカルレプリケーション」環境を構築できます。(下図参照)
　

　

上記の各手順は、以下の日時と環境で動作確認済みです。
動作確認済み日時：2021年9月17日
動作確認済み環境：CentOS Linux release 7.7.1908 (Core)

以降で、上記「PostgreSQL 13 のロジカルレプリケーションを設定する流れ」の各手順に関してご説明します。
　

手順1：複製元のPostgreSQLの設定ファイルを編集

「手順1：複製元のPostgreSQLの設定ファイルを編集」に関して解説します。
　

ロジカルレプリケーションを設定するために、複製元のPostgreSQLを設定しましょう。

PostgreSQLの設定ファイルの1つである「postgresql.conf」を編集しましょう。見直すべき設定値は、以下となります。

↓↓　wal_level(WALの出力レベル)の設定値をlogicalに設定　↓↓
wal_level = logical			# minimal, replica, or logical

↓↓　max_wal_senders(wal senderに同時接続する最大数)の設定値を変更(今回は、10を指定)　↓↓
max_wal_senders = 10		# max number of walsender processes

設定を反映させるために、以下のコマンドでPostgreSQLを再起動してください。

[root@data-manager data]# systemctl restart postgresql-13.service 

最後に、「mymovie」テーブルを作成しておきましょう。作成手順に関しては、以下の記事「PostgreSQL 13でデータ登録と参照をする方法」をご覧ください。
　

【コピペOK】5ステップでPostgreSQL13をインストールする方法

「PostgreSQL 13をインストールする方法」で悩んでいませんか。本記事では、yumでPostgreSQL 13をインストールする方法を解説しつつ、データの登録と参照する方法もご紹介します。また「Amazon Linux 2にPostgreSQL 13をインストールするには？」も知りたい方はぜひ記事をご覧ください。

kakedashi-xx.com

2023.01.29

以上で、「手順1：複製元のPostgreSQLの設定ファイルを編集」は完了です。
　

手順2：複製元のPostgreSQLでロジカルレプリケーションの対象を定義

「手順2：複製元のPostgreSQLでロジカルレプリケーションの対象を定義」に関して解説します。
　

ロジカルレプリケーションで複製したい対象を定義しましょう。

複製元のPostgreSQLにおいて、ロジカルレプリケーションの対象を定義します。定義するために、以下のコマンドを順に実行してください。

[root@data-manager data]# su - postgres
-bash-4.2$ psql
postgres=# \c testdb
testdb=# CREATE PUBLICATION pub_a FOR TABLE mymovie;
CREATE PUBLICATION
testdb=# 

コマンド実行後、「CREATE PUBLICATION」が表示された場合、「正常に複製元のPostgreSQLでロジカルレプリケーションの対象を定義できた」と判断できます。
　

「CREATE PUBLICATION」のコマンドリファレンスに関しては、以下をご覧ください。
参照：CREATE PUBLICATIONのコマンドリファレンス(公式サイト)
　

以上で、「手順2：複製元のPostgreSQLでロジカルレプリケーションの対象を定義」は完了です。
　

手順3：複製先のPostgreSQLで論理レプリケーション対象のスキーマを定義

「手順3：複製先のPostgreSQLで論理レプリケーション対象のスキーマを定義」に関して解説します。
　

ロジカルレプリケーション(論理レプリケーション)を利用する場合、複製先にロジカルレプリケーションの対象となったスキーマの定義が必要です。

複製先にスキーマを作成するために、pg_dumpコマンドで複製元からスキーマ情報を取得しましょう。そのために、以下のコマンドを順に実行してください。コマンド内のIPアドレスの情報などは、自分用に書き換えてください。

[root@develop postgresql]# su - postgres
-bash-4.2$ /usr/pgsql-13/bin/pg_dump -h 192.168.10.161 -p 5432 --username=postgres --schema-only --table mymovie testdb > mymovie_schema.sql

コマンド実行後、「mymovie_schema.sql」が存在する場合、「正常にスキーマ情報を取得できた」と判断できます。
　

ちなみに、「pg_dump」に関する詳細を知りたい方は、以下の記事をご覧ください。
　

PostgreSQLのバックアップコマンドのまとめ【pg_dumpの紹介】

「PostgreSQLをバックアップしたいけど、どのコマンドを使えば良い？」で悩んでいませんか。本記事では、「PostgreSQLのバックアップの種類」から「各バックアップで利用するコマンドやツール」をご紹介します。また、「pg_dumpコマンドでPostgreSQLをバックアップする手順」も解説します。

kakedashi-xx.com

2023.02.02

取得したスキーマ情報を複製先のPostgreSQLにインポートするために、以下のコマンドを順に実行してください。

[root@develop postgresql]# systemctl start postgresql-13.service
[root@develop postgresql]# su - postgres
-bash-4.2$ psql
postgres=# create database testdb;
CREATE DATABASE
postgres=# \q
-bash-4.2$ psql -U postgres -d testdb < mymovie_schema.sql
-bash-4.2$ echo $?
0

コマンド実行後、「0」が表示された場合、「正常に複製先のPostgreSQLにスキーマをインポートできた」と判断できます。
　

以上で、「手順3：複製先のPostgreSQLで論理レプリケーション対象のスキーマを定義」は完了です。
　

手順4：複製先のPostgreSQLでロジカルレプリケーションの利用を宣言

「手順4：複製先のPostgreSQLでロジカルレプリケーションの利用を宣言」に関して解説します。
　

複製先のPostgreSQLでロジカルレプリケーションの利用を宣言しましょう。

複製先のPostgreSQLでロジカルレプリケーションの利用を宣言するために、以下のコマンドを実行してください。コマンド内のホスト名の情報などは、自分用に書き換えてください。

[root@develop postgresql]# su - postgres
-bash-4.2$ psql
postgres=# \c testdb
testdb=# CREATE SUBSCRIPTION sub_a CONNECTION 'dbname=testdb host=data-manager' PUBLICATION pub_a;
NOTICE:  発行サーバでレプリケーションスロット"sub_a"を作成しました
CREATE SUBSCRIPTION
testdb=# 

コマンド実行後、「CREATE SUBSCRIPTION」が表示された場合、「正常に複製先のPostgreSQLでロジカルレプリケーションの利用を宣言できた」と判断できます。
　

「CREATE SUBSCRIPTION」の詳細に関しては、以下をご覧ください。
参照：CREATE SUBSCRIPTIONのコマンドリファレンス(公式サイト)
　

以上で、「手順4：複製先のPostgreSQLでロジカルレプリケーションの利用を宣言」は完了です。
　

手順5：ロジカルレプリケーションが設定されたことを確認

「手順5：ロジカルレプリケーションが設定されたことを確認」に関して解説します。
　

ロジカルレプリケーションの設定ができたことを確認しましょう。

ロジカルレプリケーションの設定ができたことを確認するために、複製元と複製先のPostgreSQLのログを確認してください。

↓↓　複製元のPostgreSQLのログ確認　↓↓
-bash-4.2$ tail -n 50 13/data/log/postgresql-Fri.log | grep "START_REPLICATION SLOT"
2021-09-10 13:06:25.884 JST [25005] 文:  START_REPLICATION SLOT "sub_a" LOGICAL 0/0 (proto_version '1', publication_names '"pub_a"')


↓↓　複製先のPostgreSQLのログ確認　↓↓
-bash-4.2$ tail -n 50 13/data/log/postgresql-Fri.log | grep "同期ワーカが終了しました"
2021-09-10 13:06:26.000 JST [13319] LOG:  サブスクリプション"sub_a"、テーブル"mymovie"に対する論理レプリケーションテーブル同期ワーカが終了しました

上記のログが出力されていた場合、「ロジカルレプリケーションを正しく設定できた」と判断できます。
　

以上で、「手順5：ロジカルレプリケーションが設定されたことを確認」は完了です。
　

手順6：複製元のPostgreSQLにデータを登録

「手順6：複製元のPostgreSQLにデータを登録」に関してご説明します。
　

ロジカルレプリケーションの動作を確認するために、複製元のPostgreSQLにデータを登録してみましょう。

複製元のPostgreSQLに対して、データを登録するために以下のコマンドを順に実行してください。

[root@data-manager data]# su - postgres
-bash-4.2$ psql
postgres=# \c testdb;
データベース"testdb"にユーザ"postgres"として接続しました。
testdb=# \t
「タプルのみ表示」は on です。
testdb=# \d
 public   | mymovie     | テーブル | postgres
 public   | seo_ranking | テーブル | postgres

testdb=# insert into mymovie values(2,'a');
INSERT 0 1
testdb=# insert into mymovie values(2,'c');
INSERT 0 1
testdb=# \q

コマンド実行後、「INSERT 0 1」が表示された場合、「正常に複製元のPostgreSQLにデータを登録できた」と判断できます。
　

以上で、「手順6：複製元のPostgreSQLにデータを登録」は完了です。
　

手順7：複製先のPostgreSQLにデータが登録されたことを確認

「手順7：複製先のPostgreSQLにデータが登録されたことを確認」に関してご説明します。
　

複製先のPostgreSQLにロジカルレプリケーション経由でデータが登録されたことを確認しましょう。

ロジカルレプリケーション経由で「データが複製先のPostgreSQLに登録された」ことを確認するために、以下のコマンドを実行してください。

[root@develop data]# su - postgres
-bash-4.2$ psql
postgres=# \c testdb
データベース "testdb" にユーザ"postgres"として接続しました。
testdb=# \d
              リレーションの一覧
 スキーマ |    名前     |    型    |  所有者  
----------+-------------+----------+----------
 public   | mymovie     | テーブル | postgres
 public   | seo_ranking | テーブル | postgres
(2 行)

testdb=# select * from mymovie;
 id | name 
----+------
  1 | a
  2 | b
  2 | b
  2 | b
  2 | b
  2 | b
  2 | b
  2 | b
  2 | c
  2 | c
  2 | a
  2 | c
(12 行)

上記コマンド実行後、「複製元で登録したデータが表示された」場合、「正常にロジカルレプリケーション経由でデータが複製された」と判断できます。
　

上記の流れで、PostgreSQL 13 のロジカルレプリケーションを設定できました。

ちなみに、「PostgreSQLのアーキテクチャを体系的に学びたい」や「その他のPostgreSQLのコマンドを知りたい」方は、以下の参考書がオススメです。

　

【まとめ】PostgreSQL 13 でロジカルレプリケーションを設定する手順

今回の記事を通して、「PostgreSQL 13 のストリーミングレプリケーションを構築する手順」をご紹介することで、以下の悩みを解消しました。
　

★悩み★
・PostgreSQLのロジカルレプリケーションとは何か？仕組みも知りたい。
・PostgreSQLのロジカルレプリケーションの仕組みを知りたい。
・PostgreSQL 13 でロジカルレプリケーションを設定する手順は？

「PostgreSQL 13 のロジカルレプリケーションを設定する手順は？」や「そもそもロジカルレプリケーションとは？」で悩んでいるあなたにこの記事が少しでも役に立てれば幸いです。