目录标题
制作镜像
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. 检查主库复制状态
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_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();
show synchronous_standby_names;
# 检查synchronous_commit是否为on，如果不是需要修改
show synchronous_commit;
# 在主库查看视图
SELECT application_name, state, sync_state FROM pg_stat_replication;
参数级别
优化建议
- 确保网络连接：确保主备库之间的网络连接稳定，避免因网络问题导致复制失败。
- 监控和报警：使用监控工具（如 Prometheus、Grafana）监控主备库的复制状态，设置报警规则，及时发现并处理问题。
- 备份和恢复：定期备份主备库的数据，确保在出现故障时能够快速恢复。
- 性能调优：根据实际负载调整
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
