あらすじ

こんな便利な時代に自前でPostgreSQLを立てる必要が出た。悲しきオンプレのさだめよ。当方PostgreSQL素人なのでいちから構築手順をメモしていく。

要件

ミドルウェアが利用するPostgreSQLでそんなにパフォーマンスも容量もいらない。でも、ちゃんと冗長化していて欲しいし、障害時にはフェイルオーバして欲しい。そんな感じ。

全体像

とりあえずPostgreSQLの同期レプリケーションでアクティブ/スタンバイの構成を作りたい。

使用バージョン

OS:CentOS7
PostgreSQL:9.6の最新

インストール手順

yumレポジトリをインストールするrpmパッケージの入手

ここにある。

PostgreSQL RPM Repository (with Yum)

以下のコマンドにてpostgresqlのレポジトリがyum.repoに追加される。

yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

postgresql-serverのインストール

そして実際のpostgresqlサーバのインストール。

yum install -y postgresql96-server

データベースディレクトリの初期化

サービスを起動するまえにデータベースディレクトリが必要なそうな。データベースあるある。

/usr/pgsql-9.6/bin/postgresql96-setup initdb

/var/lib/pgsql/9.6/data/を覗くと色々ファイルができていた。

サービス起動

初期化後にサービスを立ち上げるとpostgresqlが立ちあがる。

# systemctl start postgresql-9.6
# systemctl status postgresql-9.6
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; disabled; vendor preset: disabled)
   Active: active (running) since 水 2017-10-04 04:40:31 UTC; 5s ago
  Process: 28155 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 28161 (postmaster)
   CGroup: /system.slice/postgresql-9.6.service
           ├─28161 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
           ├─28163 postgres: logger process   
           ├─28165 postgres: checkpointer process   
           ├─28166 postgres: writer process   
           ├─28167 postgres: wal writer process   
           ├─28168 postgres: autovacuum launcher process   
           └─28169 postgres: stats collector process   

10月 04 04:40:31 test-pg-1 systemd[1]: Starting PostgreSQL 9.6 database server...
10月 04 04:40:31 test-pg-1 postmaster[28161]: < 2017-10-04 04:40:31.246 UTC > LOG:  redirecting log output to logging collector process
10月 04 04:40:31 test-pg-1 postmaster[28161]: < 2017-10-04 04:40:31.246 UTC > HINT:  Future log output will appear in directory "pg_log".
10月 04 04:40:31 test-pg-1 systemd[1]: Started PostgreSQL 9.6 database server.

おKおK

各種設定

一旦、Postgresqlサーバを止めて各種設定を確定させて行く。前述したとおりパフォーマンス要件は厳しく無いので、セキュリティやloggingの設定を主に注意してやっていく。

が、ここで問題が発生して、どうもpostgresqlの設定ファイルはinitdbで作成するディレクトリの/var/lib/pgsql/9.6/data/の下にできるそうな。個人的には設定ファイルは/etc/pgsqlの下におかれていて欲しいなぁ。。。と思っていたと所、以下のドキュメントを発見した。

PostgreSQL: Documentation: 9.6: File Locations

これを読む限り、config_fileパラメータを起動時に渡してやれば諸々上手くいくそうだ。systemdのユニットファイルをいじれば良さそう。

以下は個々のconfigファイルについて

接続周り

listen_addresses = '*'

全部のIPで待ち受けますよ的なおなじみのやつ。

WAL/レプリケーション

wal_level                 = replica
max_wal_senders           = 5
max_replication_slots     = 2
track_commit_timestamp    = on

wal_lebel

レプリケーションで使用するトランザクションログの保存レベル
9.6以前はarchiveとかhost_stanbyとかあったらしいが9.6からレプリする場合はreplicaとなった

max_wal_senders

walをレプリスレーブに送信するプロセス数
再接続に備えて、実際のスレーブ数より少し多めにすると良いらしい

max_replication_slots

レプリスレーブがどこまでwalを読んだか？という情報を管理するためのスロット数
レプリ台数にする必要がある
また、一度上げてから下げようとするとエラーになるらしい
同期レプリの場合いらない気もするけども、とりあえず設定

マスタ固有

synchronous_standby_names = '1 (pg2, pg3)'

同期レプリするための設定なので、レプリ先がいない状態で反映すると動かなくなる

スレーブ固有

hot_standby = on

スタンバイ側でクエリを許可

ロギング設定

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_line_prefix = '< %m > '
log_timezone = 'UTC'

ほとんどデフォルトでコメントアウトされていなかったパラメータそのまま。 log_directoryだけ、/var/log下に書き込まれるとうにへんこうした、、、がlog_destinationが'stderr'なので本当に吐かれるかは疑問。 log_destinationをstderrのままにしているのはsystemdのjournaldにログを監視してもらうため。

クライアント接続設定

datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

デフォルトでコメントアウトされていなかったパラメータそのまま。

パフォーマンス設定

何度も言うが、パフォーマンス要件は厳しく無い。基本的なパフォーマンスの設定だけ抑えておけると良いのだが、何か良い記事は無いかとあさってるといかのページにたどり着いた。

pgtune.leopard.in.ua

なにやらHW状態に合わせて設定を生成してくれるらしい。これを使って実運用で問題出たら都度チューニングしていこう。検証環境に合わせて生成すると以下の感じになった。

max_connections              = 1024
shared_buffers               = 1GB
effective_cache_size         = 3GB
work_mem                     = 1MB
maintenance_work_mem         = 256MB
min_wal_size                 = 1GB
max_wal_size                 = 2GB
checkpoint_completion_target = 0.7
wal_buffers                  = 16MB
default_statistics_target    = 100

ディレクトリとパス設定

include_dir = 'conf.d'

コンフィグファイルは分割してincludeできるみたいなので、よしなにincludeさせる。

認証ファイル設定

ホスト認証周りの設定はpg_hba.confで行うようだ。とりあえず、社内DCにいるサーバにはhogeユーザでhogeデータベースにパスワード指定でログイン可能なようにゆるく許可を与える。あと検証なので、レプリもゆるく開けている。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    sameuser        hoge            192.168.0.0/16          md5
host    replication     postgres        192.168.0.0/16          trust

一瞬、このファイルを設定変更した際、リスタートせんとアカンのん？？と思ったが、SIGHUPでリロードしてくれるらしい。ですよねー。良かった。

systemdの設定

個別の設定ファイルをロードさせるためにsystemdのUnitファイルを変更する。

[Unit]
Description=PostgreSQL 9.6 database server
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres
PermissionsStartOnly=true

Environment=PGDATA=/var/lib/pgsql/9.6/data/
Environment=PGCONFIG=/etc/pgsql/

OOMScoreAdjust=-1000

ExecStartPre=-/usr/pgsql-9.6/bin/postgresql96-setup initdb
ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.6/bin/postmaster \
          -c config_file=${PGCONFIG}/postgresql.conf \
          -c hba_file=${PGCONFIG}/pg_hba.conf \
          -c ident_file=${PGCONFIG}/pg_ident.conf \
          -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
 
TimeoutSec=300

[Install]
WantedBy=multi-user.target

ExecStartPreでinitdbを行うように変更した。あと、configを読む箇所を変えたいので起動時パラメータでコンフィグのロード先を指定している。

最終的なファイル構成

こんな感じになった。

/etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL 9.6 database server
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres
PermissionsStartOnly=true

Environment=PGDATA=/var/lib/pgsql/9.6/data/
Environment=PGCONFIG=/etc/pgsql/

OOMScoreAdjust=-1000

ExecStartPre=-/usr/pgsql-9.6/bin/postgresql96-setup initdb
ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.6/bin/postmaster \
          -c config_file=${PGCONFIG}/postgresql.conf \
          -c hba_file=${PGCONFIG}/pg_hba.conf \
          -c ident_file=${PGCONFIG}/pg_ident.conf \
          -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
 
TimeoutSec=300

[Install]
WantedBy=multi-user.target

/etc/pgsql/postgresql.conf

listen_addresses = '*'
include_dir      = 'conf.d'

/etc/pgsql/conf.d/replication.conf

wal_level                 = replica
max_wal_senders           = 5
max_replication_slots     = 2
track_commit_timestamp    = on

/etc/pgsql/conf.d/master.conf

synchronous_standby_names = '1 (pg2, pg3)'

/etc/pgsql/conf.d/slave.conf

hot_standby = on

/etc/pgsql/conf.d/logging.conf

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_line_prefix = '< %m > '
log_timezone = 'UTC'

/etc/pgsql/conf.d/client.conf

datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

/etc/pgsql/conf.d/pgtune.conf

max_connections              = 1024
shared_buffers               = 1GB
effective_cache_size         = 3GB
work_mem                     = 1MB
maintenance_work_mem         = 256MB
min_wal_size                 = 1GB
max_wal_size                 = 2GB
checkpoint_completion_target = 0.7
wal_buffers                  = 16MB
default_statistics_target    = 100

/etc/pgsql/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    sameuser        hoge            192.168.0.0/16          md5
host    replication     postgres        192.168.0.0/16          trust

/etc/pgsql/pg_ident.conf

使わないので空ファイル

再起動

ファイル巻き終わったらsystemdを更新して既存のデータディレクトリも消して再起動を行う。

systemctl daemon-reload
rm -rf /var/lib/pgsql/9.6/data/*
systemctl restart postgresql

この状態だと起動はするけど同期レプリケーションがいないので書き込みができない。なのでスレイブを作成してきどうさせる必要がある。

レプリケーションスレイブの作成

同じ設定をアクティブと同様に巻く。撒いた後はbg_backupコマンドを使ってSlave用のデータディレクトリを構成する。

su -c "pg_basebackup -R -D /var/lib/pgsql/9.6/data/ -h 192.168.0.1 -p 5432 -U postgres" postgres

バックアップを取った後、recovery.confを編集する必要がある。色々調べたけど、このファイルはDataディレクトリにないといけないみたいだ。。。皆デプロイどうしてんだろう？

standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.0.1 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=pg2'

application_name=pg2を追加する必要がある。

設定が終わったら起動させる。

systemctl start postgresql.service

アクティブのレプリケーション状態の確認

su -c 'psql -c "SELECT * FROM pg_stat_replication" -x' postgres
-[ RECORD 1 ]----+------------------------------
pid              | 30960
usesysid         | 10
usename          | postgres
application_name | pg2
client_addr      | 192.168.0.2
client_hostname  | 
client_port      | 51696
backend_start    | 2017-10-05 04:58:13.243582+00
backend_xmin     | 
state            | streaming
sent_location    | 0/7000060
write_location   | 0/7000060
flush_location   | 0/7000060
replay_location  | 0/7000060
sync_priority    | 1
sync_state       | sync

同期レプリされてるっぽいぞ。おｋおｋ。

終わりに

postgresqlの設定をイジってレプリが組めるところまで実施した。本番デプロイに向けて、後はフェイルオーバーをコントロールするサーバと同期レプリカの候補サーバがもう一台あったほうが良いだろう。

kanga333.b?log

雑な記録

PostgreSQLをレプリ構成で立てるまでの記録