VagrantでPostgreSQLのストリーミングレプリケーションを構成する

2018/12/31
 
この記事を書いている人 - WRITER -
kmryk0526
詳しいプロフィールはこちら

OSS-DB Goldの試験対策も兼ねてPostgreSQLのストリーミングレプリケーションを構築してみました。
Vagrantで2つの仮想サーバserver01、server02を用意します。

server01:プライマリ
server02:スタンバイ

Vagrantが既に導入されていることを前提としています。
ホストマシン上にserver01ディレクトリとserver02ディレクトリを用意し、そこにそれぞれのVagrantfileが置いてある構成です。

プライマリとするサーバの構築

server01作成

server01ディレクトリに移動し、initします。

cd server01
vagrant init generic/centos7

Vagrantfileをviで編集。

vi Vagarntfile

以下を追記。

config.vm.network "private_network", ip: "192.168.33.10"
config.vm.network "forwarded_port", guest: 5432, host: 5432

serve1を起動して、ログインする。

vagrant up
vagrant ssh

vagrant up実行時に以下のメッセージが出る場合がある。
そのときは、Vagrant実行ホストの管理者パスワードを入力する。

==> default: [vagrant-hostsupdater] This operation requires administrative access. You may skip it by manually adding equivalent entries to the hosts file.
Password:

 

OS設定

rootにスイッチする。

sudo - su

ホスト名を変更する。
一度、exitしてまたrootにスイッチするとプロンプト表示がlocalhostからserver01に変わる。

hostnamectl set-hostname server01

タイムゾーンを変更する。
PostgreSQLのデフォルトのタイムゾーンはOSのタイムゾーンになるので、PosrgreSQLインストール前にやっとく。

timedatectl set-timezone Asia/Tokyo

あとあとserver02からのssh接続設定で必要となるので、sshパスワード認証を許可しておく。

vi /etc/ssh/sshd_config
PasswordAuthentication no
↓
PasswordAuthentication yes

wheelグループにパスワードなしsodoを許可

visudo
## Allows people in group wheel to run all commands
%wheel ALL=(ALL)       ALL
↓
## Allows people in group wheel to run all commands
#%wheel ALL=(ALL)       ALL
## Same thing without a password
# %wheel  ALL=(ALL)       NOPASSWD: ALL
↓
## Same thing without a password
%wheel  ALL=(ALL)       NOPASSWD: ALL

PostgreSQLインストール

rpmをダウンロードして、適当な場所(以下では/rpm)に置く。

curl -L -O https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm > /rpm/pgdg-centos96-9.6-3.noarch.rpm

PostgreSQL本体とcontribをインストールする。

yum install postgresql96-server
yum install postgresql96-contrib

postgresql96-serverをインストールすると以下もインストールされる。
・postgresql96.x86_64
・postgresql96-libs.x86_64

yum list installed | grep postgresql96
postgresql96.x86_64           9.6.11-1PGDG.rhel7   @pgdg96  
postgresql96-contrib.x86_64   9.6.11-1PGDG.rhel7   @pgdg96  
postgresql96-libs.x86_64      9.6.11-1PGDG.rhel7   @pgdg96  
postgresql96-server.x86_64    9.6.11-1PGDG.rhel7   @pgdg96

PostgreSQLインストールにより作成されるユーザpostgresだといろいろ不都合あるので再作成する。

userdel postgres
useradd postgres
usermod -aG wheel postgres

作成したpostgresにスイッチ

su - postgres

任意のパスワードを設定

passwd

オーナーをpostgresに変更

sudo chown -R postgres:postgres /var/lib/pgsql

.bash_profileに環境変数定義。

vi .bash_profile
export PATH=/usr/pgsql-9.6/bin:$PATH
export PGDATA=/var/lib/pgsql/9.6/data
source .bash_profile

WAL退避ディレクトリ作成

sudo mkdir -p /var/postgresql/archivedir
sudo chown -R postgres:postgres /var/postgresql/

5432ポートを開放する。

sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0 eth1
  sources: 
  services: ssh dhcpv6-client postgresql
  ports: 5432/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules: 

PostgreSQL設定

DBクラスタ作成

initdb --no-locale --encoding=utf-8

スタンバイ(192.168.33.11)からのレプリケーション接続を許可

vi $PGDATA/pg_hba.conf

以下を追記

host    replication    postgres      192.168.33.11/32     trust

PostgreSQL起動

sudo systemctl start postgresql-9.6.service

alter systemコマンドでパラメータ設定

psql -c "alter system set wal_level = replica"
psql -c "alter system set max_wal_senders = 10"
psql -c "alter system set listen_addresses = '192.168.33.10'"
psql -c "alter system set archive_mode = on"
psql -c "alter system set archive_command = 'test ! -f /var/postgresql/archivedir/%f && cp %p /var/postgresql/archivedir/%f'"
psql -c "alter system set log_filename = 'postgresql-%Y-%m-%d_%H%M.log'"

一応、設定されたことを確認しとく

cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level = 'replica'
max_wal_senders = '10'
listen_addresses = '192.168.33.10'
archive_mode = 'on'
archive_command = 'test ! -f /var/postgresql/archivedir/%f && cp %p /var/postgresql/archivedir/%f'
log_filename = 'postgresql-%Y-%m-%d_%H%M.log'

PostgreSQL再起動して、alter systemで変更したパラメータを有効化する。

sudo systemctl restart postgresql-9.6.service

スタンバイとするサーバの構築

server01と同じ箇所はコメント省略。

server02作成

cd ../server02
vagrant init generic/centos7
vi Vagarntfile

以下を追記。

config.vm.network "private_network", ip: "192.168.33.11"
config.vm.network "forwarded_port", guest: 5432, host: 54321
vagrant up
vagrant ssh

OS設定

sudo - su
hostnamectl set-hostname server02
timedatectl set-timezone Asia/Tokyo

PostgreSQLインストール

yum install postgresql96-server
yum install postgresql96-contrib
userdel postgres
useradd postgres
usermod -aG wheel postgres
su - postgres
sudo chown -R postgres:postgres /var/lib/pgsql
vi .bash_profile
export PATH=/usr/pgsql-9.6/bin:$PATH
export PGDATA=/var/lib/pgsql/9.6/data
source .bash_profile
sudo mkdir -p /var/postgresql/archivedir
sudo chown -R postgres:postgres /var/postgresql/
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

WAL転送でSCPを使いたいので、プライマリのpostgresとスタンバイのpostgresで鍵交換しておく。
まずはキーペアを作成する。
passphraseは入力せず、空EnterでもOK。

ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.

プライマリに登録する。

ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@192.168.33.10
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@192.168.33.10's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.33.10'"
and check to make sure that only the key(s) you wanted were added.

PostgreSQL設定

initdb --no-locale --encoding=utf-8
sudo systemctl start postgresql-9.6.service

alter systemコマンドでパラメータ設定。
後からプライマリのPGDATAをコピーするので、スタンバイ固有の設定のみいれる。

psql -c "alter system set listen_addresses = '192.168.33.11'"
psql -c "alter system set hot_standby = on"
cat $PGDATA/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '192.168.33.11'
hot_standby = 'on'

変更したpostgresql.auto.confを/tmpに退避し、PGDATA配下をまるごと削除する。

mv $PGDATA/postgresql.auto.conf /tmp
rm -rf $PGDATA/*

プライマリの$PGDATAをスタンバイにコピーする。

pg_basebackup -h 192.168.33.10 -D ${PGDATA} -R --progress -U postgres
22259/22259 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

初期化されたpostgresql.auto.confを戻す。

mv /tmp/postgresql.auto.conf $PGDATA/

recovery.confに以下を追記

echo "restore_command = 'scp postgres@192.168.33.10:/var/postgresql/archivedir/%f %p'" >> $PGDATA/recovery.conf
echo "archive_cleanup_command = 'pg_archivecleanup -d /var/postgresql/archivedir %r’” >> $PGDATA/recovery.conf

一応確認する。

cat $PGDATA/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.33.10 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
restore_command = 'scp postgres@192.168.33.10:/var/postgresql/archivedir/%f %p'
archive_cleanup_command = 'pg_archivecleanup -d /var/postgresql/archivedir %r’

PostgreSQL再起動。

sudo systemctl restart postgresql-9.6.service

レプリケーションされていることを確認する。

プライマリでpg_stat_replicationのstateがstreamingとなっていることを確認。

psql -c "SELECT * FROM pg_stat_replication" -x;
-[ RECORD 1 ]----+------------------------------
pid              | 7151
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 192.168.33.11
client_hostname  | 
client_port      | 59216
backend_start    | 2018-12-24 16:34:18.855824+09
backend_xmin     | 
state            | streaming
sent_location    | 0/1C000140
write_location   | 0/1C000140
flush_location   | 0/1C000140
replay_location  | 0/1C000140
sync_priority    | 0
sync_state       | async

プライマリにレコードを入れて、それがスタンバイからも見れることを確認する。

psql -c "create table cities (name varchar(80),location point)"
psql -c "insert into cities values ('Barcelona','(41.384855,2.172164)')"
psql -c "select * from cities" -x
-[ RECORD 1 ]--------------------
name     | Barcelona
location | (41.384855,2.172164)

スタンバイからレコードが参照できた。

psql -c "select * from cities" -x
-[ RECORD 1 ]--------------------
name     | Barcelona
location | (41.384855,2.172164)
この記事を書いている人 - WRITER -
kmryk0526
詳しいプロフィールはこちら

- Comments -

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Copyright© つれづれテック , 2018 All Rights Reserved.