postgresql减少wal日志生成量的操作

1、在繁忙的系统中，如果需要降低checkpoint发生的频率，减少wal日志的生成量，减轻对系统io的压力，可以通过以下两种方法。

1) 调整wal segment大小，最高可以调整到64mb，不过只能通过编译来调整。对于已有系统不太方便；

2) 增大checkpoint_segments设置，使得checkpoint不会过于频繁地被触发；

2、在9.5中，checkpoint_segments被废弃，可以通过新增参数max_wal_size来调整，该参数缺省为1gb，已经是9.4的2倍。但如果9.4中手工设置了checkpoint_segments，如本例，则以下的公式可以做为9.5设置max_wal_size的参考。

max_wal_size = (3 * checkpoint_segments) * 16mb

补充：postgresql利用全备与wal日志恢复数据库

一般情况全备只能做到备份时刻的恢复，在全备操作过后的数据库信息无法同步，此时就需要利用wal日志来进行时间点的恢复

基础备份——全备

使用pg_basebackup

pg_basebackup是postgresql提供的一个方便基础备份的工具（9.1开始提供），这个工具会把整个数据库实例的数据都拷贝出来，而不只是把实例中的部分（如某个数据库或表）单独备份出来，该工具使用replication协议连接到数据库实例上，所以主数据库中的pg_hba.conf必须允许replication连接，类似如下：

local replication postgre ident

在9.2之后支持级连复制，所以在之后的版本中，pg_basebackup也可以从另外一个standby库上做基础备份，都需注意如下几方面：

1、备份中没有备份历史文件；

2、不确保所有需要的wal文件都备份了，如果想确保，需要加命令行参数 ”-x";

3、如果在备份过程中standby被提升为主库，则备份会失败；

4、要求主库中打开了“full_page_writes"参数，wal文件不能被类似pg_compresslog的工具去掉full_page_writes信息。

参数

-ft f表示输出格式，t为tar包的格示，p，默认值，输出为目录。

-x fetch x表示收集wal日志的方式 fetch表示收集wal日志，stream为不收集，以备库streaming的方式追赶主库，none一般不使用

-h 要备份数据库的所在的ip

-p 数据库端口号

-p 备份进度，以百分制显示

-v 输出备份信息，如上面pg_basebackup:等类似语句。

-w 输入密码选项

-d 要备份到的目录

其他选项 比如-r 备份备库时保存recover.conf文件

wal日志的的备份

测试流程

将被数据文件全备

一台是已运行的主库，一台是安装好数据库但是没有初始化的预恢复库

将主库的数据文件全备到备库的的数据目录中

继续操作主库

在表中插入几行数据，并留下时间

postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1
postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1
postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1
postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1
postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1
postgres=# insert into test08 values (6666666,'asdfg');
insert 0 1

postgres=# select now();
 now 
-------------------------------
 2018-07-18 15:03:28.969495+08
(1 row)
postgres=# 

切换wal日志

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 5/ef0009d8
(1 row)
postgres=# 

####将wal日志归档到备库

这边是直接将日志传过去，到备库的/backup 目录

配置recovery.conf文件

在pgdata目录里，创建文件

[postgres@mdw pgdata]$ cat recovery.conf 
recovery_target_time = ' 2018-07-18 11:00:18.526347+08 '
restore_command = 'cp /backup/pg_wal/%f %p'

启动恢复实例

[postgres@mdw pgdata]$ pg_ctl start
waiting for server to start....2018-07-18 15:07:52.420 cst [3353] log: listening on ipv4 address "0.0.0.0", port 5432
2018-07-18 15:07:52.420 cst [3353] log: listening on ipv6 address "::", port 5432
2018-07-18 15:07:52.426 cst [3353] log: listening on unix socket "/tmp/.s.pgsql.5432"
2018-07-18 15:07:52.468 cst [3354] log: database system was interrupted; last known up at 2018-07-18 15:00:09 cst
2018-07-18 15:07:52.950 cst [3354] log: starting point-in-time recovery to 2018-07-18 15:03:28.969495+08
2018-07-18 15:07:52.987 cst [3354] log: restored log file "0000000100000005000000f8" from archive
2018-07-18 15:07:53.247 cst [3354] log: redo starts at 5/f8000028
2018-07-18 15:07:53.308 cst [3354] log: consistent recovery state reached at 5/f8000b08
2018-07-18 15:07:53.308 cst [3353] log: database system is ready to accept read only connections
 done
server started
[postgres@mdw pgdata]$ 2018-07-18 15:07:53.343 cst [3354] log: restored log file "0000000100000005000000f9" from archive
2018-07-18 15:07:53.626 cst [3354] log: restored log file "0000000100000005000000fa" from archive
2018-07-18 15:07:54.192 cst [3354] log: invalid record length at 5/fa000140: wanted 24, got 0
2018-07-18 15:07:54.192 cst [3354] log: redo done at 5/fa000108
2018-07-18 15:07:54.192 cst [3354] log: last completed transaction was at log time 2018-07-18 15:03:20.200594+08
2018-07-18 15:07:54.397 cst [3354] log: restored log file "0000000100000005000000fa" from archive
cp: cannot stat `/backup/pg_wal/00000002.history': no such file or directory
2018-07-18 15:07:54.633 cst [3354] log: selected new timeline id: 2
cp: cannot stat `/backup/pg_wal/00000001.history': no such file or directory
2018-07-18 15:07:55.160 cst [3354] log: archive recovery complete
2018-07-18 15:07:55.263 cst [3353] log: database system is ready to accept connections

查看恢复情况

发现已经将之后的操作在备库上进行恢复了

postgres=# select * from test08 where id=6666666;
 id | n 
---------+-------
 6666666 | asdfg
 6666666 | asdfg
 6666666 | asdfg
 6666666 | asdfg
 6666666 | asdfg
 6666666 | asdfg
(6 rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。