Windows10配置PostgresSQL15数据库主从模式
很少有windows服务器创建PostgresSQL15主从的,经过实验后的总结。
1. 准备好两台服务器
主服务器:192.168.1.1
从服务器:192.168.1.2
2. 操作主服务器
2.1 修改pg_hba.conf
在PostgresSQL安装目录中,data文件夹内的pg_hba.conf文件添加从服务器地址及访问的用户
// 这边就设置所有用户,所有ip都可以交互
host replication all 0.0.0.0/0 md5
2.2 创建流复制用户
// 创建流复制用户replicator
create user replica replication login connection limit 5 password 'replica';
2.3 修改postgresql.conf配置文件
我这边将原来的postgresql.conf复制一份,直接将下面代码覆盖到postgresql.conf
// 覆盖postgresql.conf
# basic
listen_addresses = '*' # 监听所有ip
port = 5432 # 端口
max_connections = 1000 # 最大连接数
superuser_reserved_connections = 10 # 给超级用户预留的连接数
shared_buffers = 1GB # 共享内存,一般设置为内存的1/4
work_mem = 16MB # 设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量
maintenance_work_mem = 256MB # 在维护性操作(例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)中使用的 最大的内存量
timezone = 'Asia/Shanghai' # 系统时区
hot_standby = on # 打开热备
# optimizer
default_statistics_target = 500 # 默认100,ANALYZE在pg_statistic中存储的信息量,增大该值,会增加ANALYZE的时间,但会让解释计划更精准
# wal
max_wal_size = 1GB # 建议与shared_buffers保持一致
min_wal_size = 80MB # 建议max_wal_size/12.5
wal_log_hints = on # 控制WAL日志记录的方式,建议打开
wal_level = replica # wal日志写入级别,要使用流复制,必须使用replica或更高级别
wal_sender_timeout = 60s # 设置WAL发送者在发送WAL数据时等待主服务器响应的超时时间
# archive
archive_mode = on #
archive_command = 'copy "%p" D:\\pg_archive\\"%f"' # 归档存储位置,自行修改
# log 近7天轮询
log_destination = 'csvlog' # 日志格式
logging_collector = on # 日志收集器
log_directory = 'pg_log' # 日志目录 $PGDATA/pg_log
log_filename = 'postgresql.%a' # 7天日志轮询
log_file_mode = 0600 # 日志文件的权限
log_rotation_size = 0 # 日志的最大尺寸,设置为零时将禁用基于大小创建新的日志文件
log_truncate_on_rotation = on # 这个参数将导致PostgreSQL截断(覆盖而不是追加)任何已有的同名日志文件
log_min_duration_statement = 0 # 如果语句运行至少指定的时间量,将导致记录每一个这种完成的语句的持续时间
log_duration = on # 每一个完成的语句的持续时间被记录
log_lock_waits = on # 控制当一个会话为获得一个锁等到超过deadlock_timeout时,是否要产生一个日志消息
log_statement = 'mod' # 控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all(所有语句)。ddl记录所有数据定义语句,例如CREATE、ALTER和 DROP语句。mod记录所有ddl语句,外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM
log_timezone = 'Asia/Shanghai' # 设置在服务器日志中写入的时间戳的时区
# sql
statement_timeout = 300000 # 语句执行超时时间 5分钟
idle_in_transaction_session_timeout = 300000 # 事务空闲超时时间 5分钟
idle_session_timeout = 1800000 # 会话空闲超时时间 30分钟
lock_timeout = 60000 # 等锁超时时间 1分钟
2.4 创建归档存储位置
根据上方archive_command参数,创建归档存储文件夹,我这边就放到D盘的pg_archive文件夹了
2.5 重启Pg15服务
3 操作从服务器
3.1 复制data文件夹
3.2 删除data文件夹
3.2 运行命令获取主库的data
运行命令,输入之前创建用户的密码
// 运行命令 ip为主库ip -D 复制的位置
pg_basebackup -h 192.168.1.1 -p 5432 -U replica -D "D:\PostgreSQL\data" -p 5432 --wal-method=stream
3.3 修改postgresql.conf
由于之前从主服务器复制过来了配置文件,所以不需要修改很多
// 添加命令 主库的连接用户
primary_conninfo = 'host=192.168.1.1 port=5432 user=replica password=replica' #主库信息
3.4 data文件夹创建文件standby.signal
// 添加命令 主库的连接用户
standby_mode = on #on为从库
primary_conninfo = 'host=192.168.1.1 port=5432 user=replica password=replica' #主库信息
recovery_target_timeline = 'latest' #流复制同步最新数据
3.5 重启从库
4 测试是否成功
主从库查询:
select pg_is_in_recovery();
主库是f代表false ;备库是t,代表true
主库查询复制状态:
select * from pg_stat_replication;
从库查询wal日志接收状态:
select * from pg_stat_wal_receiver;
主库数据插入数据,查询从库是否存在
查看主库是D:\pg_archive下是否有归档数据
5 主备切换
原主库操作
1 停止主库
2 添加standby.signal文件,并根据3.3,3.4配置参数
3 重启
原备库操作
1 停止备库
2 删除standby.signal,删除postgresql.conf文件中的3.3操作
3 重启