2021-09-05 Windows环境下 postgresql12 增量备份及恢复实践

Windows环境下postgresql12 增量备份及恢复实践

因postgresql(以下简称pg)主要运行于 Linux系统，官网上参考资料及使用案例也大多是Linux系统下的，Windows 环境下有关数据库维护尤其是增量备份方面的参考资料比较少，这些都给Windows系统的pg管理员造成困扰，本文通过 Step by step 的方式，实现windows 环境下pg的增量备份与恢复，希望对大家有所帮助。

一、条件准备

1. 以管理员的方式下载并安装windows环境下的pg12，在安装过程中设置postgres超级用户密码。本例使用的操作系统是 windows server2019,安装位置是C:\PostgreSQL\12 。
2. 新建一用户 postgres（与pg的超级用户同名），并加入 Users组（默认会自动加入，检查确认一下）。

1)	net user postgres password /add    说明：设置postgres的密码，最好与pg的超级用户postgres相同。
2)	net localgroup Users  postgres /add
3)	新建备份文件夹 md  C:\backup
4)	修改backup的权限，赋予Users组完全控制权，并保持权限继承
5)	新建子目录 C:\backup\base_files 、C:\backup\wal_files

3. 修改系统脚本bug,停止pg服务

1)	以管理员运行CMD（win+R），并切换当前目录到C:\PostgreSQL\12，修改pg_env.bat文件（运行环境设置批处理脚本），通过文本编辑器打开，去除path路径环境变量值的双引号即可。
2)	如pg服务器正在运行，则停止pg服务，通过执行  pg_ctl  stop ,也可以通过 服务==》停止postgresql。

二、 修改配置文件

1. 修改pg的权限配置文件，data文件夹下的 pg_hba.conf,增加两行

1)	host    all             all             0.0.0.0/0               md5
2)	host    replication     all             0.0.0.0/0               md5

2. 修改data文件夹下的 postgresql.conf文件

1)	isten_addresses = '*'
2)	wal_level = replica	
3)	archive_mode = on
4)	archive_command = 'copy /V  "%p"  C:\\backup\\wal_files\\%f ' 注意写法，这里有大坑
5)	restore_command = 'copy  c:\\backup\\wal_files\\%f   "%p" '	
6)	recovery_target_time = '2021-09-03 11:26:33.258335+08' //根据恢复的时间点改写
7)	recovery_target_inclusive = false

三、 建立基础备份

有两种方式建立pg的基础备份，一是在pg停止服务的状态下，以管理员方式，把pg data文件夹下的所有内容拷贝到 C:\backup\base_files，下面论述第二种方式：

1. 启动pg 服务：

1)	Win+R ==> cmd ==>cd  C:\PostgreSQL\12
2)	运行设置环境变量的脚本文件Pg_env.bat
3)	Pc_ctl.exe start   //启动数据库

2. 利用pg 提供的命令完成基础备份

1)	pg_basebackup.exe -D C:\backup\base_files -Fp  -P -v  -U postgres
2)	 

四、 通过pg 客户端程序psql，执行数据库的操作，验证wal存档过程

1. 执行pg的控制台程序：Psql -U postgres
2. create table foo(c1 int, c2 timestamp default current_timestamp);
3. insert into foo select generate_series(1, 1000000), clock_timestamp(); //插入100万条记录
4. 记录当时的时间戳

1)	 select current_timestamp;
2)	2021-09-03 11:26:33.258335+08

5. 再插入50万条记录

1)	insert into foo select generate_series(1000001,1500000)；
2)	select current_timestamp;
3)	2021-09-03 14:54:34.893993+08

6. delete from foo;
7. 可以观察到随着上述数据库操作，文件夹 C:\backup\wal_files有大量的 wal存档文件。

五、 数据恢复

假如某个时刻，数据库出现故障，恢复pg的过程如下

1. 停止pg服务
2. 把原数据库集簇文件夹（data）进行更名处理，data ==> data_bad
3. 把C:\backup\base_files文件夹复制到 C:\PostgreSQL\12(原data文件夹处)，更名为data，并设置data 的权限为 Users组完全控制。
4. 在data中建一空文件，文件的全名为 recovery.signal(千万注意，要注意文件的扩展名，别创建一个recovery.signal.txt的文件)。
5. 再次确认postgresql.conf 中的restore_command设置正确。
6. 设置 recovery_target_time，注意时间格式的正确写法。
7. 在gp_env.bat环境下执行 pg_ctl -D C:\PostgreSQL\12\data start，系统在启动的过程中，自动通过回放WAL恢复数据库。

六、 善后工作

1. 通过pg客户端 psql，确认恢复工作完成。
2. 执行完恢复工作后，系统会自动删除恢复标识信号文件(recovery.signal),如未删除，手工删除之。以后pg系统再重新启动时，会自动工作在正常运行状态。
3. OK

七、 补充知识

1. 可以手工初始化数据库集簇

1)	创建数据库集簇文件夹 data，并赋予 Users组完全控制权限
2)	在pg_env.bat 环境下，initdb  -U postgres -W

2. 注册服务

1)	C:\PostgreSQL\12\bin \pg_ctl.exe runservice  -N postgresql-x64-12 -D  C:\PostgreSQL\12\data  -w

3. 取消注册

1)	C:\PostgreSQL\12\bin \pg_ctl.exe unregister -N postgresql-x64-12

4. 以用户postgres 登录系统

1)	Win键+R，输入 runas /user:postgres cmd