ls000000010000000000000008.00000028.backup &..."/>
您好,登錄后才能下訂單哦!
PostgreSQL的pg_xlog下有大量日志,空間不足,如何刪除?
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>ls
000000010000000000000008.00000028.backup 00000001000000000000009D 0000000100000000000000C9 0000000100000000000000F5 000000010000000100000021 00000001000000010000004D
000000010000000000000072 00000001000000000000009E 0000000100000000000000CA 0000000100000000000000F6 000000010000000100000022 00000001000000010000004E
000000010000000000000073 00000001000000000000009F 0000000100000000000000CB 0000000100000000000000F7 000000010000000100000023 00000001000000010000004F
......
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>ll|wc -l
263
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>du -sh /usr/local/pgsql/data/pg_xlog/
4.1G /usr/local/pgsql/data/pg_xlog/
清理步驟:
pg_resetxlog用來清理WAL日志,當(dāng)數(shù)據(jù)庫服務(wù)啟動的情況下,是不能使用的,所以執(zhí)行之前需要停機(jī)。
(1)停機(jī)
Darren1:postgres:/usr/local/pgsql/bin:>pg_ctl stop -m fast
(2)查看NextXID和NextOID(發(fā)生checkpoint的時候,這兩個值會發(fā)生改變)
Darren1:postgres:/usr/local/pgsql/bin:>pg_controldata
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6446917631406040181
Database cluster state: shut down
pg_control last modified: Thu 27 Jul 2017 05:04:12 AM CST
Latest checkpoint location: 1/73000028
Prior checkpoint location: 1/720048F8
Latest checkpoint's REDO location: 1/73000028
Latest checkpoint's REDO WAL file: 000000010000000100000073
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:19545
Latest checkpoint's NextOID: 16646
......
(3)使用pg_resetxlog,指定oid和xid
Darren1:postgres:/usr/local/pgsql/bin:>pg_resetxlog -o 16646 -x 19545 -f /usr/local/pgsql/data/
Transaction log reset
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>ll
-rw-------. 1 postgres dba 302 Jul 26 12:12 000000010000000000000008.00000028.backup
-rw-------. 1 postgres dba 16777216 Jul 27 05:07 000000010000000100000077
drwx------. 2 postgres dba 20480 Jul 27 05:07 archive_status
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>du -sh /usr/local/pgsql/data/pg_xlog/
17M /usr/local/pgsql/data/pg_xlog/
(4)啟動數(shù)據(jù)庫
Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>pg_ctl start
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。