PostgreSQLの冗長構成の構築(同期ストリーミングレプリケーション)

PostgreSQLの標準機能であるReplication機能を使って冗長構成(マスタ:スタンバイ＝1:1)を構築してみました。これまでAWSのコンソールからポチポチして冗長構成を組んだ経験しかないので、今回は手動で構築していきます。自動フェイルオーバーは含みません。

レプリケーションの方法

PostgreSQLの標準機能のレプリケーションの方法はログシッピングとストリーミングレプリケーションの2種類があります。標準のレプリケーション機能では、自動でフェイルオーバーをする機能はないため、自動でフェイルオーバーを行いたい場合は、HAクラスタを構築するミドルウェアを導入する必要があります。

ログシッピング

ログシッピングはWAL(Write ahead log)という更新に関するログファイルをスタンバイに転送し、WALを基にスタンバイのデータを更新することでマスタとスタンバイで同期を行います。

ストリーミングレプリケーション

ログシッピングはWALをファイル単位でスタンバイに転送するのに対して、ストリーミングレプリケーションはWALをレコード単位で転送します。そのため、ログシッピングより同期の遅延が少ないです。

その他

もともとPostgreSQLは冗長構成を構築する機能を標準で実装しないという方針をとっていました。そのため、PosrgreSQL 9.0以前で冗長構成を構築するためには、HA(High Avalilable)クラスタを構築するミドルウェアが必要で、多くのミドルウェアが開発されています。しかし9.0からReplication機能が入りました。
PosgreSQL Wikiで紹介されているので、興味のある人は見てください。Pgpool-IIが一番有名なんじゃないかと思います。

環境

Ubuntsu 14.04
PostgreSQL 9.6

手順

1. VMの準備
2. マスタ(db1)の設定
3. スタンバイ(db2)の設定

VMの準備

Vagrantを使ってVMを用意します。ひとつのVagrantfileで複数のVMを起動できるので便利です。
Vagrantfile

PostgreSQL 9.6のインストール

db1とdb2の両方にPostgreSQLをインストールします。

$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install postgresql-9.6

マスタ(db1)の設定

db1に入りpostgresユーザに切り替えます。

$ vagrant ssh db1
$ sudo su - postgres

レプリケーション用のユーザrepl_userを作成します。ユーザ名、パスワードは任意です。

$ psql
psql (9.6.3)
Type "help" for help.

postgres=# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'password';

どのユーザがどのIPから接続できるの設定が書かれているpg_hba.confを修正します。念のため、デフォルトの設定は別ファイルにバックアップをとります。

$ cp /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/9.6/main/pg_hba.conf.org
$ vim /etc/postgresql/9.6/main/pg_hba.conf

以下を追加してください。

host replication repl_user 192.168.50.2/32 md5


次にpostgresql.confを修正します。こちらも念のためバックアップをとっておきます。

$ cp /etc/postgresql/9.6/main/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf.org
$ vim /etc/postgresql/9.6/main/postgresql.conf

以下を追加してください。

listen_addresses = '*'
wal_level = replica
synchronous_commit = on
max_wal_senders = 2
synchronous_standby_names = 'db2'

listen_addresses = '*'はなぜ必要なのかわからなかったのですが、スタンバイからのベースバックアップの取得ができなかったので追加しました。

9.6からwal_leveの設定が変わりました。archive、hot_standbyがなくなりreplicaに変更されました。使い分けは以下の通りです。

wal_level	説明
minimal	クラッシュまたは即時停止から回復するのに必要な情報のみ書き出します。
replica	WALアーカイビングに必要なログ出力、およびスタンバイサーバ上で読み取り専用問い合わせを実行するために必要な情報を追加します。
logical	更にロジカルデコーディングをサポートするのに必要な情報を追加します。

synchronous_commitは同期レプリケーションにするので、onにします。各設定は、同期：on、メモリ同期：remote_write、スレーブ非同期：local、完全非同期：off。詳しくは、こちらで紹介されています。

max_wal_sendersはスレーブからの同時接続数の上限を設定します。通常、スタンバイの数+1を設定します。

同期レプリケーションにする場合synchronous_standby_namesの設定が必要です。この設定がなければ、スレーブ非同期レプリケーションで動作します。9.6より前は同期レプリケーションできるスタンバイは1つでしたが、9.6から複数のスタンバイで同期レプリケーションができるようになりました。

マスタの設定が終わったので、vagrantユーザに戻って、PostgreSQLを再起動します。

$ sudo service postgresql restart

スタンバイ(db2)の設定

PostgreSQLが動いている場合、一旦止めます。

$ sudo service postgresql stop

PGDATAを空にしてマスタからベースバックアップを取得します。

$ cd /var/lib/postgresql/9.6/main
$ rm -rf *
$ pg_basebackup -h 192.168.50.1 -p 5432 -U repl_user -D ~/9.6/main --xlog --checkpoint=fast --progress

postgresql.confをバックアップをとったあとに修正します。

$ sudo su - postgres
$ cp /etc/postgresql/9.6/main/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf.org
$ vim /etc/postgresql/9.6/main/postgresql.conf

ホットスタンバイ(参照可能)にするため、以下を追加します。

hot_standby = on


最後にrecovery.confを作成します。

$ vim /etc/postgresql/9.6/main/recovery.conf

スタンバイモードしマスタと連携させるため、以下を追加します。

standby_mode = on
primary_conninfo = 'host=192.168.50.1 port=5432 user=repl_user password=password application_name=db2'

vagrantユーザに戻り、PostgreSQLを再起動する。

$ sudo service postgresql start

起動確認

マスタ(db1)でstateがstreamingになっていればOKです。

$ psql -c "SELECT * FROM pg_stat_replication;"

データを入れて確認してみましょう。マスタ(db1)でデータを入れます。

$ psql postgres -c "CREATE TABLE test_table(i int);"
CREATE TABLE
$ psql postgres -c "INSERT INTO test_table VALUES (1);"
INSERT 0 1

スタンバイ(db2)でデータを確認します。値が入っていれば、レプリケーションできています。

$ postgres@db2:~$ psql postgres -c "SELECT * FROM test_table;"
 i
---
 1
(1 row)

参考

• How to install PostgreSQL 9.6 on any Ubuntu version
• Postgresqlレプリケーション ストリーミングレプリケーション仕組み
• 19.5. ログ先行書き込み（WAL）

設定ファイル

今回設定したconfのファイルは全てGithubに上げています。
https://github.com/kouyaf77/postgresql-replication-sample