WEB開発備忘録

AWSにPostgreSQL94のレプリケーション環境を構築してみる(EC2で)

ざっくりとした流れ
1.AWSでセキュリティグループにローカルのPostgreSQL接続を解放
2.マスターDBの設定
3.マスターDB再起動
4.スレーブDBにデータ複製
5.スレーブDBを設定
6.スレーブDB起動
7.動作確認

AWSのセキュリティグループを解放

ローカルネットワークからの5432番への接続を解放します。
マスター/スレーブともにDBサーバインスタンスのセキュリティグループ
PostgreSQL プロトコル/TCP ポート範囲/5432 送信元「10.0.0.0/16」
セキュリティグループ設定

 

postgresユーザでのSSH公開鍵認証を許可する(マスターとスレーブ双方向)

マスターからスレーブへ

(1).マスターのposgresql管理ユーザーで公開鍵を作成

[prompt@DB1] # su - postgres
 [prompt@DB1] $ pwd
 /var/lib/pgsql94

[prompt@DB1] $ ssh-keygen -t rsa

※ パスフレーズは無しで作成

[prompt@DB1] $ cat .ssh/id_rsa.pub

※ 公開鍵の中身をコピーする

(2).スレーブに公開鍵を登録する

[prompt@DB2] # su - postgres
 [prompt@DB2] $ pwd
 /var/lib/pgssql94

[prompt@DB2] $ vim .ssh/authorized_keys

※ マスターの公開鍵の中身を貼り付けて保存

[prompt@DB2] $ chmod 700 .ssh
 [prompt@DB2] $ chmod 600 .ssh/authorized_keys

(3).接続確認
マスターからスレーブへ、パス無しでssh接続出来たらOK

[prompt@DB1] # su - postgres
 [prompt@DB1] $ ssh スレーブDBのプライベートIP
スレーブからマスターへ

(1).スレーブのposgresql管理ユーザーで公開鍵を作成

[prompt@DB2] # su - postgres
 [prompt@DB2] $ pwd
 /var/lib/pgsql94

[prompt@DB2] $ ssh-keygen -t rsa

※ パスフレーズは無しで作成

[prompt@DB2] $ cat .ssh/id_rsa.pub

※ 公開鍵の中身をコピーする

(2).マスターに公開鍵を登録する

[prompt@DB1] # su - postgres
 [prompt@DB1] $ pwd
 /var/lib/pgssql94

[prompt@DB1] $ vim .ssh/authorized_keys

※ マスターの公開鍵の中身を貼り付けて保存

[prompt@DB1] $ chmod 700 .ssh
 [prompt@DB1] $ chmod 600 .ssh/authorized_keys

(3).接続確認
スレーブからマスターへ、パス無しでssh接続出来たらOK

[prompt@DB2] # su - postgres
 [prompt@DB2] $ ssh マスターDBのプライベートIP

 

マスターDBサーバの設定

マスターDBのレプリケーション用PostgreSQLユーザー作成

スレーブDBがマスターDBに接続するために

[prompt@DB1] # su - postgres

[prompt@DB1] $ pssql
 postgres=#

CREATE ROLE レプリケーション用ユーザー LOGIN REPLICATION PASSWORD 'レプリケーション用パスワード';

作成されたユーザーの確認
 postgres=# \du

List of roles
 Role name | Attributes | Member of
 -----------+------------------------------------------------+-----------
 postgres | Superuser, Create role, Create DB, Replication | {}
 レプリケーション用ユーザ | Replication | {}
マスターDB用の諸設定

(1).pg_hba.conf末尾にマスター/スレーブDBサーバからのレプリケーション用ユーザによる接続を許可

[prompt@DB1] # su - postgres
[prompt@DB1] $ vim /var/lib/pgsql94/data/pg_hba.conf

host replication レプリケーション用ユーザ 127.0.0.1/32 md5
host replication レプリケーション用ユーザ マスターDBプライベートIP/32 md5
host replication レプリケーション用ユーザ スレーブDBプライベートIP/32 md5

(2).postgresql.confにレプリケーション設定

[prompt@DB1] # su - postgres
[prompt@DB1] $ vim /var/lib/pgsql94/data/postgresql.conf

wal_level = hot_standby

# 同期モード
synchronous_commit = on

# スレーブ+マスター数
max_wal_senders = 2

# 許可するDBスレーブ名(カンマ区切りで)
synchronous_standby_names = '*'

# WALを指定ディレクトリに格納する
archive_mode = on

# アーカイブコマンド
archive_command = 'cp -i %p /var/lib/pgsql94/archive/%f < /dev/null'

# 定期的にWALファイルを残すときのWALファイルの数
wal_keep_segments = 16

# 外部からの接続許可
listen_addresses = '*'

(3).アーカイブ用のディレクトリ作成

[prompt@DB1] # su - postgres
[prompt@DB1] $ mkdir -p /var/lib/pgsql94/archive

(4).設定の反映

[prompt@DB1] # service postgresql94 restart

 

スレーブDBのレプリケーション設定

(1).マスターDBからデータをリストア

[prompt@DB2] # su - postgres
[prompt@DB2] $ pg_basebackup -h マスターDBプライベートIP -U レプリケーション用ユーザ名 -D /var/lib/pgsql94/data -P --xlog

※ パスを聞かれるので、レプリケーション用ユーザーのパスワードを入力

(2).スレーブに「recovery.conf」を作成

[prompt@DB2] # su - postgres
[prompt@DB2] # vim /var/lib/pgsql94/data/recovery.conf

### on 必須
standby_mode = on

### マスターDBサーバのWALアーカイブディレクトリを指定
restore_command = 'scp マスターDBプライベートIP:/var/lib/pgsql94/archive/%f %p'

### マスターDBサーバに作成したレプリケーション用ユーザで接続する
primary_conninfo = 'host=マスターDBプライベートIP port=5432 user=repl_user password=password application_name=スレーブDBエイリアス名(任意)'

(3).スレーブDBの「postgresql.conf」設定

[prompt@DB2] $ vim /var/lib/pgsql94/postgresql.conf
# 以下追記
hot_standby = on

(4).スレイブDB起動

[prompt@DB2] # /etc/init.d/postgresql94 start
動作確認

(1).プロセスで確認

[prompt@DB1] # ps aux | grep レプリケーションユーザー名

postgres 5634 0.0 0.5 334644 5936 ? Ss 17:05 0:00 postgres: wal sender process レプリケーションユーザー スレーブDBプライベートIP(48945) streaming 0/50024D0

[prompt@DB2] # ps aux | grep postgres | grep recover
postgres 3048 0.0 0.7 334440 7660 ? Ss 08:05 0:00 postgres: startup process recovering 000000010000000000000005

(2).更新確認/マスターにDBを作ってみる

[prompt@DB1] # su - postgres
[prompt@DB1] $ psql

postgres=# create database repl_test;
postgres=# \l

マスター/スレーブともにDB「repl_test」が出来ていればOK

 

スレーブが落ちてしまったら??

スレーブDBが落ちてしまったら、マスターへのDB接続も出来なくなります。
復旧させるには、もう一度最初からレプリケーション設定をする必要がありますが、
取り急ぎスレーブの状況調査は後回しにして、レプリケーション前の設定に戻す応急措置。
(Postgresql.confを元ファイルで上書き、再起動)

※ 前提
レプリケーション設定無しのファイルを用意しておく
/var/lib/pgsql94/data/postgresql.conf_norepl

1.レプリケーション無しファイルを現設定ファイルに上書き

[prompt@DB1] # su - postgres
[prompt@DB1] $ cp -pr /var/lib/pgsql94/data/postgresql.conf_norepl /var/lib/pgsql94/data/postgresql.conf

2.マスターDBを再起動

[prompt@DB1] $ exit
[prompt@DB1] # service postgresql94 restart

完全復旧するには? → レプリケーション設定を1から

 

マスターが落ちたらどうする?

スレーブDBの状態を確認。
スレーブDBに問題が無ければ、普通に再起動してOK