pg_resetxlog、pg_resetwal工具的两个用法
pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster
pg_resetwal [ --force | -f ] [ --dry-run | -n ] [option...] [ --pgdata | -D ] datadir
一、PostgreSQL恢复pg_control文件:
1.需要下面四个参数:
-x XID set next transaction ID
在pg_clog下面,找到最大的文件编号,+1 后面跟上5个0
如:0000
-x = 0x000100000
-m MXID set next and oldest multitransaction ID
在pg_multixact/offsets下面,找到最大的文件编号,+1 后面跟上4个0
如:0000
-m = 0x00010000
-O OFFSET set next multitransaction offset
在pg_multixact/members下面,找到最大的文件编号,+1 后面跟上4个0
如:0000
-m = 0x00010000
-l XLOGFILE force minimum WAL starting location for new transaction log
找到pg_wal下面最新的日志文件,编号+1,然后分别去时间线、高32位、低32位:
如:000000010000000000000002
那么最新的日志文件就是000000010000000000000003
那么参数为:
-l 000000010000000000000003
2.执行恢复:
1)touch pg_control
2)pg_resetxlog -x 0x000100000 -m 0x00010000 -O 0x00010000 -l 000000010000000000000003 -f $PGDATA
当然,-m参数如果报错,也可以不要:
pg_resetxlog -x 0x000100000 -O 0x00010000 -l 000000010000000000000003 -f ¥PGDATA
二、安全清理不必要的日志文件:
1)cd $PGDATA/pg_xlog/
2)pg_ctl stop -D $PGDATA -m fast
3)pg_controldata记录清理前的信息,并记录:NextXID NextOID给下面使用
4)pg_resetxlog -o 24584 -x 1745 -f $PGDATA
5)查看清理后大小
du -sh
三、参数详解:
-x: A safe value for the next transaction ID (-x) can be determined by looking for the numerically largest file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note that the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal too. For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide the proper multiplier). -m: A safe value for the next multitransaction ID (-m) can be determined by looking for the numerically largest file name in the directory pg_multixact/offsets under the data directory, adding one,and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the option value in hexadecimal and add four zeroes -O: A safe value for the next multitransaction offset (-O) can be determined by looking for the numerically largest file name in the directory pg_multixact/members under the data directory, adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the option value in hexadecimal and add four zeroes -l: The WAL starting address (-l) should be larger than any WAL segment file name currently existing in the directory pg_xlog under the data directory. These names are also in hexadecimal and have three parts. The first part is the “timeline ID” and should usually be kept the same. Do not choose a value larger than 255 (0xFF) for the third part; instead increment the second part and reset the third part to 0. For example, if 00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will work; but if the largest entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.
https://www.postgresql.org/docs/12/app-pgresetwal.html
pg_resetxlog、pg_resetwal工具的两个用法
原文:https://www.cnblogs.com/kuang17/p/13208204.html