安装postgresql15sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf -qy module disable postgresql dnf install -y postgresql15-server dnf install -y postgresql15-plpython3.x86_64 postgresql15-odbc.x86_64 postgresql15-llvmjit.x86_64主从节点都安装一下安装完成后,bin文件路径/usr/pgsql-15/bin/一. 主节点配置更改数据目录# vim /usr/lib/systemd/system/postgresql-15.service # 更改数据目录 EnvironmentPGDATA/mnt/localFC1/pgsqlData/初始化数据库/usr/pgsql-15/bin/postgresql-15-setup initdb创建流复制的用户su - postgres pgsql CREATE ROLE replica login replication encrypted password replica;配置文件修改-postgresql.conf# vim /mnt/localFC1/pgsqlData/postgresql.conf # 更改以下及项: listen_addresses * # 设置监听的ip* 为允许所有 wal_level replica # wal日志写入级别要使用流复制必须使用replica或更高级别 full_page_writes on # 可以防止意外宕机后部分数据无法写入 synchronous_commit on synchronous_standby_names * hot_standby on # 打开热备配置文件修改-pg_hba.conf# vim /mnt/localFC1/pgsqlData/pg_hba.conf # 新增以下: host all all 0.0.0.0/0 trust host replication replica 0.0.0.0/0 md5为避免从库复制失败,建议设置# vim /mnt/localFC1/pgsqlData/postgresql.conf # 更改以下及项: wal_keep_size 10G # 根据自己情况 archive_mode on # 开启归档 archive_command gzip %p /mnt/localFC1/pg_archive/%f.gz # 归档方式及路径 archive_timeout 600s # 强制归档时间其他参数可根据自己情况进行配置启动数据库sudo systemctl enable postgresql-15 # 设置开机自启 sudo systemctl start postgresql-15 # 启动 sudo systemctl status postgresql-15 # 查看运行状态二. 从节点配置重点是standby.signal文件和primary_conninfo配置,以及目录权限从主节点备份数据及配置到从节点pg_basebackup -h 192.168.3.97 -p 5432 -U replica --password -X stream -Fp --progress -D /mnt/localFC1/pgsqlData/ -R同步完成后就可以看到备库服务器上自动生成了standby.signal文件。同时也看到在/mnt/localFC1/pgsqlData/路径下数据库自动帮我们配置了关于流复制的主库的信息更改从节点配置-postgresql.conf# 更改配置 # vi /mnt/localFC1/pgsqlData/postgresql.conf # 确保以下设置 listen_addresses * hot_standby on # 主节点连接信息在postgresql.auto.conf文件,当然也可以手动修改 #primary_conninfo 查看主节点连接信息-postgresql.auto.conf# Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo userreplica passwordreplica channel_bindingprefer host192.168.3.97 port5432 sslmodeprefer sslcompression0 sslsni1 ssl_min_protocol_versionTLSv1.2 gssencmodeprefer krbsrvnamepostgres target_session_attrsany更改用户组# 更改目录用户组 chown -R postgres.postgres /mnt/localFC1/pgsqlData/更改数据目录# vim /usr/lib/systemd/system/postgresql-15.service # 更改数据目录 EnvironmentPGDATA/mnt/localFC1/pgsqlData/启动sudo systemctl enable postgresql-15 # 设置开机自启 sudo systemctl start postgresql-15 # 启动 sudo systemctl status postgresql-15 # 查看运行状态三. 查看主从同步状态查看状态pg_controldatapg_controldata --pgdata/mnt/localFC1/pgsqlData/ # 主库: Database cluster state: in production # 备库: Database cluster state: in archive recovery主库使用 pg_stat_replication 监控流复制select * from pg_stat_replication;备库使用 pg_stat_wal_receiver 监控流复制select * from pg_stat_wal_receiver;查看备库落后主库多少字节的WAL日志select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(), write_lsn)) delay_wal_size, * from pg_stat_replication;查看备库接收WAL日志和应用WAL日志的状态select * from pg_last_wal_receive_lsn(); select * from pg_last_wal_replay_lsn(); select * from pg_last_xact_replay_timestamp();四. 主从切换查看主备库状态:pg_controldatapg_controldata 主库: Database cluster state: in production 备库: Database cluster state: in archive recovery停止主库systemctl stop postgresql-15.service # 查看状态,应该为: shut down pg_controldata --pgdata/mnt/localFC1/pgsqlData/原备库切换为主库:pg_ctl promotepg_controldata --pgdata/mnt/localFC1/pgsqlData/ # 确保备库状态为in archive recovery su - postgres # 切换为主节点 pg_ctl promote -D /mnt/localFC1/pgsqlData/原主库切换从库重点: 从库需要standby.signal文件和primary_conninfo配置创建standby.signalcd /mnt/localFC1/pgsqlData/ su - postgres # 创建 touch standby.signal # 确保权限为: postgres postgres -rw------- 1 postgres postgres 0 Jan 26 16:08 standby.signal修改primary_conninfo# vim /mnt/localFC1/pgsqlData/postgresql.conf # 修改以下信息 primary_conninfo userreplica passwordreplica channel_bindingprefer host192.168.3.97 port5432 sslmodeprefer sslcompression0 sslsni1 ssl_min_protocol_versionTLSv1.2 gssencmodeprefer krbsrvnamepostgres target_session_attrsany启动新从库sudo systemctl start postgresql-15小结备库提升为主库的命令pg_ctl promote; 新主库(原备库)的pg_hba.conf文件要开放允许流复制访问数据库的信息给原主库的IP地址 原主库配置为新备库的时候务必要创建$PGDATA/standby.signal文件 原主库配置为新备库的时候务必要修改$PGDATA/postgresql.auto.conf文件添加主库primary_conninfo的信息参考链接:https://cloud.tencent.com/developer/article/2013763?from15425https://www.cnblogs.com/a120608yby/p/17312376.htmlhttps://blog.51cto.com/u_175779/6010446https://blog.csdn.net/hqwang4/article/details/124833185