Re: WAL archive space planning?

On Tue, Feb 21, 2017 at 12:22 PM, Ray Stell <stellr(at)vt(dot)edu> wrote:

> On 2/21/17 12:09 AM, Steven Chang wrote:
>
> check this, his 2nd part introduces Stream Replication Implementation and
> tell you px_log retention and wal archive related parameters.
> https://www.itenlight.com/blog/2016/05/19/PostgreSQL+HA+with
> +pgpool-II+-+Part+2
>
> 2017-02-18 2:30 GMT+08:00 Ray Stell <stellr(at)vt(dot)edu>:
>
>> 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?
>>
>> TIA!
>>
>>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
> I'm interested in why I might observed a dramatic difference between
> pg_xlog files and the archive target files. Other dbs I see have them
> pretty much one-to-one, but not in this case here. What might cause the
> large variation? I got only a few pg_xlogs files each minute and 42
> archive files/min.
>

Ray,

Did you see my previous response about the archive_timeout setting?