これの続き
やったこと
- EC2環境を2台構築
- マスタの環境構築
- スタンバイの環境構築
- マスタでレプリケーションステータスを確認
- マスタが更新されるとスタンバイが更新されることを確認
EC2環境を2台構築
- web UI から Amazon linux の t2.small を作成
- セキュリティグループで、SSHと、PostgreSQLを許可
環境のIP
- マスタ: 172.31.59.13
- スタンバイ: 172.31.57.96
マスタの環境構築
概要
- PostgreSQLのインストール
- DB、テーブルを作成して、動作確認用のデータを挿入
- 他のサーバからアクセスするため、
postgresユーザにパスワードを設定 - 同期レプリケーションのための設定を
postgresql.confに設定 - WALアーカイブディレクトリを作成
- 他サーバから
postgresユーザでアクセスするために、pg_hba.confを設定 - 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行目)も追加
- 接続するDB の
replicationはレプリケーション用の特別な設定 - 認証方法
md5はパスワード認証
PostgreSQLサーバ再起動
$ sudo service postgresql-9.3 restart
スタンバイ環境構築
概要
- PostgreSQLのインストール
- マスタのベースバックアップを取得
- recovery.conf にスタンバイサーバの設定を追記
- postgresql.conf にホットスタンバイの設定を追記
- 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
エラーになることを確認
![[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)](https://images-fe.ssl-images-amazon.com/images/I/51G-Ui96m4L._SL160_.jpg "[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)")
