PostgreSQL9.3で同期レプリケーションしてみる

これの続き

blog.kasei-san.com

やったこと

  1. EC2環境を2台構築
  2. マスタの環境構築
  3. スタンバイの環境構築
  4. マスタでレプリケーションステータスを確認
  5. マスタが更新されるとスタンバイが更新されることを確認

EC2環境を2台構築

  • web UI から Amazon linux の t2.small を作成
  • セキュリティグループで、SSHと、PostgreSQLを許可

環境のIP

  • マスタ: 172.31.59.13
  • スタンバイ: 172.31.57.96

マスタの環境構築

概要

  1. PostgreSQLのインストール
  2. DB、テーブルを作成して、動作確認用のデータを挿入
  3. 他のサーバからアクセスするため、postgres ユーザにパスワードを設定
  4. 同期レプリケーションのための設定を postgresql.conf に設定
  5. WALアーカイブディレクトリを作成
  6. 他サーバから postgres ユーザでアクセスするために、pg_hba.conf を設定
  7. PostgreSQLサーバ再起動

PostgreSQLのインストール

$ wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo rpm -ivh pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo yum -y install postgresql93-server.x86_64
$ sudo chkconfig postgresql-9.3 on
$ sudo service postgresql-9.3 initdb -E UTF-8 --no-locale
$ sudo service postgresql-9.3 start

DB、テーブルを作成して、動作確認用のデータを挿入

$ sudo su - postgres
$ createdb mydb
$ psql mydb
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

他のサーバからアクセスするため、postgres ユーザにパスワードを設定

$ sudo su - postgres
$ psql
alter role postgres with password 'password';

本当はもっと強固なパスワードにすべき

同期レプリケーションのための設定を postgresql.conf に設定

デフォルトの設定との差分は以下の通り

$ diff /var/lib/pgsql/9.3/data/postgresql.conf.bak /var/lib/pgsql/9.3/data/postgresql.conf
59c59
< #listen_addresses = 'localhost'               # what IP address(es) to listen on;
---
> listen_addresses = '*'                # what IP address(es) to listen on;
159c159
< #wal_level = minimal                  # minimal, archive, or hot_standby
---
> wal_level = hot_standby                 # minimal, archive, or hot_standby
188c188
< #archive_mode = off           # allows archiving to be done
---
> archive_mode = on             # allows archiving to be done
190c190,191
< #archive_command = ''         # command to use to archive a logfile segment
---
> archive_command = 'test ! -f /usr/local/pgsql/backup/%f && cp %p /usr/local/pgsql/backup/%f'
>                               # command to use to archive a logfile segment
206c207
< #max_wal_senders = 0          # max number of walsender processes
---
> max_wal_senders = 3           # max number of walsender processes
215c216
< #synchronous_standby_names = ''       # standby servers that provide sync rep
---
> synchronous_standby_names = 'slave'   # standby servers that provide sync rep

それぞれの設定値の説明

  • listen_addresses : 外部からのアクセスを許可する設定(* ですべてのIPからのアクセスを許可)
  • wal_level : WALの保存レベルの設定
  • archive_mode : on にすると、WALアーカイブプロセスが起動するようになる
  • archive_command : WALアーカイブ時に実行されるコマンド
  • max_wal_senders : WALを外部に送信するプロセスの最大数
  • synchronous_standby_names : 同期スタンバイの名前(あとでスタンバイ側でも定義する)

WALアーカイブディレクトリを作成

$ sudo mkdir -p /usr/local/pgsql/backup/
$ sudo chown postgres:postgres /usr/local/pgsql/backup

他サーバから postgres ユーザでアクセスするために、pg_hba.conf を設定

  • 接続方法/ユーザ/DB毎の認証方法を定義するファイル
  • HBAとは、host-based authentication: ホストベース認証の略
  • フォーマットは、 接続方法\t接続するDB\tuser\tIPアドレス\t認証方法

詳しくは公式ドキュメント → pg_hba.confファイル

デフォルトの設定との差分は以下の通り

$ diff /var/lib/pgsql/9.3/data/pg_hba.conf /var/lib/pgsql/9.3/data/pg_hba.conf.bak
90,91d89
< host    replication     postgres        172.16.0.0/12            md5
< host    all     postgres        172.16.0.0/12            md5

レプリケーション用の設定(1行目)と、普通にDBにアクセスする設定(2行目)も追加

  • 接続するDBreplication はレプリケーション用の特別な設定
  • 認証方法 md5 はパスワード認証

PostgreSQLサーバ再起動

$ sudo service postgresql-9.3 restart

スタンバイ環境構築

概要

  1. PostgreSQLのインストール
  2. マスタのベースバックアップを取得
  3. recovery.conf にスタンバイサーバの設定を追記
  4. postgresql.conf にホットスタンバイの設定を追記
  5. PostgreSQLサーバを起動

PostgreSQLのインストール

$ wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo rpm -ivh pgdg-ami201503-93-9.3-3.noarch.rpm
$ sudo yum -y install postgresql93-server.x86_64
$ sudo chkconfig postgresql-9.3 on

マスタのベースバックアップを取得

直接 $PG_DATAを上書き

$ sudo su - postgres
$ rm -rf $PGDATA
$ pg_basebackup -h 172.31.59.13 -D $PGDATA -x -P -U postgres -R
  • パスワードを聞かれるので、先程設定したパスワードを入力する
  • -R オプションで、後述の recovery.conf を自動生成してくれる

recovery.conf にスタンバイサーバの設定を追記

スタンバイサーバの設定は、 recovery.conf に書く

なんで recovery.conf に書くかというと、どうやら、PostgreSQLのレプリケーションとは、マスタデータから来たデータを元にDBをリカバリし続けるという方法のため、リカバリの設定を書く所に書くようになったっぽい

$ sudo su - postgres
$ vim /var/lib/pgsql/9.3/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres host=172.31.59.13 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=slave'
  • primary_conninfo はDBに接続する時の接続文字列を定義
    • pg_basebackup で自動生成された内容に加えて、同期レプリケーションのため、master の postgresql.conf で定義した application_name=slave を追記
  • primary_conninfo について、詳しくは → データベース接続制御関数#接続文字列

postgresql.conf にホットスタンバイの設定を追記

hot_standby = on だけ追記

  • これにより、スタンバイでも読み込みだけ可能になる

PostgreSQLサーバを起動

$ sudo service postgresql-9.3 start

レプリケーションステータスを確認

マスタ側で実行

$ sudo su - postgres
$ psql
postgres-# \x
Expanded display is on.

postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 356
usesysid         | 10
usename          | postgres
application_name | slave
client_addr      | 172.31.57.96
client_hostname  | 
client_port      | 34172
backend_start    | 2019-01-01 09:40:25.070405+00
state            | streaming
sent_location    | 0/30002A8
write_location   | 0/30002A8
flush_location   | 0/30002A8
replay_location  | 0/30002A8
sync_priority    | 1
sync_state       | sync
  • 172.31.57.96 と、同期 sync_state: sync のレプリケーションができていることが確認できた

マスタが更新されるとスタンバイが更新されることを確認

スタンバイでテーブルをSELECT

$ sudo su - postgres
$ psql mydb
mydb=# SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

マスタでテーブルを更新

$ sudo su - postgres
$ psql mydb
mydb=# INSERT INTO weather VALUES ('Tokyo', 46, 50, 0.25, '1994-11-27');
INSERT 0 1

スタンバイでテーブルをSELECT

mydb=# SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 Tokyo         |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

カラムが追加された! 👏

スタンバイでテーブルを更新

mydb=# INSERT INTO weather VALUES ('hoge', 46, 50, 0.25, '1994-11-27');
ERROR:  cannot execute INSERT in a read-only transaction

エラーになることを確認