PostgreSQL 11 preview - pg_resetwal 在线修改 WAL segment size
背景
PostgreSQL 11 允许用户在线修改WAL segment size。默认为16MB， 以往的版本，我们必须重新编译PG，并且不同–wal-segsize设置的postgresql软件相互不兼容。
Allow pg_resetwal to change the WAL segment size via –wal-segsize (Nathan Bossart)
--wal-segsize=wal_segment_size
Set the new WAL segment size, in megabytes. The value must be set to a power of 2 between 1 and 1024 (megabytes). See the same option of initdb for more information.
Note
While pg_resetwal will set the WAL starting address beyond the latest existing WAL segment file, some segment size changes can cause previous WAL file names to be reused. It is recommended to use -l together with this option to manually set the WAL starting address if WAL file name overlap will cause problems with your archiving strategy.
当改变wal segment size时，建议与-l参数一起使用，设置下一个wal file的名字，防止重复使用之前的名字。
-l walfile
--next-wal-file=walfile
Manually set the WAL starting location by specifying the name of the next WAL segment file.
The name of next WAL segment file should be larger than any WAL segment file name currently existing in the directory pg_wal 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. For example, if 00000001000000320000004A is the largest entry in pg_wal, use -l 00000001000000320000004B or higher.
Note that when using nondefault WAL segment sizes, the numbers in the WAL file names are different from the LSNs that are reported by system functions and system views. This option takes a WAL file name, not an LSN.
Note
pg_resetwal itself looks at the files in pg_wal and chooses a default -l setting beyond the last existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL segment files that are not currently present in pg_wal, such as entries in an offline archive; or if the contents of pg_wal have been lost entirely.
同时PostgreSQL 11也允许你在initdb时设置wal segment size。
https://www.postgresql.org/docs/devel/static/app-initdb.html
--wal-segsize=size
Set the WAL segment size, in megabytes. This is the size of each individual file in the WAL log. The default size is 16 megabytes. The value must be a power of 2 between 1 and 1024 (megabytes). This option can only be set during initialization, and cannot be changed later.
It may be useful to adjust this size to control the granularity of WAL log shipping or archiving. Also, in databases with a high volume of WAL, the sheer number of WAL files per directory can become a performance and management problem. Increasing the WAL file size will reduce the number of WAL files.