Thread: [HACKERS] Function to control physical replication slot
Is there a particular reason we don't have a function to *set* the restart_lsn of a replication slot, other than to drop it and recreate it?
Similarly, to create one with a fixed value for restart_lsn and not necessarily the one the machine is at right now?
Basically I'm doing a small script that keeps a replication slot on the standby reasonably in sync with the master, for use in the case of a failover.
AFAICT the only way to do it now is to either connect with the replication protocol and actually consume the data (which seems quite wasteful), or to drop and recreate the slot (in which case we can't actually determine the location it gets set to keep, because it will be set to "whatever is current").
--
On 4/11/17 05:15, Magnus Hagander wrote: > Is there a particular reason we don't have a function to *set* the > restart_lsn of a replication slot, other than to drop it and recreate it? I suppose there could be lots of problems if the LSN you specify isn't valid. And it might be hard to determine whether a given LSN is valid. If this could be made to work, this would actually offer an interesting option for dumping and restoring subscriptions. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2017-04-12 20:15:52 -0400, Peter Eisentraut wrote: > On 4/11/17 05:15, Magnus Hagander wrote: > > Is there a particular reason we don't have a function to *set* the > > restart_lsn of a replication slot, other than to drop it and recreate it? > > I suppose there could be lots of problems if the LSN you specify isn't > valid. And it might be hard to determine whether a given LSN is valid. As long as we're only talking about the LSN of a physical slot (and not the xmin) I'm not sure it's that important that it's valid, as long as it's not in the future. But we could otherwise pretty easily assert that the new value has to be old_value <= new_value <= GetRedoRecPtr()/GetFlushRecPtr(). That should be sufficient for both of your use-cases afaics? - Andres
On Thu, Apr 13, 2017 at 2:40 AM, Andres Freund <andres@anarazel.de> wrote:
On 2017-04-12 20:15:52 -0400, Peter Eisentraut wrote:
> On 4/11/17 05:15, Magnus Hagander wrote:
> > Is there a particular reason we don't have a function to *set* the
> > restart_lsn of a replication slot, other than to drop it and recreate it?
>
> I suppose there could be lots of problems if the LSN you specify isn't
> valid. And it might be hard to determine whether a given LSN is valid.
As long as we're only talking about the LSN of a physical slot (and not
the xmin) I'm not sure it's that important that it's valid, as long as
it's not in the future. But we could otherwise pretty easily assert
that the new value has to be old_value <= new_value <=
GetRedoRecPtr()/GetFlushRecPtr(). That should be sufficient for both of
your use-cases afaics?
Yes, I think making that restriction falls well within my requirements -- move it only forward, and not past the end of the current position.
One could argue that a reasonable thing to do when trying to move past the current position would be to just "truncate" it to the current position, instead of throwing an error. But that could also be done in userspace using CASE on the parameter I guess. Not sure which is best. Any opinions on that?