先展示一下当时设计的后台架构。
为了避免版本差异导致安装困难,也基于对版本没有特别的要求,以下搭建postgres版本号为:12.4。使用postgres:latest 最新版本也可以使用以下步骤来配置主从架构的搭建,再次亲测有效。
从dockerhub拉取postgre的12.4版本镜像,并在宿主机提前预备好*/pgsmaster2/
和*/pgsslave2/
挂载文件路径用于映射出容器内的数据库文件,同时映射容器与宿主机端口用于外界访问。执行命令后确保容器构建成功并处于运行状态,此一步骤即为完成,若未能处于运行状态请查看日志并排除失败原因。
# 拉取post12.4的docker 镜像
docker pull postgres:12.4
# 构建post docker主从两个容器
docker run --name pgsmaster2 -p 15500:5432 -e POSTGRES_PASSWORD=pgsmaster2 -v C:\Users\24092\Desktop\hy\test_postgresql/pgsmaster2:/var/lib/postgresql/data -d postgres:12.4
docker run --name pgsslave2 -p 15501:5432 -e POSTGRES_PASSWORD=pgsslave2 -v C:\Users\24092\Desktop\hy\test_postgresql/pgsslave2:/var/lib/postgresql/data -d postgres:12.4
# 查看是否成功构建容器
docker ps
# CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
# 71d82b661974 postgres:12.4 "docker-entrypoint.s…" 21 hours ago Up 3 hours 0.0.0.0:15501->5432/tcp, :::15501->5432/tcp pgsslave2
# d143431552e4 postgres:12.4 "docker-entrypoint.s…" 21 hours ago Up 3 hours 0.0.0.0:15500->5432/tcp, :::15500->5432/tcp pgsmaster2
上一步骤确保完成后可以开始进入容器内部,注:postgres构建容器默认三件事:1.为linux创建postgres用户;2.为postgres服务创建免密码postgres用户;3.创建postgres数据库。得知构建容器后自动完成的三件事后,我们开始创建同步专用账号、设置好数据库主从配置、使用pg_basebackup备份主数据库。
# postgres构建容器默认三件事:
# 为linux创建postgres用户、为postgres服务创建免密码postgres用户、创建postgres数据库
# 进入主docker
docker exec -it pgsmaster2 bash
# 进入交互界面
psql -U postgres
# 创建同步专用账户
CREATE ROLE replicator WITH REPLICATION PASSWORD 'postgres' LOGIN;
# 查看全局用户
postgres=# \du
# List of roles
# Role name | Attributes | Member of
# -------------+------------------------------------------------------------+-----------
# postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
# replicator | Replication | {}
# 创建账户对应的数据库
CREATE DATABASE replicator;
# 查看数据库
postgres=# \l
# List of databases
# Name | Owner | Encoding | Collate | Ctype | Access privileges
# -------------+----------+----------+------------+------------+-----------------------
# postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
# replicator | 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
# (4 rows)
# 配置主从
# 再宿主机中查看映射文件路径
docker inspect master | grep Source
# vim postgresql.conf 添加如下代码
wal_level = hot_standby #启动流复制
max_wal_senders = 3
# vim pg_hba.conf 添加如下代码 172.17.0.2/24 为主服务器确切IP地址
host replication replicator 172.17.0.2/24 md5
# 创建备份文件夹
mkdir -p /home/data/back
# pg_basebackup生成备份文件
pg_basebackup -h 192.168.50.200 -p 15500 -D /home/data/back -U replicator -P -v -R -X stream -C -S pgstandby1
# Password:
# pg_basebackup: initiating base backup, waiting for checkpoint to complete
# pg_basebackup: checkpoint completed
# pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
# pg_basebackup: starting background WAL receiver
# pg_basebackup: created replication slot "pgstandby1"
# 48422/48422 kB (100%), 1/1 tablespace
# pg_basebackup: write-ahead log end point: 0/2000138
# pg_basebackup: waiting for background process to finish streaming ...
# pg_basebackup: syncing data to disk ...
# pg_basebackup: base backup completed
# 在宿主机中拷贝到slave映射的位置,直接覆盖slave的配置
docker cp pgsmaster2:/home/data/back/ .
完成拷贝主服务器后覆盖从服务器的文件路径下,重启docker容器后查看是否主从服务的状态是否配置成功。如果成功可以测试创建数据库、数据表、插入记录来测试主从是否生效。
# 完成拷贝后重启主从俩容器,查看主服务器WAL发送器进程状态
psql -U postgres -c "\x" -c "SELECT * FROM pg_stat_replication;"
# Expanded display is on.
# -[ RECORD 1 ]----+------------------------------
# pid | 33
# usesysid | 16388
# usename | replicator
# application_name | walreceiver
# client_addr | 172.17.0.1
# client_hostname |
# client_port | 40880
# backend_start | 2022-02-09 02:33:33.373527+00
# backend_xmin |
# state | streaming # 进程状态
# sent_lsn | 0/C0541A8
# write_lsn | 0/C0541A8
# flush_lsn | 0/C0541A8
# replay_lsn | 0/C0541A8
# write_lag |
# flush_lag |
# replay_lag |
# sync_priority | 0
# sync_state | async # 同步状态
# reply_time | 2022-02-09 08:00:04.627528+00
# 查看从服务器WAL接收器进程状态
psql -U postgres -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"
# Expanded display is on.
# -[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# pid | 30
# status | streaming
# receive_start_lsn | 0/C000000
# receive_start_tli | 1
# received_lsn | 0/C0541A8
# received_tli | 1
# last_msg_send_time | 2022-02-09 08:04:25.368521+00
# last_msg_receipt_time | 2022-02-09 08:04:25.369901+00
# latest_end_lsn | 0/C0541A8
# latest_end_time | 2022-02-09 07:53:53.576081+00
# slot_name | pgstandby2
# sender_host | 192.168.50.200
# sender_port | 15500
# conninfo | user=replicator password=******** channel_binding=prefer dbname=replication host=192.168.50.200 port=15500 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
上面两个步骤如果都完成了现在就是需要被控制权限的账户,用于写主服务器都从服务器。创建完成后,使用此账号可以读写主服务器,且只能读从服务器。
# 创建账户与创建角色的区别在于CREATE USER自带login权限,CREATE ROLE想要登录需要另外赋予LOGIN权限
# 可以通过\h CREATE ROLE指令查看可设置的管理权限
# 创建用户
CREATE USER reader2 WITH PASSWORD '123456';
# 赋值replication数据库所有权限给reader2
GRANT ALL PRIVILEGES ON DATABASE replication TO reader2;
# 切换数据库,赋值表权限的前提
\c replication
# 赋值当前数据库所有表schema层public权限给reader2
GRANT ALL PRIVILEGES ON all tables in schema public TO reader2;
到此基于docker的postgresql版本12.4的主从架构已经搭建完成。