Re: WAL archive space planning?

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
> <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
> <mailto: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
> <mailto:pgsql-admin(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
> <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.