Edited at

postgresでstreaming replication構成を構築する

More than 1 year has passed since last update.

postgres streaming replicationでpostgresの待機系を構築する。

待機系はリードレプリカとなり、参照系のクエリは負荷分散される。

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

スクリーンショット 2017-05-02 時刻 9.26.19.png


必要なソフトウエア


手順


  1. postgresインストール

  2. プライマリ側のreplication設定

  3. replication用のユーザ作成

  4. db同期(プライマリ→セカンダリ)

  5. セカンダリ側の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 行)