dockercompose构建postgresql12主从-每日运维

postgresql的同步流复制，我们就简单叫主库和备库来表示两个不同的角色，我将分享主从的搭建过程

为了减少在安装上的繁琐流程，我将使用docker镜像，使用docker-compose编排

Docker-Compose version： v2.16.0
image: registry.cn-zhangjiakou.aliyuncs.com/marksugar-k8s/postgres:12.14-alpine3.17

因此，需要提前安装docker和docker-compose，镜像使用的是官方镜像，只是被搬到阿里而已

ID	IP	角色	配置
1	172.168.204.41	master	4c8g
2	172.168.204.42	slave	4c8g

启动的部分命令如下

wal_level = replica  # 这个是设置主为wal的主机
max_wal_senders = 5  # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 128 # 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s # 设置流复制主机发送数据的超时时间
max_connections = 200 # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on   # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on # also do full page writes of non-critical updates

这段摘自其他网页

master

使用docker-compose后，就需要在Command种使用-c指定即可，如下

version: '3.3'
services:
  postgresql12-m:
    container_name: postgresql12-m
    image: registry.cn-zhangjiakou.aliyuncs.com/marksugar-k8s/postgres:12.14-alpine3.17
    restart: always
    environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=mysecretpassword
    - PGDATA=/var/lib/postgresql/data/pgdata
    command: "postgres -c wal_level=replica -c max_wal_senders=15 -c wal_keep_segments=128 -c wal_sender_timeout=60s -c max_connections=200 -c hot_standby=on -c max_standby_streaming_delay=30s -c wal_receiver_status_interval=10s -c hot_standby_feedback=on -c wal_log_hints=on"
    volumes:
    - /etc/localtime:/etc/localtime:ro  # 时区2
    - /data/postgresql12:/var/lib/postgresql/data
    - /data/postgresql12/pg_hba.conf:/var/lib/postgresql/data/pgdata/pg_hba.conf
    logging:
      driver: "json-file"
      options:
        max-size: "50M"
    ports:
      - 8080:8080
      - 5432:5432

而后启动，命令如下

docker-compose -f docker-compose.yaml up -d

进入容器，创建用户

create role replica with replication login password '123456';
alter user replica with password '123456';
或者
CREATE USER replica WITH REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD '123456';

如下

docker exec --user=postgres -it postgresql12-m bash
2860b7926327:/$ psql
psql (12.14)
Type "help" for help.
postgres=# create role replica with replication login password '123456';
CREATE ROLE
postgres=# alter user replica with password '123456';
ALTER ROLE

启动完成后，我们需要删除原有自动生成的配置文件在替换后启动才能生效

postgres的配置文件是自动生成的，只有在容器生成在替换才可以被替换

因此，修改pg_hba.conf 的内容如下

docker rm -f postgresql12-m
rm -f /data/postgresql12/pgdata/pg_hba.conf
cat > /data/postgresql12/pgdata/pg_hba.conf  > $PG_FILE
echo "synchronous_commit = 'remote_write'" >> $PG_FILE

最后启动在重新启动

docker-compose -f docker-compose.yaml up -d

如果有必要，需要关闭防火墙或者配置放行5432端口

slave

从节点的command命令和master一致即可，修改名称添加-s以便于区分

version: '3.3'
services:
  postgresql12-s:
    container_name: postgresql12-s
    image: registry.cn-zhangjiakou.aliyuncs.com/marksugar-k8s/postgres:12.14-alpine3.17
    restart: always
    environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=mysecretpassword
    - PGDATA=/var/lib/postgresql/data/pgdata
    command: "postgres -c wal_level=replica -c max_wal_senders=15 -c wal_keep_segments=128 -c wal_sender_timeout=60s -c max_connections=200 -c hot_standby=on -c max_standby_streaming_delay=30s -c wal_receiver_status_interval=10s -c hot_standby_feedback=on -c wal_log_hints=on"
    volumes:
    - /etc/localtime:/etc/localtime:ro  # 时区2
    - /data/postgresql12:/var/lib/postgresql/data
    logging:
      driver: "json-file"
      options:
        max-size: "50M"
    ports:
      - 8080:8080
      - 5432:5432

启动数据库

docker-compose -f docker-compose.yaml up -d

备份数据

备份数据，可以通过pg_start_backup和pg_basebackup，这里我们使用pg_basebackup

1,pg_basebackup

镜像内本身带有pg_basebackup，因此，我们在从节点，进入容器内，执行如下命令

pg_basebackup -h 172.168.204.41 -p 5432 -U replica  -Fp -Xs -Pv -R -D /var/lib/postgresql/data/pgdata-latest

上述命令远程备份到当前的/var/lib/postgresql/data/pgdata-latest目录下

也是在容器的挂载路径内，先存放在pgdata-latest，而后在切换目录即可

[root@node2 pgdata-m]# docker exec -it  postgresql12-s bash
142531a6f29e:/# pg_basebackup -h 172.168.204.41 -p 5432 -U replica  -Fp -Xs -Pv -R -D /var/lib/postgresql/data/pgdata-latest
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_76"
24656/24656 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/6000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

此时备份的数据目录是在/var/lib/postgresql/data/pgdata-latest

跳过pg_start_backup

如果使用了pg_backup，现在可以跳过次方式备份。

如果不能使用pg_backup，就在主节点使用pg_start_backup后进行复制目录

docker exec -it --user=postgres postgresql12-m bash2
8e481427e025:/$ psql -U postgres
psql (12.14)
Type "help" for help.

postgres=# select pg_start_backup('$DATE',true);
 pg_start_backup
-----------------
 0/2000028
(1 row)

使用pg_start_backup这个方法后，所有请求在写日志之后不会再刷新到磁盘。直到执行pg_stop_backup()这个函数。

拷贝一份data目录，并通过scp复制到子数据库中

cp -r /data/postgresql/pgdata ./pgdata-m
tar  -zcf pgdata-m.tar.gz pgdata-m
scp -r ./pgdata-m.tar.gz  172.168.204.42:~/

复制完成停止

postgres=# select pg_stop_backup();
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
 pg_stop_backup
----------------
 0/2000138
(1 row)

回到从节点，删除容器，解压从主拿来的数据

[root@node2 postgres]# docker rm -f postgresql12-s
postgresql12-s

[root@node2 ~]# tar xf pgdata-m.tar.gz -C /data/postgresql12/
[root@node2 ~]# ll /data/postgresql12/
total 8
drwx------  2   70 root   35 Mar  6 17:31 pgdata
drwx------ 19 root root 4096 Mar  6 17:33 pgdata-m
-rw-r--r--  1   70 root  113 Mar  6 17:25 pg_hba.conf

2,修改数据目录

此时备份的数据目录是在/var/lib/postgresql/data/pgdata-latest，修改docker-compose映射的环境变量关系，指向备份好的目录

- PGDATA=/var/lib/postgresql/data/pgdata-latest

在posgress12种，需要创建文件standby.signal来声明自己是从，并且standby.signal本身也优先于其他

创建文件即可，你也可以写点其他信息。这里我们为了怀念老版本，追加standby_mode = 'on'

echo "standby_mode = 'on'" > /data/postgresql12/pgdata-latest/standby.signal

此时我们还需要检查从节点的配置。从节点的postgresql.auto.conf文件内的属性是否和预期一致，之所以是postgresql.auto.conf，只是因为postgresql.auto.conf优先于postgresql.conf被读取

pg_basebackup -R会修改postgresql.auto.conf的授权的权限信息，一旦使用了pg_basebackup ，就需要重新修改

此时我的postgresql.auto.conf和postgresql.conf都添加如下配置：

其中包含了授权的账号信息

hot_standby = 'on'
primary_conninfo = 'application_name=standbydb1 user=replica password=123456 host=172.168.204.41 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

如果修改的是文件，而这样的修改需要重启启动，也可以通过命令行进行配置，如下：

show primary_conninfo # 查看
alter system set primary_conninfo = 'application_name=standbydb1 user=replica password=123456 host=172.168.204.41 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any';

而后启动从库

docker-compose -f docker-compose.yaml up -d

验证主从

回到主库查看

linuxea=#  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 | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
-----+----------+---------+------------------+----------------+-----------------+-------------+------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 144 |    16384 | replica | standbydb1       | 172.168.204.42 |                 |       47492 | 2023-03-07 10:18:45.56999+08 |          502 | streaming | 0/F3449D8 | 0/F3449D8 | 0/F3449D8 | 0/F3449D8  |           |           |            |             1 | sync       | 2023-03-07 10:25:38.354315+08
(1 row)

linuxea=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
 pid |   state   |  client_addr   | sync_priority | sync_state
-----+-----------+----------------+---------------+------------
 207 | streaming | 172.168.204.42 |             1 | sync
(1 row)

linuxea=#  select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

在主库创建数据库，并插入数据

CREATE DATABASE linuxea OWNER postgres;
\c linuxea
CREATE TABLE test(  id integer,  test integer)WITH (OIDS=FALSE);
ALTER TABLE test  OWNER TO postgres;

如下

postgres=# \c linuxea
You are now connected to database "linuxea" as user "postgres".
linuxea=# CREATE TABLE test(  id integer,  test integer)WITH (OIDS=FALSE);
CREATE TABLE
linuxea=# ALTER TABLE test  OWNER TO postgres;
ALTER TABLE
linuxea=# insert into test SELECT generate_series(1,1000000) as key, (random()*(10^3))::integer;
INSERT 0 1000000

来到从库

[root@node2 pgdata-m]# docker exec -it --user=postgres postgresql12-s bash
da91ac9e2a19:/$ psql -U postgres
psql (12.14)
Type "help" for help.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# \c linuxea
You are now connected to database "linuxea" as user "postgres".
linuxea=# SELECT * FROM test;
   id    | test
---------+------
       1 |  935
       2 |  652
       3 |  204
       4 |  367
       5 |  100
       6 |  743
--More--

从切主

我们假设主挂掉了，一时半会好不了，就简单的将从切换到主，提供服务即可

开始之前，我们直接关闭主库模拟主库不可用，而后进入从库的容器，使用 pg_ctl promote -D

c683e39637ea:/$  pg_ctl promote -D /var/lib/postgresql/data/pgdata-latest
waiting for server to promote.... done
server promoted

将数据写入42，测试数据写入是否正常

CREATE DATABASE linuxea2 OWNER postgres;
\c linuxea2
CREATE TABLE test(  id integer,  test integer)WITH (OIDS=FALSE);
ALTER TABLE test  OWNER TO postgres;
insert into test SELECT generate_series(1,1000000) as key, (random()*(10^3))::integer;
SELECT * FROM test;

现在从库已经可以写入数据了

配置从库

此时主库起来了，如果代理已经将请求改到42上了，我们直接在41的主节点上，同步42数据，将41配置为从库

1.备份

[root@node1 pgdata]# docker-compose -f ~/postgresql/docker-compose.yaml up -d
[+] Running 1/1
 ⠿ Container postgresql12-m  Started                                                      0.4s
[root@node1 pgdata]# docker exec -it postgresql12-m bash
d7108d41f908:/# pg_basebackup -h 172.168.204.42 -p 5432 -U replica  -Fp -Xs -Pv -R -D /var/lib/postgresql/data/pgdata-latest
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/14000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_100"
147294/147294 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/14000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

修改数据目录

- PGDATA=/var/lib/postgresql/data/pgdata-latest

创建文件

echo "standby_mode = 'on'" > /data/postgresql12/pgdata-latest/standby.signal

postgresql.auto.conf和postgresql.conf都添加如下配置：

hot_standby = 'on'
primary_conninfo = 'application_name=standbydb1 user=replica password=123456 host=172.168.204.42 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

删除容器后，重新启动

docker-compose -f ~/postgresql/docker-compose.yaml down
docker-compose -f ~/postgresql/docker-compose.yaml up -d

6.验证

回到主库42

CREATE DATABASE linuxea23 OWNER postgres;
\c linuxea23
CREATE TABLE test(  id integer,  test integer)WITH (OIDS=FALSE);
ALTER TABLE test  OWNER TO postgres;
insert into test SELECT generate_series(1,1000000) as key, (random()*(10^3))::integer;

而后到从库41查看

\c linuxea23
SELECT * FROM test;

如下

[root@node1 postgresql12]# docker exec -it --user=postgres postgresql12-m bash
06a1e5ecc51b:/$ psql -U postgres
psql (12.14)
Type "help" for help.

postgres=# \c linuxea23
You are now connected to database "linuxea23" as user "postgres".
linuxea23=# SELECT * FROM test;
   id    | test
---------+------
       1 |  785
       2 |  654
       3 |  881
       4 |   19
       5 |   37
       6 |  482
       7 |  938
       8 |   25
       9 |  209
      10 |  820
      11 |  445
      12 |  238
      13 |  772
      14 |  233
      15 |  158
      16 |  964
      17 |  815
      18 |  890
      19 |  977
      20 |  437
      21 |   56
      22 |  241
      23 |  266
      24 |  123
      25 |  139
      26 |  207
      27 |   90
      28 |    4
      29 |   95
      30 |  896
      31 |  698
      32 |  752
      33 |  972
--More--

参考

PostgreSQL12恢复配置总结

dockercompose构建postgresql12主从

master

slave

备份数据

1,pg_basebackup

跳过pg_start_backup

2,修改数据目录

验证主从

从切主

配置从库

参考

相关文章

发布评论取消回复

大白菜程序猿

服务器端口转发，带你了解服务器端口转发

百度搜索：蓝易云 – Linux之查看系统性能命令大全

MySQL 主从复制的问题及解决方案

dockercompose构建postgresql12主从

master

slave

备份数据

1,pg_basebackup

跳过pg_start_backup

2,修改数据目录

验证主从

从切主

配置从库

参考

相关文章

发布评论 取消回复

大白菜程序猿

服务器端口转发，带你了解服务器端口转发

百度搜索：蓝易云 – Linux之查看系统性能命令大全

MySQL 主从复制的问题及解决方案

发布评论取消回复