Docker上搭建pg_pool实现PG主从自动切换

Docker上搭建pg_pool实现PG主从自动切换

从节点：d14r1n18 10.4.14.19

一、编辑pg_pool2的Dockerfile

 cd /devel/xwq/pgsql_data_dir/
 vi Dockerfile

FROM ubuntu
ENV PGP2_VER 3.6.2
ARG DEBIAN_FRONTEND=noninteractive
RUN apt-get update && \
apt-get install -y curl build-essential libpq-dev vim postgresql-client ssh
#
# Build pg pool2
#
RUN curl -L -o    pgpool-II-${PGP2_VER}.tar.gz    http://www.pgpool.net/download.php?f=pgpool-II-${PGP2_VER}.tar.gz    && \
tar zxvf    pgpool-II-${PGP2_VER}.tar.gz && \
cd /pgpool-II-${PGP2_VER} &&    \
./configure && \
make && \
make install && \
ldconfig && \
rm -rf /pgpool-II-${PGP2_VER}    && \
rm /pgpool-II-${PGP2_VER}.tar.gz
#
# Configure pg pool2
#
ENV PCP_USER docker
ENV PCP_PASS docker
RUN mkdir /var/log/pgpool
RUN mkdir /var/run/pgpool && \
cp /usr/local/etc/pgpool.conf.sample    /usr/local/etc/pgpool.conf && \
cp /usr/local/etc/pcp.conf.sample    /usr/local/etc/pcp.conf && \
cp    /usr/local/etc/pool_hba.conf.sample /usr/local/etc/pool_hba.conf &&    \
echo ${PCP_USER}:`pg_md5    ${PCP_PASS}` >> /usr/local/etc/pcp.conf && \
cd /usr/local/etc && \
pg_md5 -m -u $PCP_USER $PCP_PASS
EXPOSE 9999 9898
CMD ["pgpool","-n", "-d", "-D"]

二、创建pgpool2的docker镜像

docker build -t pgpool2 .

三、创建一个容器卷（container3）

docker run -itd -v /devel/xwq/pgsql_data_dir/data --name dbstore ubuntu /bin/bash

四、编辑从库的recovery.conf，添加下面内容

trigger_file = '/devel/xwq/pgsql_data_dir/data/primary_down'

五、重启从库

docker run -d -p 54321:5432  --volumes-from dbstore  -v /devel/xwq/pgsql_data_dir/d14r1n18:/var/lib/postgresql/data/ --name postgres  7e309048b14d

六、启动pgpool

docker run -it --volumes-from dbstore --name pgpool -p 9999:9999  -d pgpool2

注：通过将dbstore这个容器的volume都mount到从库和pgpool2的容器里面，实现了在两个容器里面共享/dbdata此路径的目的。

七、进入pgpool2的容器，修改相关参数

docker exec -it pgpool2 /bin/bash
vi /usr/local/etc/pgpool.conf

修改内容如下：

shared-storage volumenum_init_children 300
master_slave_mode = on
master_slave_sub_mode = 'stream'
failover_command = 'touch /dbdata/primary_down'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
sr_check_user = 'repuser'
sr_check_password = 'repuser'
listen_addresses = '*'
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'host2'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/data1'
backend_flag1 = 'ALLOW_TO_FAILOVER'

vi pool_hba.conf
。。。。。。
生成pool_password：
pg_md5 --md5auth --username=postgres --prompt
mkdir /var/log/pgpool/
八、重启pgpool2

docker restart pgpool2

九、连接pgpool

psql -h 192.168.17.62 -p 9999  -U postgres
show pool_nodes;

参考连接
https://toutiao.io/posts/16cq10/preview