1.目的
細かい設定まで行うことが出来るpostgreSQLのdocker-composeが欲しいので、Docker公式イメージをもとに試してみる。
dockerもpostgresqlも初心者なのでよりよい方法があればご教授お願いしますm(_ _)m
Docker導入までは以下を参照。
https://qiita.com/tsnb/items/d10d2770c08abc60b500
前回ver.01の導入は以下。細かい説明を書いている珍しくメモじゃないやつ。
https://qiita.com/tsnb/items/c70cd17453281356a27f
2.構築ver.02
ver.02でやりたいこと:
(A).ロケールを日本語に変更する
(B).レプリケーション設定する
ローカルで閉じる。ディスクは分ける。
ディレクトリ構造
.
|--Dockerfile
|--config.sh
|--docker-compose.yml
|--replication.sh
docker-compose.yml
version: '3'
services:
postgres-prd-master:
build:
context: .
# Dockerfileをbuildしたイメージを利用する
dockerfile: "Dockerfile"
environment:
PGDATA: /var/lib/postgresql/data/pgdata
POSTGRES_USER: "postgres"
POSTGRES_PASSWORD: "パスワード"
POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=ja_JP.UTF-8 "
ports:
# masterのポート番号
- "35432:5432"
tty: true
restart: always
user: root
volumes:
- /export/postgres/master/data:/var/lib/postgresql/data/
- /export/postgres/master/postgres_data:/postgres_data/
stdin_open: true
# pgはslaveではなくstandbyと呼ぶらしい
postgres-prd-slave:
build:
context: .
dockerfile: "Dockerfile"
environment:
PGDATA: /var/lib/postgresql/data/pgdata
POSTGRES_USER: "postgres"
POSTGRES_PASSWORD: "パスワード"
POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=ja_JP.UTF-8 "
POSTGRES_MASTER_SERVICE_HOST: postgres-prd-master
REPLICATION_ROLE: slave
ports:
- "35433:5432"
tty: true
restart: always
user: root
volumes:
# masterとは物理的に異なるディスクをマウントした場所
- /postgres/slave/data:/var/lib/postgresql/data/
- /postgres/slave/postgres_data:/postgres_data/
stdin_open: true
links:
- postgres-prd-master
以下はすべてどっかの転がってたDockerfileをパクっていじったやつ。
Dockerfile
FROM postgres:9.6
#lang settings
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV LANG ja_JP.utf8
# common settings
ENV MAX_CONNECTIONS 500
ENV WAL_KEEP_SEGMENTS 256
ENV MAX_WAL_SENDERS 100
# master/slave settings
ENV REPLICATION_ROLE master
ENV REPLICATION_USER postgres
ENV REPLICATION_PASSWORD ""
# slave settings
ENV POSTGRES_MASTER_SERVICE_HOST localhost
ENV POSTGRES_MASTER_SERVICE_PORT 5432
COPY config.sh /docker-entrypoint-initdb.d/
COPY replication.sh /docker-entrypoint-initdb.d/
RUN sed -i 's/set -e/set -e -x\nPGDATA=$(eval echo "$PGDATA")/' /docker-entrypoint.sh
config.sh
#!/bin/bash
set -e
echo [*] configuring $REPLICATION_ROLE instance
echo "max_connections = $MAX_CONNECTIONS" >> "$PGDATA/postgresql.conf"
# We set master replication-related parameters for both slave and master,
# so that the slave might work as a primary after failover.
echo "wal_level = hot_standby" >> "$PGDATA/postgresql.conf"
echo "wal_keep_segments = $WAL_KEEP_SEGMENTS" >> "$PGDATA/postgresql.conf"
echo "max_wal_senders = $MAX_WAL_SENDERS" >> "$PGDATA/postgresql.conf"
# slave settings, ignored on master
echo "hot_standby = on" >> "$PGDATA/postgresql.conf"
echo "host replication $REPLICATION_USER 0.0.0.0/0 trust" >> "$PGDATA/pg_hba.conf"
replication.sh
#!/bin/bash
set -e
if [ $REPLICATION_ROLE = "master" ]; then
psql -U postgres -c "CREATE ROLE $REPLICATION_USER WITH REPLICATION PASSWORD '$REPLICATION_PASSWORD' LOGIN"
elif [ $REPLICATION_ROLE = "slave" ]; then
# stop postgres instance and reset PGDATA,
# confs will be copied by pg_basebackup
pg_ctl -D "$PGDATA" -m fast -w stop
# make sure standby's data directory is empty
rm -r "$PGDATA"/*
pg_basebackup \
--write-recovery-conf \
--pgdata="$PGDATA" \
--xlog-method=fetch \
--username=$REPLICATION_USER \
--host=$POSTGRES_MASTER_SERVICE_HOST \
--port=$POSTGRES_MASTER_SERVICE_PORT \
--progress \
--verbose
# useless postgres start to fullfil docker-entrypoint.sh stop
pg_ctl -D "$PGDATA" \
-o "-c listen_addresses=''" \
-w start
fi
echo [*] $REPLICATION_ROLE instance configured!
メモ:
手動で遅延設定を1dで入れて再起動しておく。
cat /var/lib/postgresql/data/pgdata/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replication passfile=/home/postgres/.pgpass host=postgres-prd-master port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
recovery_min_apply_delay = '1d'
参考:
https://www.sraoss.co.jp/technology/postgresql/images/20140808_PostgreSQL9.4_report_sraoss.pdf