Ubuntu18.04でPostgreSQL10を使用して論理レプリケーションを設定する方法

投稿日: 2019-11-08 2022-04-20
タグ: Databases, postgresql, Ubuntu 18.04

序章

本番用にアプリケーションをセットアップするときは、データベースの複数のコピーを配置しておくと便利なことがよくあります。データベースコピーの同期を維持するプロセスは、レプリケーションと呼ばれます。レプリケーションは、読み取りの待ち時間を短縮するとともに、大量の同時読み取り操作に高可用性の水平スケーリングを提供できます。また、地理的に分散したデータベースサーバー間でのピアツーピアレプリケーションも可能です。

PostgreSQL は、拡張性が高く、 ACID （Atomicity、Consistency、Isolation、Durability）およびSQL標準に準拠したオープンソースのオブジェクトリレーショナルデータベースシステムです。 PostgreSQLのバージョン10.0では、物理レプリケーションに加えて、論理レプリケーションのサポートが導入されました。論理レプリケーションスキームでは、高レベルの書き込み操作がmasterデータベースサーバーから1つ以上のreplicaデータベースサーバーにストリーミングされます。物理レプリケーションスキームでは、代わりにバイナリ書き込み操作がマスターからレプリカにストリーミングされ、元のコンテンツのバイト単位の正確なコピーが生成されます。オフロードレポート、パッチ適用、アップグレードなど、データの特定のサブセットをターゲットにする場合、論理レプリケーションは速度と柔軟性を提供できます。

このチュートリアルでは、2台のUbuntu18.04サーバーでPostgreSQL10を使用して論理レプリケーションを構成し、一方のサーバーをマスターとして、もう一方のサーバーをレプリカとして機能させます。チュートリアルが終了するまでに、論理レプリケーションを使用してマスターサーバーからレプリカにデータをレプリケートできるようになります。

前提条件

このチュートリアルに従うには、次のものが必要です。

db-masterとdb-replicaという名前の2つのUbuntu18.04サーバーは、それぞれ通常のユーザーアカウントとsudo権限でセットアップされます。これらを設定するには、この初期サーバー設定チュートリアルに従ってください。
サーバーでプライベートネットワークが有効。プライベートネットワークにより、データベースをパブリックインターネットに公開することに伴うセキュリティリスクなしに、サーバー間の通信が可能になります。
Ubuntu18.04にPostgreSQLをインストールして使用する方法のステップ1に従って両方のサーバーにPostgreSQL10をインストールします。

ステップ1—論理レプリケーション用のPostgreSQLの構成

サーバー間の論理レプリケーションを有効にするために変更する必要のある構成設定がいくつかあります。まず、パブリックネットワークを介してデータを公開することはセキュリティ上のリスクがあるため、パブリックネットワークインターフェイスではなくプライベートネットワークインターフェイスでリッスンするようにPostgresを構成します。次に、db-replicaへのレプリケーションを許可するように適切な設定を構成します。

db-master で、メインサーバー構成ファイルである/etc/postgresql/10/main/postgresql.confを開きます。

sudo nano /etc/postgresql/10/main/postgresql.conf

次の行を見つけます。

/etc/postgresql/10/main/postgresql.conf

...
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
...

#を削除してコメントを解除し、db_master_private_ip_addressを追加して、プライベートネットワークでの接続を有効にします。

注：この手順とそれに続く手順では、サーバーのパブリックIPではなく、サーバーの privateIPアドレスを使用してください。データベースサーバーをパブリックインターネットに公開することは、かなりのセキュリティリスクです。

/etc/postgresql/10/main/postgresql.conf

...
listen_addresses = 'localhost, db_master_private_ip_address'
...

これにより、 db-master は、ループバックインターフェイスに加えて、プライベートネットワーク上の着信接続をリッスンします。

次に、次の行を見つけます。

/etc/postgresql/10/main/postgresql.conf

...
#wal_level = replica                    # minimal, replica, or logical
...

コメントを外し、PostgreSQLの先行書き込みログ（WAL）レベルをlogicalに設定するように変更します。これにより、ログ内のエントリの量が増え、特定のデータセットへの不一致や変更を抽出するために必要な情報が追加されます。

/etc/postgresql/10/main/postgresql.conf

...
wal_level = logical
...

このログのエントリはレプリカサーバーによって消費され、マスターからの高レベルの書き込み操作のレプリケーションが可能になります。

ファイルを保存して閉じます。

次に、許可されたホスト、認証、およびデータベースへのアクセスを制御するファイルである/etc/postgresql/10/main/pg_hba.confを編集しましょう。

sudo nano /etc/postgresql/10/main/pg_hba.conf

最後の行の後に、db-replicaからの着信ネットワーク接続を許可する行を追加しましょう。 db-replica のプライベートIPアドレスを使用し、すべてのユーザーとデータベースからの接続を許可するように指定します。

/etc/postgresql/10/main/pg_hba.conf

...
# TYPE      DATABASE        USER            ADDRESS                               METHOD
...
host         all            all             db_replica_private_ip_address/32      md5

パスワードハッシュ（md5）によって認証された、db-replicaからの着信ネットワーク接続が許可されるようになります。

ファイルを保存して閉じます。

次に、db-replicaからdb-masterのポート5432へのトラフィックを許可するようにファイアウォールルールを設定しましょう。

sudo ufw allow from db_replica_private_ip_address to any port 5432

最後に、変更を有効にするためにPostgreSQLサーバーを再起動します。

sudo systemctl restart postgresql

論理レプリケーションを許可するように構成を設定すると、データベース、ユーザーロール、およびテーブルの作成に進むことができます。

ステップ2—データベース、ユーザーロール、およびテーブルを設定する

レプリケーション設定の機能をテストするために、データベース、テーブル、およびユーザーロールを作成しましょう。サンプルテーブルを使用してexampleデータベースを作成します。これを使用して、サーバー間の論理レプリケーションをテストできます。また、専用ユーザーを作成し、データベースとテーブルの両方に対する特権を割り当てます。

まず、db-masterとdb-replicaの両方で次のコマンドを使用して、 psqlpromptをpostgresユーザーとして開きます。

sudo -u postgres psql

sudo -u postgres psql

両方のホストにexampleという名前の新しいデータベースを作成します。

CREATE DATABASE example;

CREATE DATABASE example;

注：これらのコマンドの最後の;が必要です。対話型セッションでは、PostgreSQLはセミコロンで終了するまでSQLコマンドを実行しません。メタコマンド（\qや\cなどのバックスラッシュで始まるコマンド）は、psqlクライアント自体を直接制御するため、このルールから除外されます。メタコマンドとpsqlクライアントの詳細については、PostgreSQLのドキュメントを参照してください。

\connectメタコマンドを使用して、各ホストで作成したデータベースに接続します。

\c example

\c example

両方のホストに任意のフィールドを持つwidgetsという名前の新しいテーブルを作成します。

CREATE TABLE widgets
(
    id SERIAL,
    name TEXT,
    price DECIMAL,
    CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

CREATE TABLE widgets
(
    id SERIAL,
    name TEXT,
    price DECIMAL,
    CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

db-replica のテーブルは、対応するdb-masterと同一である必要はありません。ただし、db-masterのテーブルに存在するすべての列が含まれている必要があります。追加の列には、NOT NULLまたはその他の制約があってはなりません。その場合、レプリケーションは失敗します。

db-master で、REPLICATIONオプションとログインパスワードを使用して新しいユーザーロールを作成しましょう。 REPLICATION属性は、レプリケーションに使用されるすべてのロールに割り当てる必要があります。ユーザーをsammyと呼びますが、これを自分のユーザー名に置き換えることができます。 my_passwordも必ず自分の安全なパスワードに置き換えてください。

CREATE ROLE sammy WITH REPLICATION LOGIN PASSWORD 'my_password';

後でdb-replicaでパスワードを使用してレプリケーションを設定するため、パスワードをメモしておきます。

引き続きdb-masterで、exampleデータベースに対する完全な権限を作成したユーザーロールに付与します。

GRANT ALL PRIVILEGES ON DATABASE example TO sammy;

次に、データベースに含まれるすべてのテーブルに対する特権をユーザーに付与します。

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sammy;

public schema は、テーブルが自動的に配置される各データベースのデフォルトスキーマです。

これらの権限を設定すると、exampleデータベース内のテーブルをレプリケーションに使用できるようになります。

ステップ3—パブリケーションの設定

Publications は、PostgreSQLがテーブルをレプリケーションに使用できるようにするために使用するメカニズムです。データベースサーバーは、特定のパブリケーションに関連付けられているレプリカサーバーの接続およびレプリケーションステータスを内部的に追跡します。 db-master で、サブスクライバーに送信されるデータのマスターコピーとして機能するパブリケーションmy_publicationを作成します。ケース、db-replica。

db-master で、my_publicationというパブリケーションを作成します。

CREATE PUBLICATION my_publication;

以前に作成したwidgetsテーブルを追加します。

ALTER PUBLICATION my_publication ADD TABLE widgets;

パブリケーションを配置したら、そこからデータをプルするサブスクライバーを追加できます。

ステップ4—サブスクリプションの作成

Subscriptions は、PostgreSQLが既存のパブリケーションに接続するために使用します。パブリケーションには、さまざまなレプリカサーバー間で多数のサブスクリプションを含めることができます。また、レプリカサーバーには、サブスクライバーを含む独自のパブリケーションを含めることもできます。 db-master で作成したテーブルのデータにアクセスするには、前の手順で作成したパブリケーションmy_publicationのサブスクリプションを作成する必要があります。

db-replica で、my_subscriptionというサブスクリプションを作成しましょう。 CREATE SUBSCRIPTIONコマンドはサブスクリプションに名前を付け、CONNECTIONパラメーターはパブリッシャーへの接続文字列を定義します。この文字列には、exampleデータベースの名前に加えて、前に定義したユーザー名とパスワードを含む、マスターサーバーの接続の詳細とログイン資格情報が含まれます。繰り返しになりますが、 db-master のプライベートIPアドレスを使用し、my_passwordを自分のパスワードに置き換えることを忘れないでください。

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=sammy dbname=example' PUBLICATION my_publication;

サブスクリプションを確認する次の出力が表示されます。

OutputNOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

サブスクリプションを作成すると、PostgreSQLは既存のデータをマスターからレプリカに自動的に同期します。この場合、widgetsテーブルが空であるため、同期するデータはありませんが、これは、既存のデータベースに新しいサブスクリプションを追加するときに便利な機能です。

サブスクリプションを設定したら、widgetsテーブルにデモデータを追加してセットアップをテストしてみましょう。

ステップ5—テストとトラブルシューティング

マスターとレプリカ間のレプリケーションをテストするために、widgetsテーブルにデータを追加して、正しくレプリケートされることを確認しましょう。

db-master で、widgetsテーブルに次のデータを挿入します。

INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);

db-replica で、次のクエリを実行して、このテーブルのすべてのエントリをフェッチします。

SELECT * FROM widgets;

次のように表示されます。

Output id |    name    | price 
----+------------+-------
  1 | Hammer     |  4.50
  2 | Coffee Mug |  6.20
  3 | Cupholder  |  3.80
(3 rows)

成功！エントリは、db-masterからdb-replicaに正常に複製されました。今後、すべてのINSERT、UPDATE、およびDELETEクエリは、サーバー間で一方向に複製されます。

レプリカサーバーでの書き込みクエリについて注意することの1つは、それらがマスターサーバーに複製されないことです。 PostgreSQLは現在、サーバー間のデータが分岐する場合の競合を解決するためのサポートが制限されています。競合がある場合、レプリケーションは停止し、PostgreSQLはデータベース管理者が問題を手動で修正するまで待機します。そのため、ほとんどのアプリケーションはすべての書き込み操作をマスターサーバーに転送し、使用可能なレプリカサーバー間で読み取りを分散します。

これで、両方のサーバーでpsqlプロンプトを終了できます。

\q

\q

セットアップのテストが終了したので、自分でデータを追加および複製できます。

トラブルシューティング

レプリケーションが機能していないように思われる場合は、最初のステップとして、db-replicaのPostgreSQLログでエラーの可能性を確認することをお勧めします。

tail /var/log/postgresql/postgresql-10-main.log

レプリケーションが機能しなくなる可能性のある一般的な問題は次のとおりです。

プライベートネットワークが両方のサーバーで有効になっていないか、サーバーが異なるネットワーク上にあります。
db-master は、正しいプライベートネットワークIPで接続をリッスンするように構成されていません。
db-master の先行書き込みログレベルが正しく構成されていません（logicalに設定する必要があります）。
db-master は、正しいdb-replicaプライベートIPアドレスからの着信接続を受け入れるように構成されていません。
UFWのようなファイアウォールは、ポート5432で着信PostgreSQL接続をブロックしています。
db-masterとdb-replicaの間に不一致のテーブル名またはフィールドがあります。
sammyデータベースの役割に、db-master上のexampleデータベースにアクセスするために必要な権限がありません。
sammyデータベースの役割には、db-masterのREPLICATIONオプションがありません。
sammyデータベースの役割に、db-masterのwidgetsテーブルにアクセスするために必要な権限がありません。
テーブルはdb-masterのパブリケーションに追加されませんでした。

既存の問題を解決した後、複製が自動的に実行されます。そうでない場合は、次のコマンドを使用して、既存のサブスクリプションを再作成する前に削除します。

DROP SUBSCRIPTION my_subscription;

結論

このチュートリアルでは、PostgreSQL10を2つのUbuntu18.04サーバーに正常にインストールし、それらの間で論理レプリケーションを構成しました。

これで、レプリカサーバーを追加して、水平方向の読み取りスケーリング、高可用性、およびPostgreSQLデータベースの地理的分散を試すために必要な知識が得られました。

PostgreSQL 10での論理レプリケーションの詳細については、PostgreSQLの公式ドキュメントのトピックの章、および CREATEPUBLICATIONとのマニュアルエントリを参照してください。 CREATESUBSCRIPTIONコマンド。

getdocs

13036