PostgreSQL 使用Docker搭建流复制测试环境

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

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

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#

加入

host    replication     replication      172.17.0.3/32           md5                        [1]

设置如下

wal_level = hot_standby

max_wal_senders = 3                                                                         [2]

root@debian:~# docker restart pgmaster

pgmaster

新建一个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)

编辑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]                                              

root@debian:~# docker start pgslave

pgslave

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)

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)