序章

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

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

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

前提条件

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

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

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

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

db-master で、 /etc/postgresql/10/main/postgresql.conf、メインサーバー構成ファイル:

  1. 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、許可されたホスト、認証、およびデータベースへのアクセスを制御するファイル:

  1. 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

これで、 db-replica からの着信ネットワーク接続が許可され、パスワードハッシュ(md5)によって認証されます。

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

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

  1. sudo ufw allow from db_replica_private_ip_address to any port 5432

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

  1. sudo systemctl restart postgresql

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

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

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

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

  1. sudo -u postgres psql
  1. sudo -u postgres psql

と呼ばれる新しいデータベースを作成します example 両方のホストで:

  1. CREATE DATABASE example;
  1. CREATE DATABASE example;

注:ファイナル ; これらのコマンドでは必須です。 対話型セッションでは、PostgreSQLはセミコロンで終了するまでSQLコマンドを実行しません。 メタコマンド(バックスラッシュで始まるコマンド、 \q\c)psqlクライアント自体を直接制御するため、このルールは免除されます。 メタコマンドとpsqlクライアントの詳細については、PostgreSQLのドキュメントを参照してください。

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

  1. \c example
  1. \c example

と呼ばれる新しいテーブルを作成します widgets 両方のホストに任意のフィールドがある場合:

  1. CREATE TABLE widgets
  2. (
  3. id SERIAL,
  4. name TEXT,
  5. price DECIMAL,
  6. CONSTRAINT widgets_pkey PRIMARY KEY (id)
  7. );
  1. CREATE TABLE widgets
  2. (
  3. id SERIAL,
  4. name TEXT,
  5. price DECIMAL,
  6. CONSTRAINT widgets_pkey PRIMARY KEY (id)
  7. );

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

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

  1. CREATE ROLE sammy WITH REPLICATION LOGIN PASSWORD 'my_password';

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

引き続きdb-masterで、 example 作成したユーザーロールへのデータベース:

  1. GRANT ALL PRIVILEGES ON DATABASE example TO sammy;

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

  1. 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:

  1. CREATE PUBLICATION my_publication;

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

  1. ALTER PUBLICATION my_publication ADD TABLE widgets;

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

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

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

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

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

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

Output
NOTICE: created replication slot "my_subscription" on publisher CREATE SUBSCRIPTION

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

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

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

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

db-master で、次のデータを widgets テーブル:

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

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

  1. 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 両方のサーバーでプロンプトを表示します。

  1. \q
  1. \q

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

トラブルシューティング

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

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

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

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

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

  1. DROP SUBSCRIPTION my_subscription;

結論

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

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

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