Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

『Docker』docker-composeで開発用PostgreSQLを手に入れるメモ(ver.02)

More than 1 year has passed since last update.

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

littlem
無知ゴミのカスです。 Qiitaは業務内容はあまり書かないので興味があることのトライアルしたログ用に使います。
https://t-akzw.github.io/blog/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away