Re: BUG #11603: replication, pg_basebackup and high load

On 2014-10-09 20:00:39 +0300, Heikki Linnakangas wrote:
> On 10/09/2014 07:54 PM, Andres Freund wrote:
> >On 2014-10-08 13:19:27 +0000, mdglange@gmail.com wrote:
> >>The test I did involved the following: a master database with two slaves. On
> >>the master two replication slots have been configured as per the
> >>documentation. One slave active before I put some "heavy" load (the
> >>environment is scaled such, that inserting a few gigabytes of insert
> >>statements is a heavy load. This is on purpose)
> >
> >Replication slots currently only reserve resources after they've been
> >used the first time. I.e. when you create a physical replication slot it
> >doesn't immediately reserve resources - a client needs to connect to it
> >once, telling it from where on to reserve resources.
>
> Oh, now I understand what Michiel was trying to do with the replication
> slots. The idea is to prevent the master from recycling the segments while
> the backup runs, by creating a replication slot before the backup.

Yes, that's how I understand it.

> >You can see the slot's reserved resources in the pg_replication_slots
> >view.
> >
> >So, what you could do is to connect to the slots once, for a short time,
> >using pg_receivexlog --slots. Or just use the -X stream method for
> >pg_basebackup.
>
> Hmm. Should we have an additional flag to "pg_basebackup -R" to create and
> "reserve" a replication slot, too, all in one command?  That would be handy,
> although there's some potential of shooting your foot with replication
> slots; if the backup is aborted for some reason, the slot remains.

I'd very much like to do that, but I think we need to build in some
defenses against the footgun. I unfortunately ran out of time to
implement it.

There now exists the concept of a 'ephemeral' replication slot. Such
slots are dropped upon release/error. I'm not entirely sure yet how to
string that together with pg_basebackup, but I think it should be
possible.

The easiest way would probably to add a SLOT parameter to BASE_BACKUP
that creates the slot, marks it as ephemeral for the duration, and only
persists it at the end. The problem is that that still leaves a small
window at the end when the server has finished the base backup, but the
client fails before finishing.
There's also the question how we want to make it cooperate with the
forked process that receives WAL. It's a reasonable wish to want it to
use the slot so resources are only reserved as much as necessary...

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services