Modification is risky and act with caution.
Previously, changing wal-segsize (WAL segment size) must be determined through the --with-wal-segsize parameter at compile time, and cannot be changed after compilation.
#PostgreSQL11 versions before#Data blocks are divided by 8K (--with-blocksize, unit is K, default is 8K)#WAL block size is divided by 8k (--with-wal-blocksize, unit is K, default is 8K)#The data file is decomposed by 1G (--with-segsize, unit is G, default is 1G)#WAL segment size is divided into 16MB (--with-wal-segsize, unit is K, default is 16MB)./configure --with-blocksize=8--with-wal-blocksize=8--with-segsize=1 --with-wal-segsize=16
PostgreSQL11 canceled the compilation parameter – with-wal-segsize. Use pg_resetwal(-wal-segsize) to change the WAL segment size.
#PostgreSQL11 Compile the program first#Data blocks are divided by 8K (--with-blocksize, unit is K, default is 8K)#WAL block size is divided by 8k (--with-wal-blocksize, unit is K, default is 8K)#The data file is decomposed by 1G (--with-segsize, unit is G, default is 1G)./configure --with-blocksize=8--with-wal-blocksize=8--with-segsize=1
After compiling and initializing, use pg_resetwal to modify wal-segsize
#Stop PostgreSQL service firstpg_ctl -D /data/pgdata stop -m fast #Modify the wal-segsize segment size, the default unit is MB, the value must be a power of 2 and be between 1 and 1024MBpg_resetwal --wal-segsize=32 /data/pgdata;
Check
show block_size; show wal_block_size; show segment_size; show wal_segment_size; ls /data/pgdata/pg_wal -lh
Supplement: Interpretation of new features of PostgreSQL 11: Initdb/Pg_resetwal supports modifying WAL file size
An important tweak to the PostgreSQL version 11 is the support for initdb and pg_resetwal to modify the WAL file size, while before version 11, the WAL file size could only be set when compiling and installing PostgreSQL. This feature facilitates the management of WAL files.
Release instructions
Allow the WAL file size to be set via initdb (Beena Emerson)
Previously the 16MB default could only be changed at compile time.
The following demonstrates modifying the WAL file size through initdb and pg_resetwal respectively.
Use initdb to resize WAL file
The initdb command has the following options for modifying the WAL file size:
--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.
The default WAL log file size is 16MB, and this value must be at the power of 2 between 1 and 1024. Increasing the WAL file size can reduce the generation of WAL log files.
Initialize a new PostgreSQL DB instance, specify the WAL file size 64MB, as follows:
[pg11@pghost2 ~]$ 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
Modify the relevant configuration and start the database.
[pg11@pghost2 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/..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
Verify the WAL file size as follows:
[pg11@pghost2 ~]$ 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
The WAL file size is 64MB.
Use pg_resetwal to resize WAL file
pg_resetwal is used to reset WAL logs and some control information. It is often used in database recovery scenarios. It is not easy to use unless it is necessary. The following demonstration uses the pg_resetwal command to adjust the size of the WAL log file. It is for testing reference only. Use it with caution in the production environment.
The pg_resetwal command has the options for adjusting the WAL file size, as follows:
--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.
The following demonstrates adjusting the WAL log file size based on existing PostgreSQL instances.
Check the pg_wal directory of the current database, as follows:
[pg11@pghost2 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 ... Omitted drwx------ 2 pg11 pg11 16K Oct 16 08:38 archive_status
There are already a large number of WAL log files in the pg_wal directory, with the WAL file size of 16MB. It is planned to adjust the WAL log file to 64MB.
The database needs to be closed during the pg_resetwal operation, as follows.
[pg11@pghost2 ~]$ pg_ctl stop -m fast waiting for server to shut down.... done server stopped pg_resetwal Command adjustmentWALThe log file size is 64MB: [pg11@pghost2 ~]$ pg_resetwal --wal-segsize=64 -D /database/pg11/pg_root Write-ahead log reset
Verify the WAL file size as follows:
[pg11@pghost2 ~]$ 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
It was found that the original WAL log in the pg_wal directory was cleaned up, and a new WAL file with a size of 64MB was generated.
When starting the database, prompt the min_wal_size parameter to be set to at least 2 times the wal_segment_size size.
[pg11@pghost2 ~]$ 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.
Adjust according to the prompts, the settings are as follows:
min_wal_size = 128MB
Starting the database is normal, as follows:
[pg11@pghost2 ~]$ 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/..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".
Summarize
The above demonstrates that version 11 resizes the WAL file through initdb and pg_resetwal.
pg_resetwal will clear the WAL file in the pg_wal directory. The test samples of this blog are for reference only. Use the production environment with caution.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.