postgresSQL清理xlog

pg_log可以直接用rm删除，但是pg_xlog不行，清理步骤如下：

1、停库：

[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data/pg_xlog]$ pg stop
waiting for server to shut down.... done
server stopped
Lock file exists. exit
Lock file exists. exit
 Pgagent status is 100.
Can not find the pid file. 7490 is not runing.

2查询NextXID和NextOID
[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data/pg_xlog]$ pg_controldata
pg_control version number:            942
Catalog version number:               201510051
Database system identifier:           6307074520783085345
Database cluster state:               shut down
pg_control last modified:             Sat 08 Oct 2016 06:28:29 PM CST
Latest checkpoint location:           8/E4010378
Prior checkpoint location:            8/E40102D0
Latest checkpoint's REDO location:    8/E4010378
Latest checkpoint's REDO WAL file:    000000010000000800000039
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/740317
Latest checkpoint's NextOID:          49157
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1741
Latest checkpoint's oldestXID's DB:   13241
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 13241
Latest checkpoint's oldestCommitTsXid:740312
Latest checkpoint's newestCommitTsXid:740316
Time of latest checkpoint:            Sat 08 Oct 2016 06:28:29 PM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    logical
wal_log_hints setting:                off
max_connections setting:              2000
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       on
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 1310720
WAL block size:                       16384
Bytes per WAL segment:                67108864
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

3、清理XLOG

[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ du -sh pg_xlog/
11G pg_xlog/
[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ pwd
/paic/pg7490/data
[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ pg_resetxlog -o 49157 -x 740317 -f /paic/pg7490/data
Transaction log reset
[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ du -sh pg_xlog/
66M pg_xlog/

4启库

[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ pg start
server starting
2016-10-08 18:32:30 CST::@:[1724]: LOG:  redirecting log output to logging collector process
2016-10-08 18:32:30 CST::@:[1724]: HINT:  Future log output will appear in directory "/paic/pg7490/data/pg_log".
Lock file exists. exit
Lock file exists. exit
 Pgagent status is 100.
 PG is in recovery: false

postgres  1724     1  8 18:32 pts/1    00:00:00 /paic/postgres/base/9.5.2/bin/postgres -D /paic/pg7490/data
postgres  1729  1724  0 18:32 ?        00:00:00 postgres: d0cmu: logger process                           
postgres  1731  1724  0 18:32 ?        00:00:00 postgres: d0cmu: checkpointer process                     
postgres  1732  1724  0 18:32 ?        00:00:00 postgres: d0cmu: writer process                           
postgres  1733  1724  0 18:32 ?        00:00:00 postgres: d0cmu: wal writer process                       
postgres  1734  1724  0 18:32 ?        00:00:00 postgres: d0cmu: autovacuum launcher process              
postgres  1735  1724  0 18:32 ?        00:00:00 postgres: d0cmu: stats collector process                  
[postgres@cnsz081285:d0cmu:7490 /paic/pg7490/data]$ psql
Timing is on.
psql (9.5.2)
Type "help" for help.

[postgres:7490@postgres] [10-08.18:32:33]=# \q

如果报错ERROR: can not found /../recovery.conf

则用原始命令启库

 pg_ctl start -D $PGDATA

再起pgapp进程 pgapp $PGPORTstart