On Fri, Feb 17, 2017 at 1:30 PM, Ray Stell <stellr@vt.edu> wrote:
I was "planning" to turn on WAL archiving on a postgresql 9.4.11 server that currently is running with "wal_level=hot_standby" and streaming to a standby. I thought there would be a relationship between the rate of pg_xlog files and archive generation. When I turned up the archive_command/mode I found the scale of the archive target was wrong as I had based it on the pg_xlog file creation rate.
When I turned on the archive command for a few minutes, pg_xlog dir contained these files for the time period:
-rw------- 1 postgres postgres 16777216 Feb 17 06:47 00000001000023AC0000007F -rw------- 1 postgres postgres 16777216 Feb 17 06:45 00000001000023AC0000007E
The archive command wrote 126, 16MB files:
-rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000056 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000055 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000054 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000053 ...
-rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DC -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DB -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DA -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000D9
On servers that are not nearly as busy, I observe a one-to-one relationship between these files/rates.
Is there a good WAL archive space planning guide?
Is the way to collect planning data for this to turn on wal_debug?
Did you have an archive_timeout value set? This will cause the archive_command to run and create a WAL file at that time interval, no matter how little write activity there may be. These will always be 16MB as well, but if there's no activity, they will compress down quite significantly.
If you disable the timeout, I believe you will then get the matched rate of pg_xlog generation for your archive command.