windows平台的postgresql主从数据库流备份

主:
操作系统:windows10
数据库版本：postgresql-16.2
ip：192.168.3.254
从:
操作系统：windows10
数据库版本：postgresql-16.2
ip：192.168.3.253

配置主库

配置 pg_hba.conf 文件

在 pg 的安装目录下，找到 pg_hba.conf 文件
目录示例：C:\Program Files\PostgreSQL\16\data
在文件末尾增加一行代码，目的是增加名为 replica 的用户，以进行数据库 同步的操作。填写的 ip 为从数据库的地址。

host    replication    replica    192.168.3.11/32    md5

配置 postgresql.conf 文件

listen_addresses = '*'

创建 replica 相关权限

CREATE ROLE replica login replication encrypted password 'replica111'

启动主库

配置从库

配置 data

清空data文件夹
将主数据库的 data 文件夹内容通过 pg_basebackup 备份过来
请将防火墙策略设置得当，否则将无法连接

pg_basebackup -h 192.168.3.10 -p 2903 -U replica -R -P -v -C --slot=pgstandby1 -D D:\PostgreSQL\16\data

• -R 说明会创建standby.signal文件，以及补充postgresql.auto.conf的内容
• -P 显示备份进度
• -v 显示更加详细信息
• -C 同时创建复制槽
• -slot 指定复制槽的名字（一个备库一个名字）
• -D 生成备库的路径
  

复制槽的好处
主库的事务日志一直处于滚动消耗的状态，如果备库下线，随着主库频繁的数据变动，可能就会存在当备库重新上线后，已经找不到之前没有拉取的事务日志的情况（被主库回收掉了）。
但是有了复制槽，主库就会为复制槽保留它没有消费的日志，等待它上线后进行消费。当然代价是对磁盘的消耗，不过只要备库不是永久丢失，磁盘消耗对于大部分场景来说不是问题。
但是如果备库永久丢失了，要记得删除主库中对应的复制槽。删除复制槽的语句为select pg_drop_replication_slot(‘pgstandby1’);

输入密码等待备份结束
此时postgresql.auto.conf中已有主库连接配置，并且data下新建了一个standby.signal用于指示此为备份库

确认是否成功

select * from pg_replication_slots;\\查询主库中的插槽

select * from pg_stat_replication;\\查询已建立连接的备份库

主从切换

从库切换成主库

使用以下语句将从库切换成主库

select pg_promote();

此时，从库data中的standby.signal文件已删除

并修改pg_hba.conf 文件，ip改为原主库ip

host    replication    replica    192.168.3.10/32    md5

删除原主库复制槽

select pg_drop_replication_slot('pgstandby1');

将原从库的data备份到原主库

这一步是全量备份，所以要注意如果库很大，会很费时间，但是官方文档说不会影响其他客户端

pg_basebackup -h 192.168.3.11 -p 2903 -U replica -R -P -v -C --slot=pgstandby1 -D D:\PostgreSQL\16\data

此时，主库就会变成从库，从库中也能查询到对应的备份连接