Re: Recovery control functions

Functions to control recovery, to aid PITR and Hot Standby.
pg_is_xlog_replay_paused()
pg_xlog_replay_pause()
pg_xlog_replay_resume()

recovery.conf parameter: pause_at_recovery_target (bool)

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2011-01-14 at 11:09 +0000, Simon Riggs wrote:
> Functions to control recovery, to aid PITR and Hot Standby.
> pg_is_xlog_replay_paused()
> pg_xlog_replay_pause()
> pg_xlog_replay_resume()
>
> recovery.conf parameter: pause_at_recovery_target (bool)

And now with the correct patch.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Jan 14, 2011 at 12:15, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2011-01-14 at 11:09 +0000, Simon Riggs wrote:
>> Functions to control recovery, to aid PITR and Hot Standby.
>> pg_is_xlog_replay_paused()
>> pg_xlog_replay_pause()
>> pg_xlog_replay_resume()
>>
>> recovery.conf parameter: pause_at_recovery_target (bool)

Awesome, I've been waiting for these! :-)

How hard would it be to have a pg_xlog_replay_until(<xlog location or
timestamp>), to have it resume recovery up to that point and then
pause again?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 14.01.2011 13:15, Simon Riggs wrote:
> /*
> + * Recheck shared recoveryPause by polling.
> + *
> + * XXX It might seem we should do this via a shared Latch, but
> + * currently we only support one shared latch per process and
> + * that is already taken for Startup process. Polling is used
> + * in other places in xlog.c already, so not a concern.
> + */

There is no such limitation with latches.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2011-01-14 at 12:41 +0100, Magnus Hagander wrote:
> On Fri, Jan 14, 2011 at 12:15, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On Fri, 2011-01-14 at 11:09 +0000, Simon Riggs wrote:
> >> Functions to control recovery, to aid PITR and Hot Standby.
> >> pg_is_xlog_replay_paused()
> >> pg_xlog_replay_pause()
> >> pg_xlog_replay_resume()
> >>
> >> recovery.conf parameter: pause_at_recovery_target (bool)
>
>
> Awesome, I've been waiting for these! :-)
>
> How hard would it be to have a pg_xlog_replay_until(<xlog location or
> timestamp>), to have it resume recovery up to that point and then
> pause again?

You can already do that for timestamps.

What you can't do is dynamically set recovery targets via functions,
since currently that is set via recovery.conf parameters. Which requires
restart.

Jaime has a separate patch about recovery targets as well, which does
some more of what you want.

Some things are straightforward, some things require overhaul of the
recovery.conf mechanisms, which is not the right time to do that, nor
have we even discussed let alone agreed what we would change it to.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2011-01-14 at 13:47 +0200, Heikki Linnakangas wrote:
> On 14.01.2011 13:15, Simon Riggs wrote:
> > /*
> > + * Recheck shared recoveryPause by polling.
> > + *
> > + * XXX It might seem we should do this via a shared Latch, but
> > + * currently we only support one shared latch per process and
> > + * that is already taken for Startup process. Polling is used
> > + * in other places in xlog.c already, so not a concern.
> > + */
>
> There is no such limitation with latches.

SIGUSR1 handler can only handle one shared latch

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On 14.01.2011 14:01, Simon Riggs wrote:
> On Fri, 2011-01-14 at 13:47 +0200, Heikki Linnakangas wrote:
>> On 14.01.2011 13:15, Simon Riggs wrote:
>>> /*
>>> + * Recheck shared recoveryPause by polling.
>>> + *
>>> + * XXX It might seem we should do this via a shared Latch, but
>>> + * currently we only support one shared latch per process and
>>> + * that is already taken for Startup process. Polling is used
>>> + * in other places in xlog.c already, so not a concern.
>>> + */
>>
>> There is no such limitation with latches.
>
> SIGUSR1 handler can only handle one shared latch

You can only *wait* for one latch at a time, but you can own more than
that. AFAICS you would never need to wait for the recovery-pause-latch
at the same time as the other latch.

(That you can't wait for more than one latch at a time isn't a
limitation of the SIGUSR1 handler either. The signal handler and the
self-pipe mechanism wouldn't need any changes to support multi-latch
waits. We're just missing a WaitMultipleLatches() function that would
check the is_set flag on multiple latches.)

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2011-01-14 at 14:08 +0200, Heikki Linnakangas wrote:
> On 14.01.2011 14:01, Simon Riggs wrote:
> > On Fri, 2011-01-14 at 13:47 +0200, Heikki Linnakangas wrote:
> >> On 14.01.2011 13:15, Simon Riggs wrote:
> >>> /*
> >>> + * Recheck shared recoveryPause by polling.
> >>> + *
> >>> + * XXX It might seem we should do this via a shared Latch, but
> >>> + * currently we only support one shared latch per process and
> >>> + * that is already taken for Startup process. Polling is used
> >>> + * in other places in xlog.c already, so not a concern.
> >>> + */
> >>
> >> There is no such limitation with latches.
> >
> > SIGUSR1 handler can only handle one shared latch
>
> You can only *wait* for one latch at a time, but you can own more than
> that. AFAICS you would never need to wait for the recovery-pause-latch
> at the same time as the other latch.

Yes, I understand.

Trouble is, if you wait on Latch X and other processes send wakeup
assuming you were waiting on Latch Y, then this will erroneously wake
you up.

So a process can have more than one shared latch, BUT other processes
don't know and can't tell which latch you're waiting on. Yes, you can
get around that, but not via the direct support of the latch module, as
currently written.

Polling is fine in that case, and we already do elsewhere, so it wasn't
critical for me to extend latch support to implement this.

> (That you can't wait for more than one latch at a time isn't a
> limitation of the SIGUSR1 handler either. The signal handler and the
> self-pipe mechanism wouldn't need any changes to support multi-latch
> waits. We're just missing a WaitMultipleLatches() function that would
> check the is_set flag on multiple latches.)

Something for the future.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On 14.01.2011 14:18, Simon Riggs wrote:
> On Fri, 2011-01-14 at 14:08 +0200, Heikki Linnakangas wrote:
>> On 14.01.2011 14:01, Simon Riggs wrote:
>>> On Fri, 2011-01-14 at 13:47 +0200, Heikki Linnakangas wrote:
>>>> On 14.01.2011 13:15, Simon Riggs wrote:
>>>>> /*
>>>>> + * Recheck shared recoveryPause by polling.
>>>>> + *
>>>>> + * XXX It might seem we should do this via a shared Latch, but
>>>>> + * currently we only support one shared latch per process and
>>>>> + * that is already taken for Startup process. Polling is used
>>>>> + * in other places in xlog.c already, so not a concern.
>>>>> + */
>>>>
>>>> There is no such limitation with latches.
>>>
>>> SIGUSR1 handler can only handle one shared latch
>>
>> You can only *wait* for one latch at a time, but you can own more than
>> that. AFAICS you would never need to wait for the recovery-pause-latch
>> at the same time as the other latch.
>
> Yes, I understand.
>
> Trouble is, if you wait on Latch X and other processes send wakeup
> assuming you were waiting on Latch Y, then this will erroneously wake
> you up.

The signal will wake up the process, but WaitLatch will quickly go back
to sleep if it wasn't for the latch we're waiting for. I don't think
that causes any meaningful performance issues.

So that's all handled within the latch code.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2011-01-14 at 14:27 +0200, Heikki Linnakangas wrote:

> > Trouble is, if you wait on Latch X and other processes send wakeup
> > assuming you were waiting on Latch Y, then this will erroneously wake
> > you up.
>
> The signal will wake up the process, but WaitLatch will quickly go back
> to sleep if it wasn't for the latch we're waiting for. I don't think
> that causes any meaningful performance issues.

Neither does polling.

> So that's all handled within the latch code.

I'll adjust the comment.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, Jan 14, 2011 at 8:15 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2011-01-14 at 11:09 +0000, Simon Riggs wrote:
>> Functions to control recovery, to aid PITR and Hot Standby.
>> pg_is_xlog_replay_paused()
>> pg_xlog_replay_pause()
>> pg_xlog_replay_resume()
>>
>> recovery.conf parameter: pause_at_recovery_target (bool)
>
> And now with the correct patch.

IIRC, in last year, you implemented the related function which
advances recovery the specified number of records and pauses.
Why did you drop that from the patch? That's very useful at least
for me to do PITR and debug the recovery code.

+ If in hot standby, all queries will see the same consistent snapshot
+ of the database, and no query conflicts will be generated.

Really? The access exclusive lock taken from the master before
pause of recovery can conflict with a query?

+ <primary><varname>pause_at_recovery_target</> recovery
parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies whether recovery should pause when the recovery target
+ is reached. The default is true, if a recovery target is set.

The default is false, according to the code.

If HS is disabled and pause_at_recovery_target is enabled,
recovery might never end infinitely. This is not desirable.
We should reject such a combination of settings or emit
WARNING?

+ while (RecoveryIsPaused());
+ {
+ pg_usleep(100000L); /* 100 ms */
+ HandleStartupProcInterrupts();

100ms seems too short. What about 1s or bigger?
Or wait on the latch rather than using poll loop.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Fri, Jan 14, 2011 at 9:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> How hard would it be to have a pg_xlog_replay_until(<xlog location or
>> timestamp>), to have it resume recovery up to that point and then
>> pause again?
>
> You can already do that for timestamps.

You mean using recovery_target_time and pause_at_recovery_target?
The problem is that we cannot continue recovery after the pause
by them. If we resume recovery after the pause, recovery ends
immediately.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Sat, 2011-01-15 at 20:11 +0900, Fujii Masao wrote:
> On Fri, Jan 14, 2011 at 9:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> How hard would it be to have a pg_xlog_replay_until(<xlog location or
> >> timestamp>), to have it resume recovery up to that point and then
> >> pause again?
> >
> > You can already do that for timestamps.
>
> You mean using recovery_target_time and pause_at_recovery_target?
> The problem is that we cannot continue recovery after the pause
> by them. If we resume recovery after the pause, recovery ends
> immediately.

Shutdown while paused, alter parameter, restart.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Sat, 2011-01-15 at 20:00 +0900, Fujii Masao wrote:
> On Fri, Jan 14, 2011 at 8:15 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On Fri, 2011-01-14 at 11:09 +0000, Simon Riggs wrote:
> >> Functions to control recovery, to aid PITR and Hot Standby.
> >> pg_is_xlog_replay_paused()
> >> pg_xlog_replay_pause()
> >> pg_xlog_replay_resume()
> >>
> >> recovery.conf parameter: pause_at_recovery_target (bool)
> >
> > And now with the correct patch.
>
> IIRC, in last year, you implemented the related function which
> advances recovery the specified number of records and pauses.
> Why did you drop that from the patch? That's very useful at least
> for me to do PITR and debug the recovery code.

SMoP. It complicated the code and the testing time would have exceeded
the amount of time I had available to spend on this, by a long way.

> + If in hot standby, all queries will see the same consistent snapshot
> + of the database, and no query conflicts will be generated.
>
> Really? The access exclusive lock taken from the master before
> pause of recovery can conflict with a query?

No "recovery conflicts" will be generated. i.e. no new conflicts.

Yes, existing locks will interfere with queries, if they exist.

> + <primary><varname>pause_at_recovery_target</> recovery
> parameter</primary>
> + </indexterm>
> + <listitem>
> + <para>
> + Specifies whether recovery should pause when the recovery target
> + is reached. The default is true, if a recovery target is set.
>
> The default is false, according to the code.

Thanks. Well spotted.

> If HS is disabled and pause_at_recovery_target is enabled,
> recovery might never end infinitely. This is not desirable.
> We should reject such a combination of settings or emit
> WARNING?

I was about to say "but it already does that". Checking the patch it
seems I must have removed that line, though I can't see any reason why I
would have removed it now. Will put it back.

> + while (RecoveryIsPaused());
> + {
> + pg_usleep(100000L); /* 100 ms */
> + HandleStartupProcInterrupts();
>
> 100ms seems too short. What about 1s or bigger?
> Or wait on the latch rather than using poll loop.

Yes, time is short.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Sat, Jan 15, 2011 at 12:17, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Sat, 2011-01-15 at 20:11 +0900, Fujii Masao wrote:
>> On Fri, Jan 14, 2011 at 9:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> >> How hard would it be to have a pg_xlog_replay_until(<xlog location or
>> >> timestamp>), to have it resume recovery up to that point and then
>> >> pause again?
>> >
>> > You can already do that for timestamps.
>>
>> You mean using recovery_target_time and pause_at_recovery_target?
>> The problem is that we cannot continue recovery after the pause
>> by them. If we resume recovery after the pause, recovery ends
>> immediately.
>
> Shutdown while paused, alter parameter, restart.

That's something I'd very much like to avoid - being able to say
continue-until using the function would be very nice. Consider for
example doing this from pgadmin.

So I'm back to my original question which is, how much work would this
be? I don't know my way around that part so I can't estimate, and
what's there so far is certainly a lot better than nothing, but if
it's not a huge amount of work it would be a great improvement.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Sun, Jan 16, 2011 at 11:52 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> So I'm back to my original question which is, how much work would this
> be? I don't know my way around that part so I can't estimate, and
> what's there so far is certainly a lot better than nothing, but if
> it's not a huge amount of work it would be a great improvement.

I don't think it's a huge amount of work. Though I'm not sure
Simon has time to do that since he would be very busy with
SyncRep.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Mon, Jan 17, 2011 at 02:53, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Sun, Jan 16, 2011 at 11:52 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> So I'm back to my original question which is, how much work would this
>> be? I don't know my way around that part so I can't estimate, and
>> what's there so far is certainly a lot better than nothing, but if
>> it's not a huge amount of work it would be a great improvement.
>
> I don't think it's a huge amount of work. Though I'm not sure
> Simon has time to do that since he would be very busy with
> SyncRep.

True - and I'd certainly want to see him focus on that, as long as
there is any chance we'll get it in there in time.

I'd add it myself, if I only knew that code enough to be sure of what
I was doing ;) But if somebody who does finds some spare time...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/