PostgreSQL11修改wal-segsize大小
Tags： PGwal-segsizewal日志体系结构
简介
编译安装PG的一些参数可以参考：https://www.xmmup.com/dbbao68shiyongyuanmabianyilaianzhuangpostgresql13-3shujuku.html
PostgreSQL11以前改变wal-segsize(WAL段大小)必须在编译时通过–with-wal-segsize参数确定，编译后不可更改。
PostgreSQL11之前的版本
- 数据块按8K划分(--with-blocksize,单位为K,默认为8K)
- WAL块尺寸按8k划分(--with-wal-blocksize,单位为K,默认为8K)
- 数据文件按1G一个分解(--with-segsize,单位为G,默认为1G)
- WAL段尺寸按16MB划分(--with-wal-segsize,单位为K,默认为16MB)
PostgreSQL 11 版本的一个重要调整是支持 initdb 和 pg_resetwal 修改 WAL 文件大小，而 11 版本之前只能在编译安装 PostgreSQL 时设置 WAL 文件大小。这一特性能够方便 WAL 文件的管理。PostgreSQL11取消了编译参数–with-wal-segsize。
使用pg_resetwal(–wal-segsize)更改WAL段大小
PostgreSQL11先编译程序
- 数据块按8K划分(--with-blocksize,单位为K,默认为8K)
- WAL块尺寸按8k划分(--with-wal-blocksize,单位为K,默认为8K)
- 数据文件按1G一个分解(--with-segsize,单位为G,默认为1G)
编译完成并初始化后使用pg_resetwal修改wal-segsize
1 2 3 4 5 6 | #首先停止PostgreSQL服务 pg_ctl -D /data/pgdata stop -m fast #修改wal-segsize段大小,默认单位是MB,值必须是2的幂并且在1到1024MB之间 pg_resetwal --wal-segsize=32 /data/pgdata |
使用initdb时更改WAL段大小
1 | initdb --wal-segsize =< MB中的大小> [其他选项] |
initdb的参数--wal-segsize参数的取值范围是1MB到1024MB
查看
1 2 3 4 5 6 7 | show block_size; show wal_block_size; show segment_size; show wal_segment_size; pg_controldata |grep -i wal ls /data/pgdata/pg_wal -lh |
1 2 3 4 5 6 7 | [pg1110@lhr ~]$ pg_controldata |grep -i wal Latest checkpoint's REDO WAL file: 000000010000000000000038 wal_level setting: replica wal_log_hints setting: off WAL block size: 8192 Bytes per WAL segment: 16777216 ----->>>>此处说明wal segment的大小是16MB |
示例
使用 initdb 调整WAL文件大小
1 2 3 4 5 6 7 8 9 10 11 12 | [pg1110@lhr ~]$ initdb --help|grep -C 4 wal-seg default text search configuration -U, --username=NAME database superuser name -W, --pwprompt prompt for a password for the new superuser -X, --waldir=WALDIR location for the write-ahead log directory --wal-segsize=SIZE size of WAL segments, in megabytes ----->>>此处！！！ Less commonly used options: -d, --debug generate lots of debugging output -k, --data-checksums use data page checksums [pg1110@lhr ~]$ |
initdb 命令关于修改 WAL 文件大小选项，如下:
1 | --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.
WAL 日志文件大小默认为16MB，该值必须是1到1024之间的2的次方，增大WAL文件大小能够减少WAL日志文件的产生。
初始化一个新的 PostgreSQL 数据库实例，指定WAL文件大小64MB，如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [pg11@lhrpg ~]$ initdb -E UTF8 --locale=C --wal-segsize=64 -D /home/pg11/data01 -U postgres -W The files belonging to this database system will be owned by user "pg11". This user must also own the server process. The database cluster will be initialized with locale "C". The default text search configuration will be set to "english". Data page checksums are disabled. Enter new superuser password: Enter it again: creating directory /home/pg11/data01 ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /home/pg11/data01 -l logfile start |
修改 postgresql.conf 相关配置，之后启动数据库。
1 2 3 4 5 6 7 8 | [pg11@lhrpg data01]$ pg_ctl start -D /home/pg11/data01 waiting for server to start....2018-10-16 15:58:16.714 CST [10583] LOG: listening on IPv6 address "::1", port 1950 2018-10-16 15:58:16.714 CST [10583] LOG: listening on IPv4 address "127.0.0.1", port 1950 2018-10-16 15:58:16.731 CST [10583] LOG: listening on Unix socket "/tmp/.s.PGSQL.1950" 2018-10-16 15:58:16.762 CST [10584] LOG: database system was shut down at 2018-10-16 15:56:46 CST 2018-10-16 15:58:16.782 CST [10583] LOG: database system is ready to accept connections done server started |
验证WAL文件大小，如下:
1 2 3 4 | [pg11@lhrpg ~]$ ll /home/pg11/data01/pg_wal total 65M -rw------- 1 pg11 pg11 64M Oct 16 16:03 000000010000000000000001 drwx------ 2 pg11 pg11 4.0K Oct 16 15:56 archive_status |
可见WAL文件大小为64MB。
使用 pg_resetwal 调整WAL文件大小
pg_resetwal 用来重置WAL日志和一些控制信息，常用于数据库恢复场景，不到万不得已不轻易使用，以下演示使用pg_resetwal命令调整WAL日志文件大小，仅供测试参考，生产环境慎用。
pg_resetwal 命令关于调整WAL文件大小的选项，如下:
1 | --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.
以下演示在已有PostgreSQL实例基础上调整WAL日志文件大小。
查看当前数据库的 pg_wal 目录，如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [pg11@lhrpg pg_wal]$ ll /database/pg11/pg_root/pg_wal/ total 2.3G -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000013 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000014 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000015 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000016 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000017 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000018 -rw------- 1 pg11 pg11 16M Sep 30 14:45 000000010000001700000019 -rw------- 1 pg11 pg11 16M Sep 30 14:45 00000001000000170000001A -rw------- 1 pg11 pg11 16M Sep 30 14:45 00000001000000170000001B ... 省略 drwx------ 2 pg11 pg11 16K Oct 16 08:38 archive_status |
pg_wal 目录中已有大量WAL日志文件，WAL文件大小为16MB，计划将WAL日志文件调整成64MB。
pg_resetwal 操作时需要关闭数据库，如下。
1 2 3 | [pg11@lhrpg ~]$ pg_ctl stop -m fast waiting for server to shut down.... done server stopped |
pg_resetwal 命令调整WAL日志文件大小为 64MB:
1 2 3 | [pg11@lhrpg ~]$ pg_resetwal --wal-segsize=64 -D /database/pg11/pg_root Write-ahead log reset |
验证WAL文件大小,如下:
1 2 3 4 | [pg11@lhrpg ~]$ ll /database/pg11/pg_root/pg_wal/ total 65M -rw------- 1 pg11 pg11 64M Oct 16 08:55 000000010000001700000029 drwx------ 2 pg11 pg11 16K Oct 16 08:55 archive_status |
发现 pg_wal 目录中原有的WAL日志被清理，同时生成了大小为64MB新的WAL文件。
启动数据库提示 min_wal_size 参数至少需设置成 wal_segment_size 大小为 2 倍。
1 2 3 4 5 6 | [pg11@lhrpg ~]$ pg_ctl start waiting for server to start....2018-10-16 09:01:26.096 CST [24318] FATAL: "min_wal_size" must be at least twice "wal_segment_size". 2018-10-16 09:01:26.096 CST [24318] LOG: database system is shut down stopped waiting pg_ctl: could not start server Examine the log output. |
根据提示调整 postgresql.conf，设置如下：
1 | min_wal_size = 128MB |
启动数据库正常，如下:
1 2 3 4 5 6 | [pg11@lhrpg ~]$ pg_ctl start waiting for server to start....2018-10-16 09:02:45.680 CST [24614] LOG: listening on IPv4 address "0.0.0.0", port 1930 2018-10-16 09:02:45.680 CST [24614] LOG: listening on IPv6 address "::", port 1930 2018-10-16 09:02:45.687 CST [24614] LOG: listening on Unix socket "/tmp/.s.PGSQL.1930" 2018-10-16 09:02:45.715 CST [24614] LOG: redirecting log output to logging collector process 2018-10-16 09:02:45.715 CST [24614] HINT: Future log output will appear in directory "log". |
总结
以上演示了 11 版本通过 initdb 和 pg_resetwal 调整WAL文件大小。
pg_resetwal 会清除pg_wal目录的WAL文件，生产环境使用需慎重。
官网之pg_resetwal
pg_resetwal — 重置一个PostgreSQL数据库集簇的预写式日志以及其他控制信息
大纲
pg_resetwal [ --force | -f ] [ --dry-run | -n ] [option...] [ --pgdata | -D ] datadir
描述
pg_resetwal会清除预写式日志（WAL）并且有选择地重置存储在pg_control文件中的一些其他控制信息。如果这些文件已经被损坏，某些时候就需要这个功能。当服务器由于这样的损坏而无法启动时，这只应该被用作最后的手段。
在运行这个命令之后，就可能可以启动服务器，但是记住数据库可能包含由于部分提交事务产生的不一致数据。你应当立刻转储你的数据、运行initdb并且重新载入。重新载入后，检查不一致并且根据需要修复之。
这个工具只能被安装服务器的用户运行，因为它要求对数据目录的读写访问。出于安全原因，你必须在命令行中指定数据目录。pg_resetwal不使用环境变量PGDATA。
如果pg_resetwal抱怨它无法为pg_control决定合法数据，你可以通过指定-f（强制）选项强制它继续。在这种情况下，丢失的数据将被替换为看似合理的值。可以期望大部分域是匹配的，但是下一个 OID、下一个事务 ID 和纪元、下一个多事务 ID 和偏移以及 WAL 开始位置域可能还是需要人工协助。这些域可以使用下面讨论的选项设置。如果你不能为所有这些域决定正确的值，-f还是可以被使用，但是恢复的数据库还是值得怀疑：一次立即的转储和重新载入是势在必行的。在你转储之前不要\在该数据库中执行任何数据修改操作，因为任何这样的动作都可能使破坏更严重。
选项
-f--force即使
pg_resetwal无法从pg_control中确定有效的数据（如前面所解释的），也强迫pg_resetwal继续运行。-n--dry-run-n/--dry-run选项指示pg_resetwal打印从pg_control重构出来的值以及要被改变的值，然后不修改任何东西退出。这主要是一个调试工具，但是可以用来在允许pg_resetwal真正执行下去之前进行完整性检查。-V--version显示版本信息然后退出。
-?--help显示帮助然后退出。
只有当pg_resetwal无法通过读取pg_control确定合适的值时，才需要下列选项。安全值可以按下文所述来确定。对于接收数字参数的值，可以使用前缀0x指定 16 进制值。
-c *xid*,*xid*--commit-timestamp-ids=*xid*,*xid*手工设置提交时间可以检索到的最老的和最新的事务 ID。能检索到提交时间的最老事务 ID 的安全值（第一部分）可以通过在数据目录下
pg_commit_ts目录中数字上最小的文件名来决定。反过来，能检索到提交时间的最新事务 ID 的安全值（第二部分）可以通过同一个目录中数字上最大的文件名来决定。文件名都是十六进制的。-e *xid_epoch*--epoch=*xid_epoch*手工设置下一个事务 ID 的 epoch。事务 ID 的 epoch 实际上并没有存储在数据库中的任何地方，除了被
pg_resetwal设置在这个域中，所以只要关心的是数据库本身，任何值都可以用。你可能需要调整这个值来确保诸如Slony-I和Skytools之类的复制系统正确地工作 — 如果确实需要调整，应该可以从下游的复制数据库的状态中获得一个合适的值。-l *walfile*--next-wal-file=*walfile*通过指定下一个WAL段文件名称来手工设置WAL开始位置。下一个WAL段文件的名称应该比当前存在于数据目录下
pg_wal目录中的任意 WAL 段文件名更大。这些名称也是十六进制的并且有三个部分。第一部分是“时间线 ID”并且通常应该被保持相同。例如，如果00000001000000320000004A是pg_wal中最大的项，则使用-l 00000001000000320000004B或更高的值。注意在使用非默认WAL段尺寸时，WAL文件名中的数字与系统函数和系统视图报告的LSN不同。这个选项要的是WAL文件名而不是LSN。注意pg_resetwal本身查看pg_wal中的文件并选择一个超出最新现存文件名的默认-l设置。因此，只有当你知道 WAL 段文件当前不在pg_wal中时，或者当pg_wal的内容完全丢失时，才需要对-l的手工调整，例如一个离线归档中的项。-m *mxid*,*mxid*--multixact-ids=*mxid*,*mxid*手工设置下一个和最老的多事务 ID。确定下一个多事务 ID（第一部分）的安全值的方法：在数据目录下的
pg_multixact/offsets目录中查找最大的数字文件名，然后在它的基础上加一并且乘以 65536 (0x10000)。反过来，确定最老的多事务 ID（-m的第二部分）的方法：在同一个目录中查找最小的数字文件名并且乘以 65536。文件名是十六进制的数字，因此实现上述方法最简单的方式是以十六进制指定选项值并且追加四个零。-o *oid*--next-oid=*oid*手工设置下一个 OID。没有相对容易的方法来决定超过数据库中最大 OID 的下一个 OID。但幸运的是正确地得到下一个 OID 设置并不是决定性的。
-O *mxoff*--multixact-offset=*mxoff*手工设置下一个多事务偏移量。确定安全值的方法：查找数据目录下
pg_multixact/members目录中最大的数字文件名，然后在它的基础上加一并且乘以 52352 (0xCC80)。文件名是十六进制数字。没有像其他选项那样追加零的简单方法。--wal-segsize=*wal_segment_size*设置新的WAL段尺寸，以兆字节为单位。这个值必须被设为2的1次幂和1024次幂（兆字节）之间。更多信息请参考initdb的相同选项。注意虽然
pg_resetwal将把WAL起始地址设置成超过最新的现有WAL段文件，但一些段尺寸的改变可能导致之前的WAL文件名被重用。如果WAL文件名重叠会导致归档策略出现问题，推荐把-l和这个选项一起使用来手动设置WAL起始地址。-x *xid*--next-transaction-id=*xid*手工设置下一个事务 ID。确定安全值的方法：在数据目录下的
pg_xact目录中查找最大的数字文件名，然后在它的基础上加一并且乘以 1048576 (0x100000)。注意文件名是十六进制的数字。通常以十六进制的形式指定该选项值也是最容易的。例如，如果0011是pg_xact中的最大项，-x 0x1200000就可以（五个尾部的零就表示了前面说的乘数）。
Environment
PG_COLOR规定在诊断消息中是否使用颜色。可能的值为
always、auto、never。
注解
这个命令不能在服务器正在运行时被使用。如果在数据目录中发现一个服务器锁文件，pg_resetwal将拒绝启动。如果服务器崩溃那么一个锁文件可能会被留下，在那种情况下你能移除该锁文件来让pg_resetwal运行。但是在你那样做之前，再次确认没有服务器进程仍然存活。
pg_resetwal仅能在具有相同主版本的服务器上使用。
