你好,游客 登录 注册 搜索
背景:
阅读新闻

PostgreSQL删除pg_xlog日志

[日期:2017-11-01] 来源:Linux社区  作者:Darren_Chen [字体: ]

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


(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

Ubuntu 16.04 下安装 PostgreSQL 和 phpPgAdmin  http://www.linuxidc.com/Linux/2016-08/134260.htm

Linux下RPM包方式安装PostgreSQL  http://www.linuxidc.com/Linux/2016-03/128906.htm

Linux下安装PostgreSQL  http://www.linuxidc.com/Linux/2016-12/138765.htm

Linux下PostgreSQL安装部署指南  http://www.linuxidc.com/Linux/2016-11/137603.htm

Linux下安装 PostgreSQL 并设置基本参数  http://www.linuxidc.com/Linux/2016-11/137324.htm

Ubuntu 16.04 下 PostgreSQL 主从复制配置  http://www.linuxidc.com/Linux/2017-08/146190.htm

Fedota 24 将数据库升级到 PostgreSQL 9.5  http://www.linuxidc.com/Linux/2016-11/137374.htm

CentOS7安装配置PostgreSQL9.6  http://www.linuxidc.com/Linux/2017-10/147536.htm

CentOS5.8_x64下离线安装PostgreSQL 9.1  http://www.linuxidc.com/Linux/2017-10/147822.htm

CentOS 6.5下PostgreSQL服务部署  http://www.linuxidc.com/Linux/2017-01/139144.htm

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-11/148186.htm

linux
相关资讯       PostgreSQL日志删除  pg_xlog日志删除 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款