资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划,从策略到执行的一站式服务

pg_resetxlog清理的pg_xlog下的WAL日志

PostgreSQL的pg_xlog下有大量日志,空间不足,如何删除?

咸丰ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联建站的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:18982081108(备注:SSL证书合作)期待与您的合作!

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日志,当数据库服务启动的情况下,是不能使用的,所以执行之前需要停机。

(1)停机

Darren1:postgres:/usr/local/pgsql/bin:>pg_ctl stop -m fast

(2)查看NextXID和NextOID(发生checkpoint的时候,这两个值会发生改变)

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)启动数据库

Darren1:postgres:/usr/local/pgsql/data/pg_xlog:>pg_ctl start


分享题目:pg_resetxlog清理的pg_xlog下的WAL日志
文章地址:http://cdkjz.cn/article/jochjd.html
多年建站经验

多一份参考,总有益处

联系快上网,免费获得专属《策划方案》及报价

咨询相关问题或预约面谈,可以通过以下方式与我们联系

大客户专线   成都:13518219792   座机:028-86922220