WAL logs under pg_xlog cleaned by pg_resetxlog

There are a large number of logs under PostgreSQL's pg_xlog. There is insufficient space. How to delete them?

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

two hundred and sixty three

Darren1:postgres:/usr/local/pgsql/data/pg_xlog: > du-sh / usr/local/pgsql/data/pg_xlog/

4.1G / usr/local/pgsql/data/pg_xlog/

Cleanup steps:

Pg_resetxlog is used to clean up WAL logs, which is not available when the database service is started, so it needs to be stopped before execution.

(1) shutdown

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

(2) check NextXID and NextOID (these two values will change when checkpoint occurs)

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) use pg_resetxlog to specify oid and 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) start the database

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