PostgresSQL 16 主从架构

PostgreSQL 是一个功能强大的开源关系型数据库系统，基于16.0版本和docker 环境做一个主从架构配置。

环境

postgres_master:
- ip: 172.19.0.3
- pgdata: ./pgdata_master
postgres_slave:
- ip: 172.19.0.2
- pgdata: ./pgdata_slave

# pg-test.yml
version: "3.8"
x-restart-policy:
    restart: always
services:
    postgres_master:
        container_name: postgres_master
        environment:
            POSTGRES_USER: postgres
            POSTGRES_PASSWORD: postgres
            POSTGRES_DB: test
        image: "postgres:16"
        ports:
            - "6432:5432"
        user: "1000:999"
        volumes:
            - ./pgdata_master:/var/lib/postgresql/data

    postgres_slave:
        container_name: postgres_slave
        environment:
            POSTGRES_USER: postgres
            POSTGRES_PASSWORD: postgres
            POSTGRES_DB: test
        image: "postgres:16"
        ports:
            - "7432:5432"
        user: "1000:999" # id -u  查看当前用户id
        volumes:
            - ./pgdata_slave:/var/lib/postgresql/data

# rm -rf pgdata_master pgdata_slave  &&  mkdir pgdata_master pgdata_slave 
# docker-compose -f pg-test.yml -p pg_test up -d
# docker-compose -f pg-test.yml -p pg_test down

FAQ:
- 需要手动建立文件夹，docker 自动建立的有权限问题： mkdir pgdata_master pgdata_slave
- 以当前用户身份启动docker： user: “1000:999” ： id -u （查看当前用户id为1000 postgresql 容器默认用户id为999 ）
- 查看启动状态：docker ps
- 查看docker容器ip： docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' postgres_master
- 执行sh: docker exec -it postgres_master /bin/bash
  - 执行sql：psql -U postgres

配置主库 Master

添加流复制专用角色、增强安全性

1 2	-- 新建用户: copy 密码： replica create user copy replication encrypted password 'replica';

修改身份认证(pg_hba.conf) 以允许从库访问主库

1
2
3

-- 查看配置所在目录 
SHOW data_directory
-- /var/lib/postgresql/data

# 已经映射出来可在主机直接编辑sudo vim ./pgdata_master/pg_hba.conf
# vim /var/lib/postgresql/data/pg_hba.conf

#允许 172.19.0.2 访问
host    all         copy     172.19.0.2/24            md5
host    replication copy     172.19.0.2/24            md5

修改主库设置

#已经将/var/lib/postgresql/data映射出来可在主机直接编辑： sudo  vim ./pgdata_master/postgresql.conf
# vim /var/lib/postgresql/data/postgresql.conf

listen_addresses ='*'    # 允许远程访问
wal_level = replica      # 记录足够的wal信息以支持流复制
max_wal_senders = 10     # 同步最大的进程数量
wal_sender_timeout = 60s # 流复制主机发送数据的超时时间

# 在docker的bash 里没有文本编辑器 可以使用echo 追加
# echo "wal_level = replica" >> /var/lib/postgresql/data/postgresql.conf

执行sql重新加载配置

1
2
3

SELECT pg_reload_conf();

-- t 成功

配置从库

备份初始主库信息到从库

# docker exec -it postgres_slave /bin/bash  
pg_basebackup -D /var/lib/postgresql/data/main -h postgres_master -p 5432 -U copy -Xs -P -R

# 提示failed: FATAL:  no pg_hba.conf entry for replication connection from host 则为主库pg_hba.conf 配置不正确

# 提示 "/var/lib/postgresql/data" exists but is not empty 可以换个目录或删除这个目录
# docker 启动的 应该没法删除， 可以改为 /var/lib/postgresql/data/main 停止docker 服务后在主机./pgdata_slave 目录把其他文件删除，把main里的移动到外面

检查是否有特殊文件standby.signal，如果没有可手动创建

1	touch standby.signal

配置从库配置

#sudo  vim ./postgres_slave/postgresql.conf
#  /var/lib/postgresql/data/postgresql.conf

primary_conninfo = 'host=postgres_master port=5432 user=copy password=replica'	 #host 为主机ip地址
recovery_target_timeline = 'latest' # 流复制同步到最新的数据
hot_standby = on                    # 开启热备 (从库支持select )
max_standby_streaming_delay = 30s   # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s   # 从节点向主节点报告自身状态的最长间隔时间

重新启动从库: docker start postgres_slave
检查是否成功：

-- 在主库查看 是否有从库接入
select * from pg_stat_replication;

-- client_addr 有从库ip即为成功

1 2	-- 在从库查看接收状态 SELECT * FROM pg_stat_wal_receiver;

在主库建表，插入数据，可以查看从库是否同步

从库切换为主库

验证主从配置状态: 查看同步状态，为0则没有延迟

SELECT CASE 
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() 
    THEN 0 
    ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) 
END AS log_delay;

切换从数库为主库

1	SELECT pg_promote();

检查是否切换成功

1 2	SELECT pg_is_in_recovery(); -- t 仍是从库 f是主库

扩展

更完整的高可用方案可使用Pigsty

ZhangHeng's Blog

PostgresSQL 16 主从架构

环境

配置主库 Master

配置从库

从库切换为主库

扩展

参考