PostgreSQL 使用Docker搭建流复制测试环境
环境准备：
利用Docker启动2个实例
root@debian:~# docker run --name pgmaster -p 6000:5432 -e POSTGRES_PASSWORD=postgres -d postgres 745b5a1b221e3c4d14ae88da3d2cc10487aef845eaf54439b47ab4fd3d8ffa28 root@debian:~# docker run --name pgslave -p 6001:5432 -e POSTGRES_PASSWORD=postgres -d postgres 5f713eb0257492697cd67aceb8fb4e06a293f34561d7362c87d38edc000fb74e root@debian:~# docker inspect pgmaster|grep IPAddress "SecondaryIPAddresses": null, "IPAddress": "172.17.0.2", "IPAddress": "172.17.0.2", root@debian:~# docker inspect pgslave|grep IPAddress "SecondaryIPAddresses": null, "IPAddress": "172.17.0.3", "IPAddress": "172.17.0.3", root@debian:~# docker stop pgslave pgslave
设置步骤：
- 设置master：
-
- 设置复制账号
root@debian:~# psql -hlocalhost -p6000 -Upostgres Password for user postgres: psql (9.5.0) Type "help" for help. postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'postgres' LOGIN; CREATE ROLE postgres=# \q
-
- cd至master容器对应的宿主机上的data文件夹中
root@debian:~# docker inspect pgmaster|grep Source "Source": "/var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data", root@debian:~# cd /var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data root@debian:/var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data#
-
- 编辑pg_hba.conf以允许slave连接
加入 host replication replication 172.17.0.3/32 md5 [1]
-
- 编辑配置文件postgresql.conf
设置如下 wal_level = hot_standby max_wal_senders = 3 [2]
-
- 重启服务，直接重启该容器
root@debian:~# docker restart pgmaster
pgmaster
-
- 同步数据文件到slave
新建一个exclude.txt 文件，放入不要同步的 文件名 [3] root@debian:~# vim /tmp/exclude.txt 写入内容 pg_hba.conf pg_ident.conf postgresql.conf postmaster.pid
同样获取slave的data文件夹 root@debian:~# docker inspect pgslave|grep Source "Source": "/var/lib/docker/volumes/6d0e60a38cc300793dccdf35df5bd6660849847aa4236166d5c09bccbdf26019/_data"
同步master和slave的data文件夹 root@debian:~# psql -hlocalhost -p6000 -Upostgres -c "SELECT pg_start_backup('base', true)" Password for user postgres: pg_start_backup ----------------- 0/2000028 (1 row) root@debian:~# rsync -ac /var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data/ /var/lib/docker/volumes/6d0e60a38cc300793dccdf35df5bd6660849847aa4236166d5c09bccbdf26019/_data/ --exclude-from=/tmp/exclude.txt root@debian:~# psql -hlocalhost -p6000 -Upostgres -c "SELECT pg_stop_backup()" Password for user postgres: NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup [4] pg_stop_backup ---------------- 0/2000130 (1 row)
- 设置 slave
编辑postgresql.conf hot_standby = on 新建recovery.conf并写入 standby_mode = 'on' primary_conninfo = 'host=172.17.0.2 port=5432 user=replication password=postgres application_name=172.17.0.3' #trigger_file = '/path_to/trigger' [5]
- 启动slave
root@debian:~# docker start pgslave
pgslave
验证：
- 连接master，查看状态
root@debian:~# psql -hlocalhost -p6000 -Upostgres Password for user postgres: psql (9.5.0) Type "help" for help. postgres=# select client_addr,sent_location, write_location, flush_location, replay_location, sync_state from pg_stat_replication; client_addr | sent_location | write_location | flush_location | replay_location | sync_state -------------+---------------+----------------+----------------+-----------------+------------ 172.17.0.3 | 0/5000060 | 0/5000060 | 0/5000060 | 0/5000060 | async (1 row)
- 在master建立对象，登录slave查看
postgres=# CREATE DATABASE test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# CREATE TABLE test(a int); CREATE TABLE test=# INSERT INTO test(a) VALUES(100); INSERT 0 1 test=# \q root@debian:~# psql -hlocalhost -p6001 -Upostgres Password for user postgres: psql (9.5.0) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.utf8 | en_US.utf8 | (4 rows) postgres=# \c test You are now connected to database "test" as user "postgres". test=# SELECT * FROM test; a ----- 100 (1 row)
升级为同步复制：
root@debian:~# docker inspect pgmaster|grep Source "Source": "/var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data", root@debian:~# vim /var/lib/docker/volumes/b2892403b622c733f771520eaff33055c77e1621f525dd038ea4610622454749/_data/postgresql.conf
修改为
synchronous_standby_names = '172.17.0.3'
再次验证，已经从异步升级为同步
root@debian:~# psql -hlocalhost -p6000 -Upostgres Password for user postgres: psql (9.5.0) Type "help" for help. postgres=# select client_addr,sent_location, write_location, flush_location, replay_location, sync_state from pg_stat_replication; client_addr | sent_location | write_location | flush_location | replay_location | sync_state -------------+---------------+----------------+----------------+-----------------+------------ 172.17.0.3 | 0/50162E0 | 0/50162E0 | 0/50162E0 | 0/50162E0 | sync (1 row)
说明：
[1] 官方镜像默认的pg_hba.conf安全性比较差，没有访问限制
[2]可以选择添加archive模式，以规避日志切换频繁而丢失文件的潜在风险;max_wal_senders可以大一点，以支持更多slave
[3]官方镜像将配置文件与数据文件放在了一起，配置 文件不需要同步
[4]既[2]中风险
[5]建立该文件即failover
