Docker PG流复制搭建实操

制作镜像

1. 删除旧的容器

docker rm -f pg15.7

2. 创建并配置容器

docker run -it --name=pg15.7 --restart=always --entrypoint=/bin/bash --user=root \
  -e PGDATA=/pgdata/ \
  -v /opt/qfusion/master/data:/pgdata \
  k8smaster.qfusion.irds/irds/postgres-15:v1.4.1-202409251139

# sed -e 's|^mirrorlist=|#mirrorlist=|g' \
#     -e 's|^#baseurl=http://dl.rockylinux.org/$contentdir|baseurl=https://mirrors.aliyun.com/rockylinux|g' \
#     -i.bak \
#     /etc/yum.repos.d/Rocky*.repo
# -- 设置yum源缓存
# dnf makecache -y
# yum repolist

mv /etc/yum.repos.d/pgdg-redhat-all.repo /root
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf install -y --enablerepo=powertools postgresql14-devel unzip redhat-rpm-config openssl-devel
dnf install iproute

echo 'export PATH=$PATH:/usr/pgsql-15/bin' >> ~/.bashrc
source ~/.bashrc

3. 初始化数据库并启动

chown -R postgres:postgres /pgdata
su - postgres 
/usr/pgsql-15/bin/initdb -D /pgdata/ 
/usr/pgsql-15/bin/pg_ctl -D /pgdata/ -l logfile start

docker commit pg15.7 pg15.7-build

主库配置参数

172.18.12.100 主
172.18.12.101 备

创建网络

docker network create --driver bridge --subnet=172.18.12.0/16 --gateway=172.18.12.1 mynet

docker network

4. 配置主库

mkdir -p /opt/qfusion/master/data

docker rm -f pg15.7-master
docker run -it --name=pg15.7-master --restart=always  --entrypoint=/bin/bash --user=root \
  --network=mynet --ip 172.18.12.100 \
  -e POSTGRES_PASSWORD=master \
  -p 5432:5432 \
  -v /opt/qfusion/master/data:/pgdata \
  pg15.7-build 


echo "host replication replica 172.18.12.101/32 trust" >> /pgdata/pg_hba.conf
tail /pgdata/pg_hba.conf

psql
CREATE ROLE replica login replication encrypted password 'replicationpassword';

pg_hba.conf

5. 修改 postgresql.conf 配置

grep -E 'listen_addresses|wal_level|max_wal_senders|wal_keep_segments|hot_standby|synchronous_commit|synchronous_standby_names|archive_mode|archive_command' postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
#wal_level = replica                    # minimal, replica, or logical
#synchronous_commit = on                # synchronization level;
#archive_mode = off             # enables archiving; off, on, or always
                                # (empty string indicates archive_command should
#archive_command = ''           # command to use to archive a logfile segment
#max_wal_senders = 10           # max number of walsender processes
#synchronous_standby_names = '' # standby servers that provide sync rep
#hot_standby = on                       # "off" disallows queries during recovery
#hot_standby_feedback = off             # send info from standby to prevent

sed -i 's/^#listen_addresses = .*/listen_addresses = \'*\'/' postgresql.conf

sed -i 's/^#archive_mode = .*/archive_mode = on/' postgresql.conf

grep -E 'listen_addresses|archive_mode' /pgdata/postgresql.conf

备库配置参数

6. 创建并配置备库容器

docker rm -f pg15.7-slave1

mkdir -p /opt/qfusion/slave1/data

docker run -it --name=pg15.7-slave1 --restart=always --entrypoint=/bin/bash --user=root \
  --network=mynet --ip 172.18.12.101 \
  -e POSTGRES_PASSWORD=master \
  -p 5433:5432 \
  -v /opt/qfusion/slave1/data:/pgdata \
  pg15.7-build

7. 初始化备库

su - postgres
chown -R postgres:postgres /pgdata

pg_basebackup -Fp --progress -R -h 172.18.12.100 -U replica -D /pgdata -X stream

# rm -rf /pgdata/*

默认生成的文件
cat postgresql.auto.conf
ls -l standby.signal

docker restart pg15.7-slave1

nohup /usr/pgsql-15/bin/pg_ctl -D /pgdata/ -l logfile start

流复制

8. 配置&检查主库复制状态

# 替换synchronous_standby_names 为standby1
sed -i 's/^#synchronous_standby_names = .*/synchronous_standby_names = standby1/' postgresql.conf
grep synchronous_standby_names /pgdata/postgresql.conf

SELECT pg_reload_conf();

SELECT pg_reload_conf();

SELECT * FROM pg_stat_replication;

9. 检查备库配置

# 增加 application_name=standby1 到 postgresql.auto.conf，默认是 walreceiver
vi /pgdata/postgresql.auto.conf
primary_conninfo = 'user=replica passfile=''/home/postgres/.pgpass'' channel_binding=prefer host=172.18.12.100 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=standby1'


# 检查synchronous_commit是否为on，如果不是需要修改
show synchronous_commit;

# 在主库查看视图
SELECT application_name, state, sync_state FROM pg_stat_replication;

参数级别

优化建议

1. 确保网络连接：确保主备库之间的网络连接稳定，避免因网络问题导致复制失败。
2. 监控和报警：使用监控工具（如 Prometheus、Grafana）监控主备库的复制状态，设置报警规则，及时发现并处理问题。
3. 备份和恢复：定期备份主备库的数据，确保在出现故障时能够快速恢复。
4. 性能调优：根据实际负载调整 postgresql.conf 中的参数，如 max_wal_senders、wal_keep_segments 等，以优化复制性能。

希望这些步骤和优化建议能帮助您更好地配置和管理 PostgreSQL 的主备复制。如果有任何问题或需要进一步的帮助，请随时联系。

问题1：FATAL: using recovery command file “recovery.conf” is not supported

tail postgresql-Mon.log
2025-01-13 09:41:22.194 UTC [122] LOG:  starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
2025-01-13 09:41:22.194 UTC [122] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-01-13 09:41:22.194 UTC [122] LOG:  listening on IPv6 address "::", port 5432
2025-01-13 09:41:22.195 UTC [122] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2025-01-13 09:41:22.196 UTC [122] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-01-13 09:41:22.199 UTC [126] LOG:  database system was interrupted; last known up at 2025-01-13 09:29:27 UTC
2025-01-13 09:41:22.208 UTC [126] FATAL:  using recovery command file "recovery.conf" is not supported
2025-01-13 09:41:22.209 UTC [122] LOG:  startup process (PID 126) exited with exit code 1
2025-01-13 09:41:22.209 UTC [122] LOG:  aborting startup due to startup process failure
2025-01-13 09:41:22.209 UTC [122] LOG:  database system is shut down