Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?
The reason for wanting to increase it from 16 MB is to increase the speed of transferring WAL backups when using WAL-E. 16 MB WAL files were transferring at a speed of about 20 MB/s (using SSDs in striped mirrored pairs) which is really slow for database sizes larger than 1 TB.
After increasing the WAL segment size, do we typically want to change the values for min_wal_size and max_wal_size?
Best Answer
After shutting down the server cleanly, you can use pg_resetwal to change the wal segment size. However, I think that doing so will invalidate any streaming replicas you have set up, as well as create a discontinuity in any wal archive you might have, and so force a new base backup.
I don't think I would do this myself. I would just upgrade to v12, and create the new server with the desired size. Why create two discontinuities when you can have just one?
min_wal_size needs to be at least twice the new segment size, so if it isn't already that high you will have to change it. Other than that, I don't see a reason to change them unless they weren't optimal to start with.