pg调整wal_segment_size(默认是16MB)大小

环境:
OS:Centos 7
DB:pg14

pg默认的wal_segment_size是16MB,下面可以通过如下命令修改

 

1.关闭数据库
systemctl stop postgresql-14

 

2.修改wal默认大小
[root@dsc1 bin]# /usr/pgsql-14/bin/pg_resetwal --wal-segsize=64 -D /opt/pg14/data
pg_resetwal: error: cannot be executed by "root"
pg_resetwal: You must run pg_resetwal as the PostgreSQL superuser.

 

切换到postgres用户下执行
[root@dsc1 ~]# su - postgres
-bash-4.2$ /usr/pgsql-14/bin/pg_resetwal --wal-segsize=64 -D /opt/pg14/data
Write-ahead log reset

 

执行命令后会清空之前的wal日志,重新生成大小为64MB的日志
[root@dsc1 pg_wal]# ls -al
total 65540
drwx------. 3 postgres postgres 60 May 22 15:41 .
drwx------. 19 postgres postgres 4096 May 22 15:43 ..
-rw-------. 1 postgres postgres 67108864 May 22 15:43 000000010000000000000001
drwx------. 2 postgres postgres 6 May 22 15:41 archive_status

3.设置 min_wal_size
min_wal_size 参数至少需设置成 wal_segment_size 大小为2倍.
min_wal_size=128

4.启动数据库
systemctl start postgresql-14

5.登录查看
-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 14.11)
Type "help" for help.

postgres=# show wal_segment_size;
wal_segment_size
------------------
64MB
(1 row)

 

有主从关系的环境,从库会报如下错误:
2024-05-22 16:31:11 CST [17300]: [2-1] user=,db=,app=,client=FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000001 has already been removed

这种情况只能是重新部署从库.