postgresでstreaming replication構成を構築する #PostgreSQL

postgres　streaming replicationでpostgresの待機系を構築する。
待機系はリードレプリカとなり、参照系のクエリは負荷分散される。

本番環境設定前に Vagrant + VirtualBox　で動作確認を実施する。

必要なソフトウエア

手順

postgresインストール
プライマリ側のreplication設定
replication用のユーザ作成
db同期(プライマリ→セカンダリ)
セカンダリ側のreplication設定

今回の手順では、上記1,2までをAnsibleで実施する。
以下手順で利用するvagrantユーザのpasswordはvagrant

仮想環境構築

Vagrantの設定ファイルを以下より取得し、仮想環境を立ち上げる。

Vagrantfile

構築コマンド　※Vagrantfileを配置したフォルダで実施

bash

$ vagrant up --provider virtualbox

ベースイメージ(CentOS7)をVagrant Cloudより取得し、仮想環境を2台立ち上げる。
init shellで必要なソフトウエアのinstallを実施する。

postgresセットアップ、プライマリ側のreplication設定

Ansibleでpostgresのセットアップ（プライマリ、セカンダリ）、プライマリ側のreplicaiton設定を実施する。
AnsilbeのplaybookはGitHubより取得する。

playbookを実施するため、プライマリノードへssh

bash

$ vagrant ssh pg1

vagrantユーザにてsshでプライマリ(ssh 192.168.202.1)、セカンダリ(ssh 192.168.202.2)への認証を実施した後、playbookを実施する。

bash

$ git clone https://github.com/Thirosue/ansible-samples.git
$ cd ansible-samples/postgres-streaming-replication/
$ ansible-playbook -i hosts main.yml -vv

postgres.confをdefaultから変更した箇所を抜粋。

postgresql.conf

wal_level = hot_standby         # minimal, archive, or hot_standby
                    # (change requires restart)
synchronous_commit = local      # synchronization level;
                    # off, local, remote_write, or on
max_wal_senders = 2     # max number of walsender processes
                # (change requires restart)
log_filename = 'postgresql-%Y-%m-%d.log'    # log file name pattern,
                    # can include strftime() escapes

pg_hba.confに必要な同期設定を抜粋

pg_hba.conf

# スタンバイより接続を受付けるために設定
host    replication     repl_user        0.0.0.0/0            trust
# localhostよりバックアップコマンドを受付けるために設定
host    replication     repl_user        ::1/128            trust

プライマリで同期用のユーザを作成

postgresユーザにスイッチした上で、postgresを起動し、同期用のユーザを作成する。

$ pg_ctl start -D /var/lib/pgsql/data/
$ psql
postgres=# CREATE USER repl_user REPLICATION PASSWORD 'password';
CREATE ROLE

DB同期

プライマリでDBバックアップを実施し、セカンダリへ同期する。
プライマリで取得したバックアップをscpしてセカンダリのdataディレクトリとする。

以下プライマリで実施
→バックアップの実施及びデータディレクトリのscp

$ mkdir /var/lib/pgsql/bk
$ pg_basebackup -h localhost -U repl_user -D /var/lib/pgsql/bk/ -P --xlog
36413/36413 kB (100%), 1/1 tablespace

$ cd /var/lib/pgsql/
$ tar cvfz bk.tgz bk/
$ scp bk.tgz vagrant@192.168.202.2:

以下セカンダリで実施
→バックアップファイルをデータディレクトリとする

$ mv bk.tgz /var/lib/pgsql/
$ tar xvfz bk.tgz
$ rm -rf /var/lib/pgsql/data/
$ mv bk /var/lib/pgsql/data/

セカンダリ側のreplication設定

postgresql.confにスタンバイ設定を追記

bash

$ echo 'hot_standby = on' >> /var/lib/pgsql/data/postgresql.conf

recovery.confを追加し、同期設定を実施

bash

$ echo 'standby_mode = on' >> /var/lib/pgsql/data/recovery.conf
$ echo "primary_conninfo = 'host=192.168.202.1 port=5432 user=repl_user application_name=192.168.202.2'" >> /var/lib/pgsql/data/recovery.conf

スレーブの起動

bash

$ pg_ctl start -D /var/lib/pgsql/data/

同期されているかログを確認する

bash

$ tail -f /var/lib/pgsql/data/pg_log/postgresql-2017-05-01.log

28351 2017-05-01 08:34:43 EDTLOG:  received smart shutdown request
28354 2017-05-01 08:34:43 EDTLOG:  shutting down
28354 2017-05-01 08:34:43 EDTLOG:  database system is shut down
28386 2017-05-01 08:35:49 EDTLOG:  database system was shut down in recovery at 2017-05-01 08:34:43 EDT
28386 2017-05-01 08:35:49 EDTLOG:  entering standby mode
28386 2017-05-01 08:35:49 EDTLOG:  redo starts at 0/2000020
28386 2017-05-01 08:35:49 EDTLOG:  consistent recovery state reached at 0/3000000
28384 2017-05-01 08:35:49 EDTLOG:  database system is ready to accept read only connections
28390 2017-05-01 08:35:49 EDTLOG:  streaming replication successfully connected to primary

　replication設定確認

プライマリにpgbenchでデータを投入し、セカンダリに正常に同期されるか確認する。

以下プライマリで実施
→testデータベースを作成し、pgbenchでデータ投入

bash

$ psql
postgres=# create database test;
CREATE DATABASE
postgres=# \q

$ pgbench -i test
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

$ psql test
psql (9.2.18)
"help" でヘルプを表示します.

test=# \d
                リレーションの一覧
 スキーマ |       名前       |    型    |  所有者
----------+------------------+----------+----------
 public   | pgbench_accounts | テーブル | postgres
 public   | pgbench_branches | テーブル | postgres
 public   | pgbench_history  | テーブル | postgres
 public   | pgbench_tellers  | テーブル | postgres
(4 行)

test=# select count(1) from pgbench_accounts;
 count
--------
 100000
(1 行)

以下セカンダリで実施
→データが正常に同期されていることを確認する

bash

$ psql test
psql (9.2.18)
"help" でヘルプを表示します.

test=# \d
                リレーションの一覧
 スキーマ |       名前       |    型    |  所有者
----------+------------------+----------+----------
 public   | pgbench_accounts | テーブル | postgres
 public   | pgbench_branches | テーブル | postgres
 public   | pgbench_history  | テーブル | postgres
 public   | pgbench_tellers  | テーブル | postgres
(4 行)

test=# select count(1) from pgbench_accounts;
 count
--------
 100000
(1 行)