PostgreSQL Windows 数据库主从模式 热同步

1.操作主服务器

1.1修改pg_hba.conf

// 这边就设置所有用户，所有ip都可以交互
host    replication     all             0.0.0.0/0               md5

2.2 创建流复制用户

// 创建流复制用户replicator
CREATE USER replica REPLICATION LOGIN  PASSWORD 'replica';

2.3 修改postgresql.conf配置文件

// 覆盖postgresql.conf
# basic
listen_addresses = '*'                	# 监听所有ip
port = 5432                           	# 端口
max_connections = 1000                	# 最大连接数
superuser_reserved_connections = 10   	# 给超级用户预留的连接数
shared_buffers = 1GB                  	# 共享内存，一般设置为内存的1/4
wal_buffers = 16MB						# 用于还未写入磁盘的 WAL 数据的共享内存，一般设置为内存的1/128
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_keep_size = 10240					# 用于指定pg_wal目录中保存的过去的wal文件（wal 段）的最小数量，以防备用服务器在进行流复制时需要
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:\\PostgreSQL\\"%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分钟

# 参考
dynamic_shared_memory_type = windows  	# 服务器使用的内存管理方式。可能的值是posix（用于使用 shm_open分配的 POSIX 共享内存）、sysv （用于通过shmget分配的 System V 共享内存）、 windows（用于 Windows 共享内存）、和mmap （使用存储在数据目录中的内存映射文件模拟共享内存）。
max_worker_processes = 32			  	# 系统能够支持的后台进程的最大数量，默认值为 8。在更改这个值时，考虑也对 max_parallel_workers、max_parallel_maintenance_workers、max_parallel_workers_per_gather 进行调整。
max_parallel_workers_per_gather = 16  	# 设置允许的最大并行查询数，并行查询可能消耗比非并行查询更多的资源，把这个值设置为0将会禁用并行查询执行，默认值是2。
max_parallel_maintenance_workers = 4  	# 单一工具性命令能够启动的最大并行数。当前支持使用并行工作者的工具性命令是 CREATE INDEX，并且只有在构建B-树索引时才能并行，并且 VACUUM 没有 FULL选项。并行工作者从由 max_worker_processes 创建的进程池中取出，数量由max_parallel_workers控制，默认值为2。
max_parallel_workers = 32			  	# 设置系统支持的最大并行数量，默认值为8，要注意将这个值设置得大于 max_worker_processes 将不会产生效果，因为并行工作者进程都是从 max_worker_processes 所建立的工作者进程池中取出来的。
max_wal_senders = 6						# 启动复制进程数量限制，必须大于0
effective_cache_size = 32GB   			# 推荐操作系统物理内存的1/2
default_text_search_config = 'pg_catalog.simple'	# 选择被那些没有显式参数指定配置的文本搜索函数变体使用的文本搜索配置，内建默认值是pg_catalog.simple，但是如果能够标识一个匹配区域的配置，initdb将用对应于选中的lc_ctype区域的设置初始化配置文件
datestyle = 'iso, ymd'					# 设置日期和时间值的显示格式，以及解释有歧义的日期输入值的规则。由于历史原因， 这个变量包含两个独立的部分：输出格式声明（ISO、Postgres、SQL或German）、 输入/输出的年/月/日顺序（DMY、MDY或YMD）。这些可以被独立设置或者一起设置。关键字Euro和European是DMY的同义词；关键字US、NonEuro和NonEuropean是MDY的同义词。内建默认值是ISO, MDY，但是initdb将用对应于选中的lc_time区域行为的设置初始化配置文件
random_page_cost = 1.1					# 随机扫描磁盘的代价,默认是4;SSD等高性能磁盘可以设置小点。
lc_messages = 'Chinese (Simplified)_China.936'		# 设置消息显示的语言。可接受的值是系统相关的，如果这个变量被设置为空字符串（默认），那么该值将以一种系统相关的方式从服务器的执行环境中继承。
lc_monetary = 'Chinese (Simplified)_China.936'		# 设置用于格式化货币量的区域，例如用to_char函数族。可接受的值是系统相关的，如果这个变量被设置为空字符串（默认），那么该值将以一种系统相关的方式从服务器的执行环境中继承。
lc_numeric = 'Chinese (Simplified)_China.936'		# 设置用于格式化数字的区域，例如用to_char函数族。可接受的值是系统相关的，如果这个变量被设置为空字符串（默认），那么该值将以一种系统相关的方式从服务器的执行环境中继承。
lc_time = 'Chinese (Simplified)_China.936'			# 设置用于格式化日期和时间的区域，例如用to_char函数族。可接受的值是系统相关的，如果这个变量被设置为空字符串（默认），那么该值将以一种系统相关的方式从服务器的执行环境中继承。

2.4 创建归档存储位置

根据上方archive_command参数，创建归档存储文件夹

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 测试是否成功

主从库查询:
	主库是f代表false ；备库是t，代表true
	select pg_is_in_recovery();
主库查询复制状态：
	select * from pg_stat_replication;
从库查询wal日志接收状态：
	select * from pg_stat_wal_receiver;
从库是否同步
	select client_addr,sync_state from pg_stat_replication;

5 主备切换

原主库操作
1 停止主库
2 添加standby.signal文件，并根据3.3，3.4配置参数
3 重启
原备库操作
1 停止备库
2 删除standby.signal，删除postgresql.conf文件中的3.3操作
3 重启

PS: 如果启动失败则用 pg_ctl 启动从库(cmd 切到 pgsql/bin 目录下) 然后去看log报错原因

	-D pg_basebackup 执行的复制位置
	pg_ctl -D "D:\PostgreSQL\data" start