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

更新時(shí)間：2020年12月30日 16:06:38 作者：奈何流年

這篇文章主要介紹了postgresql減少wal日志生成量的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

1、在繁忙的系統(tǒng)中，如果需要降低checkpoint發(fā)生的頻率，減少WAL日志的生成量，減輕對(duì)系統(tǒng)IO的壓力，可以通過(guò)以下兩種方法。

1) 調(diào)整WAL segment大小，最高可以調(diào)整到64MB，不過(guò)只能通過(guò)編譯來(lái)調(diào)整。對(duì)于已有系統(tǒng)不太方便；

2) 增大checkpoint_segments設(shè)置，使得checkpoint不會(huì)過(guò)于頻繁地被觸發(fā)；

2、在9.5中，checkpoint_segments被廢棄，可以通過(guò)新增參數(shù)max_wal_size來(lái)調(diào)整，該參數(shù)缺省為1GB，已經(jīng)是9.4的2倍。但如果9.4中手工設(shè)置了checkpoint_segments，如本例，則以下的公式可以做為9.5設(shè)置max_wal_size的參考。

max_wal_size = (3 * checkpoint_segments) * 16MB

補(bǔ)充：PostgreSQL利用全備與WAL日志恢復(fù)數(shù)據(jù)庫(kù)

一般情況全備只能做到備份時(shí)刻的恢復(fù)，在全備操作過(guò)后的數(shù)據(jù)庫(kù)信息無(wú)法同步，此時(shí)就需要利用wal日志來(lái)進(jìn)行時(shí)間點(diǎn)的恢復(fù)

基礎(chǔ)備份——全備

使用pg_basebackup

pg_basebackup是postgresql提供的一個(gè)方便基礎(chǔ)備份的工具（9.1開(kāi)始提供），這個(gè)工具會(huì)把整個(gè)數(shù)據(jù)庫(kù)實(shí)例的數(shù)據(jù)都拷貝出來(lái)，而不只是把實(shí)例中的部分（如某個(gè)數(shù)據(jù)庫(kù)或表）單獨(dú)備份出來(lái)，該工具使用replication協(xié)議連接到數(shù)據(jù)庫(kù)實(shí)例上，所以主數(shù)據(jù)庫(kù)中的pg_hba.conf必須允許replication連接，類(lèi)似如下：

local replication postgre ident

在9.2之后支持級(jí)連復(fù)制，所以在之后的版本中，pg_basebackup也可以從另外一個(gè)standby庫(kù)上做基礎(chǔ)備份，都需注意如下幾方面：

1、備份中沒(méi)有備份歷史文件；

2、不確保所有需要的WAL文件都備份了，如果想確保，需要加命令行參數(shù) ”-x";

3、如果在備份過(guò)程中standby被提升為主庫(kù)，則備份會(huì)失?。?/p>

4、要求主庫(kù)中打開(kāi)了“full_page_writes"參數(shù)，WAL文件不能被類(lèi)似pg_compresslog的工具去掉full_page_writes信息。

參數(shù)

-Ft F表示輸出格式，t為tar包的格示，p，默認(rèn)值，輸出為目錄。

-X fetch X表示收集wal日志的方式 fetch表示收集wal日志，stream為不收集，以備庫(kù)streaming的方式追趕主庫(kù)，none一般不使用

-h 要備份數(shù)據(jù)庫(kù)的所在的IP

-p 數(shù)據(jù)庫(kù)端口號(hào)

-P 備份進(jìn)度，以百分制顯示

-v 輸出備份信息，如上面pg_basebackup:等類(lèi)似語(yǔ)句。

-W 輸入密碼選項(xiàng)

-D 要備份到的目錄

其他選項(xiàng) 比如-R 備份備庫(kù)時(shí)保存recover.conf文件

WAL日志的的備份

測(cè)試流程

將被數(shù)據(jù)文件全備

一臺(tái)是已運(yùn)行的主庫(kù)，一臺(tái)是安裝好數(shù)據(jù)庫(kù)但是沒(méi)有初始化的預(yù)恢復(fù)庫(kù)

將主庫(kù)的數(shù)據(jù)文件全備到備庫(kù)的的數(shù)據(jù)目錄中

繼續(xù)操作主庫(kù)

在表中插入幾行數(shù)據(jù)，并留下時(shí)間

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日志歸檔到備庫(kù)

這邊是直接將日志傳過(guò)去，到備庫(kù)的/backup 目錄

配置recovery.conf文件

在pgdata目錄里，創(chuàng)建文件

[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'

啟動(dòng)恢復(fù)實(shí)例

[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

查看恢復(fù)情況

發(fā)現(xiàn)已經(jīng)將之后的操作在備庫(kù)上進(jìn)行恢復(fù)了

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

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: