docker 部署postgeres主从环境

1. 创建测试网络
创建一个 docker bridge 网络用于测试：

 创建测试网络
docker network create --subnet=172.18.0.0/24 dockernetwork

 查看网络
docker network ls

规划主从库IP端口如下：

主库：172.18.0.101:5432

从库：172.18.0.102:5433

2. 拉取 postgres 镜像
docker pull postgres
3. 创建数据目录
mkdir -p /data/psql/pgsmaster
mkdir -p /data/psql/pgsslave
mkdir -p /data/psql/pgsrepl
chown 999:999 /data/psql/pgsmaster
chown 999:999 /data/psql/pgsslave
chown 999:999 /data/psql/pgsrepl
4. 运行 master 容器
docker run -d \
--network dockernetwork --ip 172.18.0.101 -p 5432:5432 \
--name master -h master \
-e "POSTGRES_DB=postgres" \
-e "POSTGRES_USER=postgres" \
-e "POSTGRES_PASSWORD=postgres" \
-v /data/psql/pgmaster:/var/lib/postgresql/data \
postgres
查看容器：

docker ps -a 

NAMES IMAGE CREATED STATUS NETWORKS PORTS
master postgres 48 seconds ago Up 46 seconds dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
5. 创建主从流复制专用账号
# 1. 进入容器
docker exec -it master bash

# 2. 连接PostgreSQL
psql -U postgres

# 3. 创建用户规则
CREATE ROLE repuser WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456';
# 用户名 repuser；最大链接数：5；密码：123456

# 4. 查看规则
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repuser | Replication +| {}
| 5 connections |
6. 修改 master 配置文件
# 1. 进入 master 文件夹
cd /data/psql/pgmaster

# 2. 在末尾增加规则
echo "host replication repuser 172.18.0.102/24 md5" >> pg_hba.conf
修改 postgresql.conf 配置文件，找到以下几行，取消注释修改配置：

archive_mode = on # 开启归档模式
archive_command = '/bin/date' # 设置归档行为
# 从机连接到主机的并发连接数之总和
max_wal_senders = 10
# 指定在后备服务器需要为流复制获取日志段文件的情况下, pg_wal目录下所能保留的过去日志文件段的最小尺寸
wal_keep_size = 16
# 指定一个支持同步复制的后备服务器的列表
synchronous_standby_names = '*'


7. 重启 master 容器
docker stop master
docker start master
8. 创建 slave 容器
docker run -d \
--network dockernetwork --ip 172.18.0.102 -p 5433:5432 \
--name slave -h slave \
-e "POSTGRES_DB=postgres" \
-e "POSTGRES_USER=postgres" \
-e "POSTGRES_PASSWORD=postgres" \
-v /data/psql/pgslave:/var/lib/postgresql/data \
-v /data/psql/pgrepl:/var/lib/postgresql/repl \
postgres
# 查看容器
docker ps -a

NAMES IMAGE CREATED STATUS NETWORKS PORTS
slave postgres 18 seconds ago Up 15 seconds dockernetwork 0.0.0.0:5433->5432/tcp, :::5433->5432/tcp
master postgres 2 hours ago Up 2 hours dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
9. 同步数据
# 1. 进入容器
docker exec -it -u postgres slave /bin/bash

# 2. 备份主机数据到 repl 文件夹，此处输入在上面设置的密码：123456

从pg12版本以后pg的主从配置只支持pg_basebackup
pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 172.18.0.101 -p 5432 -U repuser

# 3. 备份完成退出容器
exit
10. 重建 slave 容器
通过上一步的初始备份，现在可以使用 /data/psql/pgrepl 里的数据重建 pgslave容器了。首先删除psslave目录，然后将 pgrepl 目录改为 pgslave，这个目录就是从库的数据目录了：

# 1. 删除容器
docker rm -f pgslave
# 2. 删除原有文件夹，将 pgrepl 重命名为 pgslave
cd /data/psql/
rm -rf pgslave
mv pgrepl psslave
cd /data/pgql/psslave
# 3. 查看配置信息
# postgresql.auto.conf 将含有复制所需信息
cat postgresql.auto.conf

primary_conninfo = 'user=repuser password=123456 channel_binding=prefer host=172.18.0.101 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
重建 slave 容器：

docker run -d \
--network dockernetwork --ip 172.18.0.102 -p 5433:5432 \
--name slave -h slave \
-e "POSTGRES_DB=postgres" \
-e "POSTGRES_USER=postgres" \
-e "POSTGRES_PASSWORD=postgres" \
-v /data/psql/pgslave:/var/lib/postgresql/data \
postgres
# 查看容器
docker ps -a

NAMES IMAGE CREATED STATUS NETWORKS PORTS
slave postgres 23 seconds ago Up 21 seconds dockernetwork 0.0.0.0:5433->5432/tcp, :::5433->5432/tcp
master postgres 2 hours ago Up 2 hours dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
11. 查看主从复制信息
ps -aux | grep postgres

主库进程：从库进程：

postgres: walreceiver streaming 0/5000148
systemd+ 20020 0.0 0.0 213936 4180 ? Ss 13:36 0:00 postgres: walsender repuser 172.18.0.102(52938) streaming 0/5000148

验证主从配置
主机生成数据
# 进入 master 容器，切换到postgres用户
docker exec -it master bash
psql -U postgres
-- 查询复制信息
select * from pg_stat_replication;
-- 创建测试数据库
CREATE DATABASE test;
-- 查看所有数据库
\list
-- 切换数据库
\c test
-- 创建测试表
CREATE TABLE test (
"id" int4 NOT NULL,
"value" varchar(255),
PRIMARY KEY ("id")
);
-- 查看创建的表
\dt


-- 向表中插入十条数据
insert into test select generate_series(1,10);

-- 查看所有数据
select * from test;

从机查看数据
# 进入从机容器
docker exec -it slave bash
psql -U postgres
-- 查看数据库
\d
-- 查看表
\c test
-- 查看所有数据
select * from test;
参考链接：https://python.iitter.com/other/102038.html

所有操作步骤如下：

[root@sunny data]# docker network create --subnet=172.18.0.0/24 dockernetwork
4ddef2b0fe7db6200429977cf6f640fa9fdfb34f6e5a477652465533daec4728
[root@sunny data]# docker ps -a
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS    PORTS     NAMES
863b930c08c3   postgres   "docker-entrypoint.s…"   4 minutes ago   Created             master
[root@sunny data]# docker rm master
master
[root@sunny data]# docker run -d \
> --network dockernetwork --ip 172.18.0.101 -p 5432:5432 \
> --name master -h master \
> -e "POSTGRES_DB=postgres" \
> -e "POSTGRES_USER=postgres" \
> -e "POSTGRES_PASSWORD=postgres" \
> -v /data/psql/pgsmaster:/var/lib/postgresql/data  postgres
51eb3d511fd3e26f1f9125df6c2035003390503994327025db58c372ff7aa8a5
[root@sunny data]# docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}}"
NAMES     IMAGE      CREATED          STATUS          NETWORKS        PORTS
master    postgres   54 seconds ago   Up 54 seconds   dockernetwork   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
[root@sunny data]# docker exec -it master bash
root@master:/# psql -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# CREATE ROLE repuser WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repuser   | Replication                                               +| {}
           | 5 connections                                              |

postgres=# \q
root@master:/# exit
exit
[root@sunny data]# cd /data/psql/pgsmaster
[root@sunny pgsmaster]# ls
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid
[root@sunny pgsmaster]# echo "host replication repuser 172.18.0.102/24 md5" >> pg_hba.conf
[root@sunny pgsmaster]# vi postgresql.conf
[root@sunny pgsmaster]# docker exec -it -u postgres master pg_ctl stop
waiting for server to shut down....[root@sunny pgsmaster]#
[root@sunny pgsmaster]# docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
[root@sunny pgsmaster]# docker start master
master
[root@sunny pgsmaster]# docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS                                       NAMES
51eb3d511fd3   postgres   "docker-entrypoint.s…"   7 minutes ago   Up 3 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   master
[root@sunny pgsmaster]# docker run -d \
> --network dockernetwork --ip 172.18.0.102 -p 5433:5432 \
> --name slave -h slave \
> -e "POSTGRES_DB=postgres" \
> -e "POSTGRES_USER=postgres" \
> -e "POSTGRES_PASSWORD=postgres" \
> -v /data/psql/psslave:/var/lib/postgresql/data \
> -v /data/psql/psrepl:/var/lib/postgresql/repl \
> postgres
3ddc6f3967b4a3cee4ca9d71c97d5b1c697d7e63603dca64eaa0ca75eb5ac317
[root@sunny pgsmaster]# docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}}"
NAMES     IMAGE      CREATED              STATUS              NETWORKS        PORTS
slave     postgres   About a minute ago   Up About a minute   dockernetwork   0.0.0.0:5433->5432/tcp, :::5433->5432/tcp
master    postgres   9 minutes ago        Up About a minute   dockernetwork   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
[root@sunny pgsmaster]#

[root@sunny pgsmaster]# docker exec -it -u postgres slave /bin/bash

postgres@slave:~/repl$ pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 172.18.0.101 -p 5432 -U repuser
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/4000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_148"
26288/26288 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/4000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@slave:~/repl$
[root@sunny psslave]# docker rm slave
slave
[root@sunny psslave]# docker run -d \
> --network dockernetwork --ip 172.18.0.102 -p 5433:5432 \
> --name slave -h slave \
> -e "POSTGRES_DB=postgres" \
> -e "POSTGRES_USER=postgres" \
> -e "POSTGRES_PASSWORD=postgres" \
> -v /data/psql/psslave:/var/lib/postgresql/data \
> postgres
c6f4973cb4657431f0ef301b9bce3b15ff0759d01f9fd7cb38d4483da9c05f24
[root@sunny psslave]# docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}
>
[root@sunny psslave]# docker ps -a
CONTAINER ID   IMAGE      COMMAND                  CREATED          STATUS          PORTS                                       NAMES
c6f4973cb465   postgres   "docker-entrypoint.s…"   27 seconds ago   Up 26 seconds   0.0.0.0:5433->5432/tcp, :::5433->5432/tcp   slave
51eb3d511fd3   postgres   "docker-entrypoint.s…"   2 hours ago      Up 2 hours      0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   master
[root@sunny psslave]# ps -aux | grep postgres
root      1613  0.0  0.0   4232   292 ?        Ss    2021   0:00 runsv postgresql
root      1632  0.0  0.0   4232   288 ?        Ss    2021   0:00 runsv postgres-exporter
root      1639  0.0  0.0   4376   536 ?        S     2021   0:02 svlogd -tt /var/log/gitlab/postgresql
root      1641  0.0  0.0   4376   484 ?        S     2021   0:00 svlogd -tt /var/log/gitlab/postgres-exporter
systemd+  6794  0.0  0.2 213264 19404 ?        Ss   11:43   0:00 postgres
systemd+  6874  0.0  0.0 213384  4032 ?        Ss   11:43   0:00 postgres: checkpointer
systemd+  6875  0.0  0.0 213264  3732 ?        Ss   11:43   0:00 postgres: background writer
systemd+  6876  0.0  0.0 213264  6624 ?        Ss   11:43   0:00 postgres: walwriter
systemd+  6877  0.0  0.0 213800  3440 ?        Ss   11:43   0:00 postgres: autovacuum launcher
systemd+  6878  0.0  0.0 213264  2608 ?        Ss   11:43   0:00 postgres: archiver last was 000000010000000000000004.00000028.backup
systemd+  6879  0.0  0.0  67856  2488 ?        Ss   11:43   0:00 postgres: stats collector
systemd+  6880  0.0  0.0 213700  2928 ?        Ss   11:43   0:00 postgres: logical replication launcher
systemd+ 19932  0.1  0.2 213264 19300 ?        Ss   13:36   0:00 postgres
systemd+ 20015  0.0  0.0 213400  2924 ?        Ss   13:36   0:00 postgres: startup recovering 000000010000000000000005
systemd+ 20016  0.0  0.0 213264  2440 ?        Ss   13:36   0:00 postgres: checkpointer
systemd+ 20017  0.0  0.0 213264  2972 ?        Ss   13:36   0:00 postgres: background writer
systemd+ 20018  0.0  0.0  67856  2300 ?        Ss   13:36   0:00 postgres: stats collector
systemd+ 20019  0.1  0.0 213660  4776 ?        Ss   13:36   0:00 postgres: walreceiver streaming 0/5000148
systemd+ 20020  0.0  0.0 213936  4180 ?        Ss   13:36   0:00 postgres: walsender repuser 172.18.0.102(52938) streaming 0/5000148
root     20145  0.0  0.0 112828   984 pts/1    S+   13:37   0:00 grep --color=auto postgres
[root@sunny psslave]#
[root@sunny psslave]# docker exec -it master bash
root@master:/# psql -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# select * from pg_stat_replication;
 pid | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | writ
e_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
-----+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----
------+-----------+------------+---------------+------------+-------------------------------
 167 |    16384 | repuser | walreceiver      | 172.18.0.102 |                 |       52938 | 2022-03-11 05:36:35.269344+00 |              | streaming | 0/5000148 | 0/5000148 | 0/5000148 | 0/5000148  |
      |           |            |             1 | sync       | 2022-03-11 05:39:45.781351+00
(1 row)

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \list
                                 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=# CREATE TABLE test (
  "id" int4 NOT NULL,
  "value" varchar(255),
  PRIMARY KEY ("id")
);
CREATE TABLE
test=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# insert into test select generate_series(1,10);
INSERT 0 10
test=# select * from test;
 id | value
----+-------
  1 |
  2 |
  3 |
  4 |
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(10 rows)

test=#
查看从库
[root@sunny psql]# docker exec -it slave bash
root@slave:/# psql -U postgres
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# \list
                                 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=# \dt
Did not find any relations.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

test=#
test=# select * from test;
 id | value
----+-------
  1 |
  2 |
  3 |
  4 |
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(10 rows)

test=#