Re: Replication: How to query current segments allocation relative to "Wal keep segments"?

On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter
<edsonrichter(at)hotmail(dot)com> wrote:
> Would this kind of count being recorded somewhere else?
> How does the server knows that the wal_segments have been exhausted?
You should evaluate the amount of wal_keep_segments necessary using
the replication lag in terms of WAL position differences (LSN) between
the master and its slaves. pg_stat_replication gives you the WAL
position (LSN) up to where each slave has received WAL information.
Combine it with pg_current_xlog_location() to determine what is the
current location master is writing WAL and you can evaluate the number
of WAL files that need to be retained on master. Knowing that each WAL
file is normally 16MB, simply use pg_xlog_location_diff to calculate
the WAL lag as a difference of bytes (for 9.4 a simple difference
operation is possible with the data type pg_lsn), and then guess from
it the number of WAL files that are actually necessary.

If you care that much about WAL retention btw, consider using
replication slots with 9.4, just be careful to monitor the partition
where pg_xlog sits in.
--
Michael