linuxea: docker-compose构建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，镜像使用的是官方镜像，只是被搬到阿里而已IDIP角色配置1172.168.204.41master4c8g2172.168.204.42slave4c8g启动的部分命令如下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 << EOFFF local all all trust host all all 0.0.0.0/0 md5 host all all ::1/128 trust host replication replica 0.0.0.0/0 md5 EOFFF除此之外，我们还需要配置流复制的必要配置，直接添加到/data/postgresql12/pgdata/postgresql.conf文件内PG_FILE=/data/postgresql12/pgdata/postgresql.conf echo "synchronous_standby_names = 'standbydb1' #同步流复制才配置该值" >> $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_basebackup1,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.conf2,修改数据目录此时备份的数据目录是在/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 -Dc683e39637ea:/$ 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.signalpostgresql.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 -d6.验证回到主库42CREATE 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恢复配置总结