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

设置步骤:

  1. 设置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)
  1. 设置 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]                                              
  1. 启动slave
root@debian:~# docker start pgslave

pgslave

验证:

  1. 连接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)
  1. 在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

posted @ 2016-01-27 21:37  cxy486  阅读(3814)  评论(1编辑  收藏  举报