共计 2914 个字符，预计需要花费 8 分钟才能阅读完成。
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
