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

On 05-01-2015 10:02, Michael Paquier wrote:
> On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter
> <edsonrichter@hotmail.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.
Despite being a completely valid statement, I've two contrary thoughts
about it:

1) I cannot migrate production servers at my free will. It requires long
planning, and probably will happen only in one or two years from now
(year end 2015 or 2016)
2) I do prefer to monitor how much wal segments I'm really using (and in
need), and then fix them up to 25% above this limit, than giving a
chance to blow my disk space (I mean, is preferable to stop replication
than put in risk whole database because of disk space)

Thanks,

Edson