Postgresql 如何清理WAL日志
WAL是Write Ahead Log的簡(jiǎn)寫(xiě)，和oracle的redo日志類(lèi)似，存放在$PGDATA/pg_xlog中，10版本以后在$PGDATA/pg_wal目錄.
如果開(kāi)啟了歸檔，在目錄archive_status下會(huì)有一些文件，以ready結(jié)尾的，表示可以歸檔但還沒(méi)有歸檔，done結(jié)尾的表示已經(jīng)歸檔。
和WAL日志數(shù)量相關(guān)的幾個(gè)參數(shù)：
wal_keep_segments = 300 # in logfile segments, 16MB each; 0 disables checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 checkpoint_segments = 2048 # in logfile segments, min 1, 16MB each #9.5以后checkpoint_segments不再使用，要參考設(shè)置以下兩個(gè)參數(shù) #max_wal_size = 1GB #最大不超過(guò)這個(gè)值 #min_wal_size = 80MB #最小保留這個(gè)值
如果沒(méi)有開(kāi)啟歸檔的情況下：
不超過(guò)以下兩個(gè)公式計(jì)算得出的個(gè)數(shù)
(2 + checkpoint_completion_target) * checkpoint_segments + 1 或者checkpoint_segments + wal_keep_segments + 1
9.5以后，如果超過(guò)了max_wal_size，那么就會(huì)刪除不需要的wal.
如果開(kāi)啟了歸檔，那么歸檔成功了，才會(huì)被清除，所以這里注意一下，如果你開(kāi)啟了歸檔，但是歸檔命令是失效的，那么wal目錄會(huì)一直增長(zhǎng)，不會(huì)自動(dòng)刪除WAL，會(huì)使得此目錄被撐爆。
什么情況下回自動(dòng)清理wal
1.做檢查點(diǎn)的時(shí)候
2.數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候，或者修改了相關(guān)參數(shù)后重啟數(shù)據(jù)庫(kù)。
觸發(fā)檢查點(diǎn)的情況：
1.從前一個(gè)檢查點(diǎn)發(fā)生過(guò)后的時(shí)間超過(guò)checkpoint_timeout設(shè)置的間隔（默認(rèn)間隔為300秒（5分鐘））。
2.在版本9.4或更早版本中，自上一個(gè)檢查點(diǎn)以來(lái)，超過(guò)使用checkpoint_segments（默認(rèn)數(shù)量為3）設(shè)置的WAL段文件數(shù)量。
3.在9.5或更高版本中，pg_xlog（版本10或更高版本，pg_wal）中WAL段文件的總大小已超過(guò)參數(shù)max_wal_size的值（默認(rèn)值為1GB（64個(gè)文件））。
4.PostgreSQL服務(wù)在smart和fast模式下停止。
5.手動(dòng)執(zhí)行checkpoint.
如何手動(dòng)清理wal
可以通過(guò)縮小以上涉及到的函數(shù)減少wal segment的數(shù)量，也可以手動(dòng)刪除，如下：
pg_controldata Latest checkpoint location: 16/79FF5520 Latest checkpoint's REDO location: 16/79FF54E8 Latest checkpoint's REDO WAL file: 00000001000000160000001E
這里表示16/79FF54E8檢查點(diǎn)已經(jīng)執(zhí)行，已經(jīng)包含在00000001000000160000001E日志文件中，那么這個(gè)日志之前的日志是可以清理的。
可以手動(dòng)使用系統(tǒng)命令rm清理
也可以使用pg_archivecleanup清理
比如：
查看日志 ll total 833M -rw------- 1 postgres postgres 64M Aug 29 11:10 00000001000000160000001E -rw------- 1 postgres postgres 64M Jul 16 11:24 00000001000000160000001F -rw------- 1 postgres postgres 64M Jul 16 11:24 000000010000001600000020 -rw------- 1 postgres postgres 64M Jul 16 11:25 000000010000001600000021 -rw------- 1 postgres postgres 64M Jul 16 11:25 000000010000001600000022 -rw------- 1 postgres postgres 64M Jul 16 11:26 000000010000001600000023 -rw------- 1 postgres postgres 64M Jul 16 11:24 000000010000001600000024 -rw------- 1 postgres postgres 64M Jul 16 11:24 000000010000001600000025 -rw------- 1 postgres postgres 64M Jul 16 11:26 000000010000001600000026 -rw------- 1 postgres postgres 64M Jul 16 11:40 000000010000001600000027 -rw------- 1 postgres postgres 64M Jul 16 11:40 000000010000001600000028 -rw------- 1 postgres postgres 64M Jul 16 11:51 000000010000001600000029 -rw------- 1 postgres postgres 64M Jul 16 11:40 00000001000000160000002A drwx------ 2 postgres postgres 4.0K Jun 18 10:15 archive_status 保留000000010000001600000027之后的日志 pg_archivecleanup /opt/pgdata11.3/pg_root/pg_wal/ 000000010000001600000027 再次查看驗(yàn)證 ls 000000010000001600000027 000000010000001600000028 000000010000001600000029 00000001000000160000002A archive_status
補(bǔ)充：Postgresql 數(shù)據(jù)庫(kù)設(shè)置備份以及簡(jiǎn)單清理磁盤(pán)空間和wal日志的方法
1. 最近想簡(jiǎn)單的進(jìn)行數(shù)據(jù)庫(kù)的備份工作, 因?yàn)楝F(xiàn)在數(shù)據(jù)庫(kù)主要是用的pg數(shù)據(jù)庫(kù) , 所以想到用文本的方式進(jìn)行, 有清理了一下日志表的數(shù)據(jù) 這里一起記錄一下.
先記錄一下查看比較大的表的信息.
從網(wǎng)上找了下資料, 使用子查詢(xún)的方式來(lái)查詢(xún)表信息.
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
找出來(lái)最大的表信息之后, 需要使用
truncate table 的方式能夠直接清理空間不然空間還是占用著的.
truncate table ****
或者是在數(shù)據(jù)庫(kù)的 /data/base 目錄下面也能夠看到響應(yīng)的表信息
2. 整理pg_wal 文件內(nèi)的信息
wal 是 write ahead log 的意思 里面跟oracle的redo內(nèi)容類(lèi)似, 有時(shí)候文件會(huì)比較多需要清理一下.
方法
su - postgres
cd /usr/lib/yourpgbinpath
./pg_resetwal {PGDATA}
執(zhí)行清理
3. 執(zhí)行備份恢復(fù)的方式
備份 tar -czvf /pgdata.tar.gz /pgdatapath 恢復(fù) tar -zxvf /pgdata.tar.gz / 恢復(fù)即可,
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
PostgreSQL使用JSONB存儲(chǔ)和查詢(xún)復(fù)雜的數(shù)據(jù)結(jié)構(gòu)
在PostgreSQL中,JSONB是一種二進(jìn)制格式的JSON數(shù)據(jù)類(lèi)型,它允許你在數(shù)據(jù)庫(kù)中存儲(chǔ)和查詢(xún)復(fù)雜的JSON數(shù)據(jù)結(jié)構(gòu),本文給大家介紹了如何使用JSONB類(lèi)型在PostgreSQL中存儲(chǔ)和查詢(xún)復(fù)雜的數(shù)據(jù)結(jié)構(gòu),需要的朋友可以參考下2024-04-04
PostgreSQL自動(dòng)更新時(shí)間戳實(shí)例代碼
最近有這么一個(gè)工程,需要使用postgresql數(shù)據(jù)庫(kù),在數(shù)據(jù)庫(kù)中的好幾個(gè)表中都需要時(shí)間戳這個(gè)字段,這篇文章主要給大家介紹了關(guān)于PostgreSQL自動(dòng)更新時(shí)間戳的相關(guān)資料,需要的朋友可以參考下2021-11-11
初識(shí)PostgreSQL存儲(chǔ)過(guò)程
這篇文章主要介紹了初識(shí)PostgreSQL存儲(chǔ)過(guò)程,本文講解了PostgreSQL中存儲(chǔ)過(guò)程的語(yǔ)法,并給出了一個(gè)操作實(shí)例,需要的朋友可以參考下2015-01-01
PostgreSQL查看數(shù)據(jù)庫(kù)占用空間大小的幾種常用方法
在PostgreSQL中,查看數(shù)據(jù)庫(kù)及數(shù)據(jù)表當(dāng)前數(shù)據(jù)的占用量可以通過(guò)執(zhí)行特定的SQL查詢(xún)來(lái)實(shí)現(xiàn),本文給大家介紹了幾種常用的方法,并通過(guò)代碼示例講解的非常詳細(xì),需要的朋友可以參考下2024-05-05
postgresql insert into select無(wú)法使用并行查詢(xún)的解決
這篇文章主要介紹了postgresql insert into select無(wú)法使用并行查詢(xún)的解決，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
PostgreSQL與MySQL優(yōu)劣勢(shì)比較淺談
這篇文章主要詳細(xì)介紹了PostgreSQL與MySQL有哪些優(yōu)劣勢(shì)，文中介紹的非常詳細(xì)，對(duì)于學(xué)習(xí)有一定的幫助，感興趣的小伙伴可以參考一下2023-04-04
postgresql rank() over, dense_rank(), row_number()用法區(qū)別
這篇文章主要介紹了postgresql rank() over, dense_rank(), row_number()的用法區(qū)別，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
PostgreSQL的外部數(shù)據(jù)封裝器fdw用法
這篇文章主要介紹了PostgreSQL的外部數(shù)據(jù)封裝器fdw用法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL+GeoHash地圖點(diǎn)位聚合實(shí)現(xiàn)代碼
這篇文章主要介紹了PostgreSQL+GeoHash地圖點(diǎn)位聚合,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-07-07
PostgreSQL?流復(fù)制配置環(huán)境搭建過(guò)程
PostgreSQL 流復(fù)制是 9.0 提供的一種新的 WAL 傳遞方法，使用流復(fù)制時(shí)，每當(dāng) Primary 節(jié)點(diǎn) WAL 產(chǎn)生，就會(huì)馬上傳遞到 Standby 節(jié)點(diǎn)，流復(fù)制提供異步和同步兩種模式，同步模式可以保障數(shù)據(jù) 0 丟失，這篇文章主要介紹了PostgreSQL?流復(fù)制搭建,需要的朋友可以參考下2023-09-09