基于windows平台的postgresql主从数据库流备份配置

基于windows平台的postgresql主从数据库流备份配置

因工作需要,需要搞pg数据库的主从备份,领导给了个方向使用流备份,于是开始朝着这个方向进发。

鸣谢大佬A_ccelerator的博客

一、配置主从库

1.环境准备

对于 pg 的主从库配置,建议是使用两台机器。本文档基于两台 windows 环境编写。

主: 
操作系统:windows10 
数据库版本:postgresql-10.4 
ip:192.168.61.132 

从: 
操作系统:windows10 
数据库版本:postgresql-10.4 
ip:192.168.61.130

2.配置主库

(1)配置 pg_hba.conf 文件

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

host    replication    replica    192.168.61.130/32    md5

(2)创建 replica 相关权限

可以在 pgadmin 中直接运行 sql 语句:

CREATE ROLE replica login replication encrypted password ‘replica‘

(3)配置 postgresql.conf 文件

在 pg 的安装目录下,找到 postgresql.conf 文件,查找并修改如下配置(如 果前面有#,需要删除#字符):

wal_level = replica 
max_wal_senders = 32 
wal_keep_segments = 256 
wal_sender_timeout = 60s

参数简要说明:

wal_level                  //设置流复制模式至少设置为 
replica max_wal_senders    //这个设置了可以最多有几个流复制连接,差不多有几个从,就设置几个 
wal_keep_segments          //pg_wal 目录下保留 WAL 日志的个数,每个 WAL 文件默认 16M,为保 障从库能在应用归档落后时依旧能追上主库,此值建议设置较大一点。 
wal_sender_timeout         //设置流复制主机发送数据的超时时间

(4)启动主库

启动 pg 服务 postgresql-x64-10

3.配置从库

(1)配置 data

首先将主数据库的 data 文件夹内容通过 pg_basebackup 备份过来:

pg_basebackup -F p --progress -D "C:\Program Files\PostgreSQL\10\data2" -h 192.168.61.132 -p 5432 -U replica --password

输入密码 replica 等待备份结束
然后关闭 pg 服务,打开 PostgreSQL\10\的目录可以看到多出了 data2 出来, 此时备份并删除 data 文件夹,修改 data2 文件夹名为 data。

(2)配置 recovery.conf 文件

复制 PostgreSQL\10\share 下的 recovery.conf.sample 到 data 下,重命名 为 recovery.conf。
修改以下配置(如果前面有#,需要删除#字符):

standby_mode = on primary_conninfo = ‘host=192.168.61.132 port=5432 user=replica password=replica‘ recovery_target_timeline = ‘latest‘

(3)配置 postgresql.conf 文件

在 pg 的安装目录下,找到 postgresql.conf 文件,查找并修改如下配置(如 果前面有#,需要删除#字符):

hot_standby = on 
max_standby_streaming_delay = 30s 
wal_receiver_status_interval = 1s 
hot_standby_feedback = on

参数简要说明:

hot_standby                        //此参数控制在恢复归档期间是否支持只读操作,设置为 ON 后从库为只读模式。 
max_standby_streaming_delay        //数据流备份的最大延迟时间 
wal_receiver_status_interval       //多久向主报告一次从的状态,当然从每次数据复制都会向主 报告状态,这里只是设置最长的间隔时间。 
hot_standby_feedback               //如果有错误的数据复制,是否向主进行反馈

(4)启动从库

启动 pg 服务 postgresql-x64-10

4.确认是否成功

在主数据库的 pgadmin 中可以执行:

select client_addr,sync_state from pg_stat_replication;

假如出现如下的查询结果,便说明已经开始同步了
基于windows平台的postgresql主从数据库流备份配置
此时在主库上进行增删改操作,打开从库查看是否同步

二、主从切换

如果主库挂了,需要紧急使用从库来作为主库,以便服务能尽快恢复,操作方法如下:
(1)改一下从库的recovery.conf文件名。
(2)主库上新增recovery.conf文件,按照上文的从库配置方式配置一遍。重启主库,此时主库变更为从库
修改软件程序链接的数据库地址即可

三、问题记录及解决方案

1.从库配置data之后启动不了

自己看日志(windows系统日志 + pg日志)
(1)删除data文件夹下的postmaster.pid。
(2)修改data文件夹权限,给所有用户权限。

基于windows平台的postgresql主从数据库流备份配置

上一篇:装饰模式 - Decorator 和 外观模式 - Facade


下一篇:slow-log对mysql性能的影响