PostgreSQLのレプリケーション構成をインストールしてみた。

************************************************************
********************　①マスタ側を設定する******************
************************************************************

[root@node2 ~]# vi /etc/hosts　★hostsファイルを改修する
[root@node2 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1 localhost.localdomain localhost
#::1 localhost6.localdomain6 localhost6

192.168.56.112 node2
192.168.56.113 node3
[root@node2 ~]# /etc/init.d/iptables stop　★Firewallを止める
ファイアウォールルールを適用中: [ OK ]
チェインポリシーを ACCEPT に設定中filter [ OK ]
iptables モジュールを取り外し中 [ OK ]

-bash-3.2$ vi postgresql.conf　★postgresql.confを改修する。
　★改修内容は次の通りです。
| # Add settings for extensions here
| listen_addresses = '*'
| checkpoint_timeout = 15min
| hot_standby = on
| logging_collector = on
| log_filename = 'postgresql-%Y-%m-%d.log'
| log_min_duration_statement = 60
| log_checkpoints = on
| log_lock_waits = on
| log_error_verbosity = verbose
| log_line_prefix = '%m [%p] '
|
| wal_level = hot_standby
| max_wal_senders = 3
-bash-3.2$ egrep -i 'listen_addresses|checkpoint_timeout|hot_standby|logging_collector|log_filename|log_min_duration_statement|log_checkpoints|log_lock_waits|log_error_verbosity|log_line_prefix|wal_level|max_wal_senders' postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listen_addresses = '*'
wal_level = hot_standby # minimal, archive, hot_standby, or logical
checkpoint_timeout = 15min # range 30s-1h
max_wal_senders = 3 # max number of walsender processes
hot_standby = on # "on" allows queries during recovery
#hot_standby_feedback = off # send info from standby to prevent
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# These are only used if logging_collector is on:
#log_filename = 'postgresql-%a.log' # log file name pattern,
log_filename = 'postgresql-%Y%m%d.log'
log_min_duration_statement = 60 # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_line_prefix = '%m [%p] '
log_lock_waits = on # log lock waits >= deadlock_timeout

-bash-3.2$ vi pg_hba.conf　★pg_hba.confを改修する。
-bash-3.2$ cat pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
＜省略＞
local all postgres peer
host hoge piyo 192.168.56.0/24 trust
host replication postgres 192.168.56.0/24 trust　★追加した行
-bash-3.2$ pg_ctl start -w　★マスタ側をスタートする
サーバの起動完了を待っています....2019-05-09 13:31:54.159 JST [3490] LOG: XX000: could not create IPv6 socket: Address family not supported by protocol
2019-05-09 13:31:54.159 JST [3490] LOCATION: StreamServerPort, pqcomm.c:435
2019-05-09 13:31:54.381 JST [3490] LOG: 00000: redirecting log output to logging collector process
2019-05-09 13:31:54.381 JST [3490] HINT: Future log output will appear in directory "pg_log".
2019-05-09 13:31:54.381 JST [3490] LOCATION: SysLogger_Start, syslogger.c:622
完了
サーバ起動完了
-bash-3.2$

************************************************************
********************②スレーブ側を設定する******************
****(前提：RPMインストール、PGDATA環境変数を設定する)*******
************************************************************
[root@node3 ~]# cat /etc/hosts　★hostsファイルを改修する
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1 localhost.localdomain localhost
#::1 localhost6.localdomain6 localhost6

#127.0.0.1 node3 localhost
#172.16.30.170 node3 localhost

192.168.56.112 node2
192.168.56.113 node3
[root@node3 ~]# /etc/init.d/iptables stop　★Firewallを止める
ファイアウォールルールを適用中: [ OK ]
チェインポリシーを ACCEPT に設定中filter [ OK ]
iptables モジュールを取り外し中 [ OK ]
[root@node3 ~]# su - postgres
-bash-3.2$ pg_basebackup -h 192.168.56.112 -D $PGDATA -X stream --progress -U postgres -R　★ベースバックアップを取得する
29756/29756 kB (100%), 1/1 tablespace
-bash-3.2$ env | grep PG
PGDATA=/var/lib/pgsql/9.5/data
-bash-3.2$ cd /var/lib/pgsql/9.5/data
-bash-3.2$ ll
合計 124
-rw------- 1 postgres postgres 4 5月 9 13:34 PG_VERSION
-rw------- 1 postgres postgres 206 5月 9 13:34 backup_label　★取得したベースバックアップ
drwx------ 6 postgres postgres 4096 5月 9 13:34 base
drwx------ 2 postgres postgres 4096 5月 9 13:34 global
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_clog
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_commit_ts
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_dynshmem
-rw------- 1 postgres postgres 4574 5月 9 13:34 pg_hba.conf
-rw------- 1 postgres postgres 1636 5月 9 13:34 pg_ident.conf
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_log
drwx------ 4 postgres postgres 4096 5月 9 13:34 pg_logical
drwx------ 4 postgres postgres 4096 5月 9 13:34 pg_multixact
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_notify
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_replslot
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_serial
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_snapshots
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_stat
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_stat_tmp
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_subtrans
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_tblspc
drwx------ 2 postgres postgres 4096 5月 9 13:34 pg_twophase
drwx------ 3 postgres postgres 4096 5月 9 13:34 pg_xlog
-rw------- 1 postgres postgres 88 5月 9 13:34 postgresql.auto.conf
-rw------- 1 postgres postgres 21319 5月 9 13:34 postgresql.conf
-rw-r--r-- 1 postgres postgres 137 5月 9 13:34 recovery.conf
-bash-3.2$ vi recovery.conf　★recovery.confを改修する
-bash-3.2$ cat recovery.conf
#standby_mode = 'on'　★コメントアウトした行
#primary_conninfo = 'user=postgres host=192.168.56.112 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'　★コメントアウトした行

standby_mode = 'on'　★追加した行
primary_conninfo = 'user=postgres host=192.168.56.112 port=5432'　★追加した行

-bash-3.2$ pg_ctl start -w
サーバの起動完了を待っています....2019-05-09 13:39:02.788 JST [4317] LOG: XX000: could not create IPv6 socket: Address family not supported by protocol
2019-05-09 13:39:02.788 JST [4317] LOCATION: StreamServerPort, pqcomm.c:435
2019-05-09 13:39:02.858 JST [4317] LOG: 00000: redirecting log output to logging collector process
2019-05-09 13:39:02.858 JST [4317] HINT: Future log output will appear in directory "pg_log".
2019-05-09 13:39:02.858 JST [4317] LOCATION: SysLogger_Start, syslogger.c:622
.完了
サーバ起動完了

-bash-3.2$ psql postgres -c "CREATE TABLE test_table(i int)"　★テスト用の表を作成する
CREATE TABLE
-bash-3.2$ psql postgres -c "INSERT INTO test_table VALUES (1)"★一行データを投入する
INSERT 0 1
-bash-3.2$ psql postgres -c "SELECT * FROM test_table"　★結果が取得てきる
i
---
1
(1 行)
※スレーブ側へ遷移する
-bash-3.2$ psql postgres -c "SELECT * FROM test_table"　★スレーブ側も同じ結果を取得できた
i
---
1
(1 行)

************************************************************
********************④同期モードへ変更する******************
************************************************************
※マスタ側
-bash-3.2$ cat postgresql.conf | grep synchronous_standby_names
#synchronous_standby_names = '' # standby servers that provide sync rep
-bash-3.2$ vi postgresql.conf　★変更する
-bash-3.2$
-bash-3.2$ cat postgresql.conf | grep synchronous_standby_names
synchronous_standby_names = 'node3'　★追加した行。スレーブ側のホスト名を指定した
#synchronous_standby_names = '' # standby servers that provide sync rep
-bash-3.2$ pg_ctl start -w　★マスタを起動させる
サーバの起動完了を待っています....2019-05-09 14:22:19.058 JST [3715] LOG: XX000: could not create IPv6 socket: Address family not supported by protocol
2019-05-09 14:22:19.059 JST [3715] LOCATION: StreamServerPort, pqcomm.c:435
2019-05-09 14:22:19.089 JST [3715] LOG: 00000: redirecting log output to logging collector process
2019-05-09 14:22:19.089 JST [3715] HINT: Future log output will appear in directory "pg_log".
2019-05-09 14:22:19.089 JST [3715] LOCATION: SysLogger_Start, syslogger.c:622
完了
サーバ起動完了
-bash-3.2$ ps -ef | grep postgres
root 3593 3379 0 14:03 pts/1 00:00:00 su - postgres
postgres 3594 3593 0 14:03 pts/1 00:00:00 -bash
postgres 3715 1 0 14:22 pts/1 00:00:00 /usr/pgsql-9.5/bin/postgres
postgres 3722 3715 0 14:22 ? 00:00:00 postgres: logger process
postgres 3724 3715 0 14:22 ? 00:00:00 postgres: checkpointer process
postgres 3725 3715 0 14:22 ? 00:00:00 postgres: writer process
postgres 3726 3715 0 14:22 ? 00:00:00 postgres: wal writer process
postgres 3728 3715 0 14:22 ? 00:00:00 postgres: wal sender process postgres 192.168.56.113(44368) streaming 0/3015858　★
postgres 3745 3594 0 14:30 pts/1 00:00:00 ps -ef
postgres 3746 3594 0 14:30 pts/1 00:00:00 grep postgres

※スレーブ側へ遷移する
-bash-3.2$ cat recovery.conf
#standby_mode = 'on'
#primary_conninfo = 'user=postgres host=192.168.56.112 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.56.112 port=5432'

-bash-3.2$ vi recovery.conf
-bash-3.2$ cat recovery.conf
#standby_mode = 'on'
#primary_conninfo = 'user=postgres host=192.168.56.112 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.56.112 port=5432 application_name=node3'　★編集した箇所
-bash-3.2$ pg_ctl start -w　★スレーブ側を起動させる
サーバの起動完了を待っています....2019-05-09 14:22:27.030 JST [3372] LOG: XX000: could not create IPv6 socket: Address family not supported by protocol
2019-05-09 14:22:27.030 JST [3372] LOCATION: StreamServerPort, pqcomm.c:435
2019-05-09 14:22:27.250 JST [3372] LOG: 00000: redirecting log output to logging collector process
2019-05-09 14:22:27.250 JST [3372] HINT: Future log output will appear in directory "pg_log".
2019-05-09 14:22:27.251 JST [3372] LOCATION: SysLogger_Start, syslogger.c:622
完了
サーバ起動完了
-bash-3.2$ ps -ef | grep postgres
root 3295 3155 0 14:05 pts/1 00:00:00 su - postgres
postgres 3296 3295 0 14:05 pts/1 00:00:00 -bash
postgres 3372 1 0 14:22 pts/1 00:00:00 /usr/pgsql-9.5/bin/postgres
postgres 3379 3372 0 14:22 ? 00:00:00 postgres: logger process
postgres 3380 3372 0 14:22 ? 00:00:00 postgres: startup process recovering 000000010000000000000003　★
postgres 3381 3372 0 14:22 ? 00:00:00 postgres: checkpointer process
postgres 3382 3372 0 14:22 ? 00:00:00 postgres: writer process
postgres 3383 3372 0 14:22 ? 00:00:00 postgres: wal receiver process streaming 0/3015858　★
postgres 3402 3296 0 14:31 pts/1 00:00:00 ps -ef
postgres 3403 3296 0 14:31 pts/1 00:00:00 grep postgres