前書き

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

PostgreSQLは、非常に拡張性が高く、https://en.wikipedia.org/wiki/ACID_(computer_science)[ACID](原子性、一貫性、分離、耐久性)およびSQL標準。 PostgreSQLのバージョン10.0では、_physical replication_に加えて、_logical replication_のサポートが導入されました。 論理レプリケーションスキームでは、高レベルの書き込み操作が_master_データベースサーバーから1つ以上の_replica_データベースサーバーにストリーミングされます。 物理レプリケーションスキームでは、代わりにバイナリ書き込み操作がマスターからレプリカにストリーミングされ、元のコンテンツのバイト単位の正確なコピーが生成されます。 オフロードレポート、パッチ適用、アップグレードなど、データの特定のサブセットを対象とする場合、論理レプリケーションは速度と柔軟性を提供します。

このチュートリアルでは、2つのUbuntu 18.04サーバーでPostgreSQL 10を使用して論理レプリケーションを構成します。一方のサーバーはマスターとして機能し、他方のサーバーはレプリカとして機能します。 チュートリアルの終わりまでに、論理レプリケーションを使用して、マスターサーバーからレプリカにデータをレプリケートできるようになります。

前提条件

このチュートリアルを実行するには、次のものが必要です。

  • * db-master および db-replica *という名前の2つのUbuntu 18.04サーバーで、それぞれ通常のユーザーアカウントとsudo特権を設定します。 これらをセットアップするには、https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-16-04 [このサーバーの初期セットアップチュートリアル]に従ってください。

  • サーバー上のhttps://www.digitalocean.com/docs/networking/private-networking/quickstart/ [プライベートネットワーキングが有効]。 プライベートネットワーキングを使用すると、データベースをパブリックインターネットに公開することに伴うセキュリティリスクなしにサーバー間の通信が可能になります。

  • https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04の手順1に従って、両方のサーバーにインストールされたPostgreSQL 10 Ubuntu 18.04上のPostgreSQL]。

手順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;
...

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

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

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

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

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

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

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

コメントを外し、PostgreSQL Write Ahead Log(WAL)レベルを `+ logical +`に設定するように変更します。 これにより、ログ内のエントリの量が増え、特定のデータセットの不一致または変更を抽出するために必要な情報が追加されます。

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

...
wal_level =
...

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

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

次に、許可されたホスト、認証、データベースへのアクセスを制御するファイルである「+ / 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             /32      md5

パスワードハッシュhttps://en.wikipedia.org/wiki/MD5[(md5)]で認証された* db-replica *からの着信ネットワーク接続が許可されるようになりました。

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

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

sudo ufw allow from  to any port 5432

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

sudo systemctl restart postgresql

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

手順2-データベース、ユーザーロール、およびテーブルの設定

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

まず、https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04#using-postgresql-roles-and-databases [`+ * db-master db-replica の両方で次のコマンドを使用して、 postgres *ユーザーとしてpsql + `プロンプト]:

sudo -u postgres psql
sudo -u postgres psql

両方のホストで「++」という新しいデータベースを作成します。

CREATE DATABASE ;
CREATE DATABASE ;

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

\c
\c

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

CREATE TABLE
(
   id SERIAL,
   name TEXT,
   price DECIMAL,
   CONSTRAINT widgets_pkey PRIMARY KEY (id)
);
CREATE TABLE
(
   id SERIAL,
   name TEXT,
   price DECIMAL,
   CONSTRAINT widgets_pkey PRIMARY KEY (id)
);
  • db-replica のテーブルは、対応する db-master と同一である必要はありません。 ただし、 db-master *のテーブルに存在するすべての列が含まれている必要があります。 追加の列には、「+ NOT NULL +」などの制約があってはなりません。 その場合、レプリケーションは失敗します。

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

CREATE ROLE  WITH REPLICATION LOGIN PASSWORD '';

後で* db-replica *でパスワードを使用してレプリケーションをセットアップするため、パスワードをメモします。

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

GRANT ALL PRIVILEGES ON DATABASE  TO ;

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ;

https://www.postgresql.org/docs/current/static/ddl-schemas.html [+ public + schema]は、テーブルが自動的に配置される各データベースのデフォルトのスキーマです。

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

ステップ3-パブリケーションのセットアップ

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

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

CREATE PUBLICATION ;

前に作成した `++`テーブルを追加します:

ALTER PUBLICATION  ADD TABLE ;

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

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

_Subscriptions_は、PostgreSQLが既存のパブリケーションに接続するために使用されます。 パブリケーションは、異なるレプリカサーバー間で多くのサブスクリプションを持つことができ、レプリカサーバーはサブスクライバーとの独自のパブリケーションを持つこともできます。 * db-master *で作成したテーブルのデータにアクセスするには、前の手順「++」で作成したパブリケーションへのサブスクリプションを作成する必要があります。

  • db-replica で、 “というサブスクリプションを作成しましょう。 `+ CREATE SUBSCRIPTION +`コマンドはサブスクリプションに名前を付け、 `+ CONNECTION +`パラメーターはパブリッシャーへの接続文字列を定義します。 この文字列には、先に定義したユーザー名とパスワード、および「」データベースの名前を含む、マスターサーバーの接続詳細とログイン認証情報が含まれます。 もう一度、 db-master *のプライベートIPアドレスを使用し、 `++`を自分のパスワードに置き換えてください。

CREATE SUBSCRIPTION  CONNECTION 'host= port=5432 password= user= dbname=' PUBLICATION ;

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

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

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

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

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

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

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

INSERT INTO  (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);
  • db-replica *で、次のクエリを実行して、このテーブルのすべてのエントリを取得します。

SELECT * FROM ;

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

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 *の間でテーブル名またはフィールドが一致していません。

  • “データベースロールには、* db-master *の “データベースにアクセスするために必要な権限がありません。

  • `+`データベースロールには、* db-master *の ` REPLICATION +`オプションがありません。

  • “データベースロールには、* db-master *の “テーブルにアクセスするために必要な権限がありません。

  • このテーブルは、* db-master *のパブリケーションに追加されていません。

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

DROP SUBSCRIPTION ;

結論

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

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

PostgreSQL 10の論理レプリケーションの詳細については、公式のPostgreSQLドキュメントのhttps://www.postgresql.org/docs/10/static/logical-replication.html [トピックの章]を読むことができます。 +CREATE PUBLICATION + `およびhttps://www.postgresql.org/docs/10/static/のマニュアルエントリsql-createsubscription.html [+ CREATE SUBSCRIPTION +`]コマンド。