Re: Controlling hot standby

As the patch stands, there's no way to disable hot standby. The server
always opens for read-only connections as soon as it can. That might not
be what you want.

I think we need a GUC to enable/disable hot standby. It would become
handy if the unimaginable happens and there's a bug in the hot standby
code that prevents a server from recovering.

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

On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote:

> As the patch stands, there's no way to disable hot standby. The server
> always opens for read-only connections as soon as it can. That might not
> be what you want.
>
> I think we need a GUC to enable/disable hot standby. It would become
> handy if the unimaginable happens and there's a bug in the hot standby
> code that prevents a server from recovering.

Heaven forbid!

I'll add it now, default = on.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2009-01-23 at 10:05 +0000, Simon Riggs wrote:
> I'll add it now, default = on.

Did you mean "off"?

--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote:
> On Fri, 2009-01-23 at 10:05 +0000, Simon Riggs wrote:
> > I'll add it now, default = on.
>
> Did you mean "off"?

No, do you?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote:
> As the patch stands, there's no way to disable hot standby. The server
> always opens for read-only connections as soon as it can. That might not
> be what you want.
>
> I think we need a GUC to enable/disable hot standby. It would become
> handy if the unimaginable happens and there's a bug in the hot standby
> code that prevents a server from recovering.

Currently recovery processing is only active during archive recovery,
never during crash recovery, for the above reason.

> I think we need a GUC to enable/disable hot standby.

I presume you mean something that will stop us keeping track of
snapshots and preventing users from connecting?

Suggestion: hot_standby = on | off (on is default)
(in recovery.conf)

Would that include starting the bgwriter during recovery? Or should be a
separate boolean? If needed, should this be in postgresql.conf or in
recovery.conf?

Suggestion: bgwriter_during_recovery = on | off (on is default)
(in postgresql.conf)
If we do have this it may be deprecated in later releases if code is
stable and we have no reason to de-select this.

This second option will give us a performance boost and low failover
time, just without being able to run queries.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote:
>> As the patch stands, there's no way to disable hot standby. The server
>> always opens for read-only connections as soon as it can. That might not
>> be what you want.
>>
>> I think we need a GUC to enable/disable hot standby. It would become
>> handy if the unimaginable happens and there's a bug in the hot standby
>> code that prevents a server from recovering.
>
> Currently recovery processing is only active during archive recovery,
> never during crash recovery, for the above reason.

Oh, ok. Is the bgwriter still launched in crash recovery, and do we
start accepting connections as soon as the replay finishes, before the
first checkpoint is finished?

>> I think we need a GUC to enable/disable hot standby.
>
> I presume you mean something that will stop us keeping track of
> snapshots and preventing users from connecting?
>
> Suggestion: hot_standby = on | off (on is default)
> (in recovery.conf)

Right.

> Would that include starting the bgwriter during recovery? Or should be a
> separate boolean? If needed, should this be in postgresql.conf or in
> recovery.conf?

I don't think that's necessary.

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

On Fri, 2009-01-23 at 14:28 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote:
> >> As the patch stands, there's no way to disable hot standby. The server
> >> always opens for read-only connections as soon as it can. That might not
> >> be what you want.
> >>
> >> I think we need a GUC to enable/disable hot standby. It would become
> >> handy if the unimaginable happens and there's a bug in the hot standby
> >> code that prevents a server from recovering.
> >
> > Currently recovery processing is only active during archive recovery,
> > never during crash recovery, for the above reason.
>
> Oh, ok. Is the bgwriter still launched in crash recovery,

Currently in the patch we never launch bgwriter in crash recovery.

> and do we
> start accepting connections as soon as the replay finishes, before the
> first checkpoint is finished?

Which means we never use "fast start" behaviour. We write a full
checkpoint and only then do we open for business normally.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
>
> On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote:
> > On Fri, 2009-01-23 at 10:05 +0000, Simon Riggs wrote:
> > > I'll add it now, default = on.
> >
> > Did you mean "off"?
>
> No, do you?

Depends on the setting :-) It is "hot_standby=off" by default, right?
I think having a double negative "disable_hot_standby=off" would be
awkward.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 1/23/09, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Simon Riggs wrote:
> >
> > On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote:
> > > On Fri, 2009-01-23 at 10:05 +0000, Simon Riggs wrote:
> > > > I'll add it now, default = on.
> > >
> > > Did you mean "off"?
> >
> > No, do you?
>
>
> Depends on the setting :-) It is "hot_standby=off" by default, right?
> I think having a double negative "disable_hot_standby=off" would be
> awkward.

Is 'hot standby' going to be the official moniker for the feature?
(not 'standby replication', or something else?). I wonder if we
should pick something more descriptive.

merlin

Merlin Moncure wrote:
> Is 'hot standby' going to be the official moniker for the feature?
> (not 'standby replication', or something else?). I wonder if we
> should pick something more descriptive.

Could also be something like "allow_connections_during_recovery".

I'd keep the word "replication" out of this..

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:

> Merlin Moncure wrote:
>> Is 'hot standby' going to be the official moniker for the feature?
>> (not 'standby replication', or something else?). I wonder if we
>> should pick something more descriptive.
>
> Could also be something like "allow_connections_during_recovery".
>
> I'd keep the word "replication" out of this..

Just wondering, but does this make more sense as a recovery.conf parameter?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> >
> > On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote:
> > > On Fri, 2009-01-23 at 10:05 +0000, Simon Riggs wrote:
> > > > I'll add it now, default = on.
> > >
> > > Did you mean "off"?
> >
> > No, do you?
>
> Depends on the setting :-) It is "hot_standby=off" by default, right?
> I think having a double negative "disable_hot_standby=off" would be
> awkward.

It is on by default. Why would you want it "off" by default?

We want people to use the feature and turn it off only if they need to
for some reason.

It is only used during archive recovery, so it is already "off" if
you're using some other kind of replication.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2009-01-23 at 17:07 +0200, Heikki Linnakangas wrote:
> Merlin Moncure wrote:
> > Is 'hot standby' going to be the official moniker for the feature?
> > (not 'standby replication', or something else?). I wonder if we
> > should pick something more descriptive.
>
> Could also be something like "allow_connections_during_recovery".
>
> I'd keep the word "replication" out of this..

I suspect that it is used much more during "hot standby" than during
simple "recovery", which would mean something you do after unclean
shutdown or when restoring from backup.

maybe allow_hot_slaves ;)

--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

Simon Riggs wrote:
>
> On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote:

> > Depends on the setting :-) It is "hot_standby=off" by default, right?
> > I think having a double negative "disable_hot_standby=off" would be
> > awkward.
>
> It is on by default. Why would you want it "off" by default?

Would it slow down the normal recovery after a crash if I don't have any
slaves?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Simon Riggs wrote:
>> On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote:
>
>>> Depends on the setting :-) It is "hot_standby=off" by default, right?
>>> I think having a double negative "disable_hot_standby=off" would be
>>> awkward.
>> It is on by default. Why would you want it "off" by default?
>
> Would it slow down the normal recovery after a crash if I don't have any
> slaves?

Not in any meaningful way, I'd imagine.

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

>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Simon Riggs wrote:
>>
>> It is on by default. Why would you want it "off" by default?
>
> Would it slow down the normal recovery after a crash if I don't have
> any slaves?

And how about during "traditional" PITR recovery?

-Kevin

> Could also be something like "allow_connections_during_recovery".

+1 (should we say "continuous recovery?")

> I'd keep the word "replication" out of this..

+1.

...Robert

On Fri, 2009-01-23 at 13:28 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> >
> > On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote:
>
> > > Depends on the setting :-) It is "hot_standby=off" by default, right?
> > > I think having a double negative "disable_hot_standby=off" would be
> > > awkward.
> >
> > It is on by default. Why would you want it "off" by default?
>
> Would it slow down the normal recovery after a crash if I don't have any
> slaves?

No

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2009-01-23 at 10:35 -0600, Kevin Grittner wrote:
> >>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > Simon Riggs wrote:
> >>
> >> It is on by default. Why would you want it "off" by default?
> >
> > Would it slow down the normal recovery after a crash if I don't have
> > any slaves?
>
> And how about during "traditional" PITR recovery?

It is possible that it might slow down a traditional PITR recovery. But
we enable the bgwriter to do checkpoints if we have it enabled, which
were a major cause of slow down during PITR anyway.

There are considerable benefits to having it turned on during PITR

Please read this to see why
http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

>>> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> There are considerable benefits to having it turned on during PITR
>
> Please read this to see why
>
http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery

Am I reading this right? What I get out of it is that users can
connect to the database during recovery, like with psql or JDBC? Any
user can query recovery completion status and progress, while a
superuser can also step through transactions in various ways? Can the
data in the database be queried while recovery is paused?

If I'm understanding this, and data can be queried during a pause, I
can certainly see use cases for this; but it might be important to be
able to start the recovery in a paused state to avoid going too far in
the transaction stream before a connection can be established and
recovery paused.

-Kevin

On Fri, 2009-01-23 at 12:17 -0600, Kevin Grittner wrote:
> >>> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> > There are considerable benefits to having it turned on during PITR
> >
> > Please read this to see why
> >
> http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery
>
> Am I reading this right? What I get out of it is that users can
> connect to the database during recovery, like with psql or JDBC? Any
> user can query recovery completion status and progress, while a
> superuser can also step through transactions in various ways? Can the
> data in the database be queried while recovery is paused?

That's a big Yes to all of that. Exactly what all this is for.

> If I'm understanding this, and data can be queried during a pause, I
> can certainly see use cases for this; but it might be important to be
> able to start the recovery in a paused state to avoid going too far in
> the transaction stream before a connection can be established and
> recovery paused.

OK, that's a simple addition. Thanks for the suggestion.

I'll make it pause just after it changes state, so you can connect and
tell it to progress as far as you like.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2009-01-23 at 12:09 -0500, Robert Haas wrote:
> > Could also be something like "allow_connections_during_recovery".
>
> +1 (should we say "continuous recovery?")

Rather than a boolean, it seems more useful to specify a parameter that
has some additional usefulness, if we are going to have one at all.

max_recovery_connections = 0+

If you set it to 0 then we will turn off hot standby. Default value
would be to set it to same value as max_connections, though can be
overridden if specifically set.

This then allows us to control the number of users who get access to the
standby, which we might conceivably want to be smaller than
max_connections because recovery takes resources also.

It also means we don't have a specific "name" for this feature, we just
say what we want: connections.

The patch currently sets these parameters in recovery.conf. The above
change would only work if set via postgresql.conf, since it must be read
by the postmaster. So I would suggest that we put both
max_recovery_connections and max_standby_delay into postgresql.conf,
which then allows them both to be changed as recovery progresses.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support