Re: pg_replication_slots

On Wed, Nov 29, 2017 at 12:39 PM, Nicola Contu <nicola(dot)contu(at)gmail(dot)com> wrote:
> Hello,
Hi!

> I just have few questions on the replication slots :
>
> - is it possible to get size of the slot?
I use something like this to know where my slot is at:
SELECT pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn)
FROM pg_replication_slots;

pg_current_xlog_location() will give you the actual log location in
master, and you can get all info about a slot in the
pg_replication_slots relation. In this case we query for restart_lsn
which has the slot restart location. pg_xlog_location_diff() is a
convenient function which returns the difference between two
transaction log locations (pg_lsn type). pg_lsn represent byte
locations, so the number returned by this function is the difference
in bytes from the current log position and slot restart location, the
most approximate value for what a slot size would be.

> - if the slave is down, the table grows, when the slave comes up again, will
> the table be flushed after pushing wals?

Yes. Actually, checkpoints should continue to be created in the
master, only difference is that WAL segments are kept at least from
the slot restart location onward. Once your slave is back up, and all
WALs have been transfered, old segments will be deleted.

> - will they impact performances on the master?

Slots themselves shouldn't have any appreciable impact on performance.
And unless your slave is down for a long period of time, or it's
configured wrongly so it doesn't use the slot, or your master server
is very restricted in disk space, you shouldn't have any problem, and
you'll see it coming before it affects the system. Although, you might
have the issues common to augmenting the WAL level, namely increased
IO. A good practice is to store your WAL files in a separate disk to
be able to take advantage of parallel IO ops. But this shouldn't even
be necessary unless you note something.

Also not related to slots themselves, streaming replication has the
aggregated impact of transferring WALs across the net. Again, this
isn't usually an issue, specially if you use asynchronous replication.

> I'm just worried about the size.

If your slave is fast enough to keep up with master you shouldn't even
note there's replication going on. Just in case, I have a check that
runs the query in the answer to the first question every a couple of
minutes and sends me a notification if it gets too high. 99.99% of the
time is zero, but we did have instances where the slave went down and
the disk on master started to get filled. Just watch out for those
cases, and in an emergency you can just delete the slot and all the
WAL segments will be reclaimed seamlessly.

> Thank you

Hope the answers are useful.

Regards,
Ema