Thread: Immediate standby promotion
Hi, I'd like to propose to add new option "--immediate" to pg_ctl promote. When this option is set, recovery ignores any WAL data which have not been replayed yet and exits immediately. Patch attached. This promotion is faster than normal one but can cause data loss. So it's useful if we want to switch the server to normal operation as soon as possible at the expense of durability. Also imagine the case where, while recovery is being delayed (by a time-delayed standby which was introduced in 9.4) or paused (by pg_xlog_replay_pause), you find that subsequent WAL data can cause a disaster to happen (for example, WAL data indicating an unexpected deletion of important database). In this case, this immediate promotion can be used to ignore such problematic WAL data. With the patch, "--immediate" option controls whether immediate promotion is performed or not. OTOH, we can extend "-m" option that pg_ctl stop has already used so that it controls also the mode of promotion. But when we discussed this feature before, someone disagreed to do that because shutdown mode and promotion mode are different concepts. For example, if "smart" is specified as mode, how should the promotion work? I agree with him, and just added new separate option. Thought? Regards, -- Fujii Masao
Attachment
Fujii Masao <masao.fujii@gmail.com> writes:
> I'd like to propose to add new option "--immediate" to pg_ctl promote.
> When this option is set, recovery ignores any WAL data which have not
> been replayed yet and exits immediately. Patch attached.
> This promotion is faster than normal one but can cause data loss.
TBH, I cannot imagine a situation where that would be a sane thing to do.
If you have WAL, why would you not replay what you have? The purpose
of a database is to preserve your data, not randomly throw it away.
> Also imagine the case
> where, while recovery is being delayed (by a time-delayed standby
> which was introduced in 9.4) or paused (by pg_xlog_replay_pause),
> you find that subsequent WAL data can cause a disaster to happen
> (for example, WAL data indicating an unexpected deletion of
> important database). In this case, this immediate promotion can be
> used to ignore such problematic WAL data.
That example does not demonstrate that a patch like this is useful.
What you'd presumably want is a way to stop replay at a defined place
(comparable to the PITR facilities). Not to just abandon the WAL stream
at whatever point replay has reached.
regards, tom lane
On Thu, Aug 14, 2014 at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Fujii Masao <masao.fujii@gmail.com> writes: >> I'd like to propose to add new option "--immediate" to pg_ctl promote. >> When this option is set, recovery ignores any WAL data which have not >> been replayed yet and exits immediately. Patch attached. > >> This promotion is faster than normal one but can cause data loss. > > TBH, I cannot imagine a situation where that would be a sane thing to do. > If you have WAL, why would you not replay what you have? The purpose > of a database is to preserve your data, not randomly throw it away. I've wanted this a number of times, so I think it's quite sane. >> Also imagine the case >> where, while recovery is being delayed (by a time-delayed standby >> which was introduced in 9.4) or paused (by pg_xlog_replay_pause), >> you find that subsequent WAL data can cause a disaster to happen >> (for example, WAL data indicating an unexpected deletion of >> important database). In this case, this immediate promotion can be >> used to ignore such problematic WAL data. > > That example does not demonstrate that a patch like this is useful. > What you'd presumably want is a way to stop replay at a defined place > (comparable to the PITR facilities). Not to just abandon the WAL stream > at whatever point replay has reached. We already have the facilities to stop replay at a defined place. But then what? Without this patch, do well tell the customer to stop replay, do a pg_dump of the whole database, and restore it into a new database? Because that's crazy. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
<div dir="ltr"><div class="gmail_extra"><br />On Thu, Aug 14, 2014 at 4:27 PM, Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br />><br />> We already have the facilitiesto stop replay at a defined place. But<br /> > then what? Without this patch, do well tell the customer tostop<br />> replay, do a pg_dump of the whole database, and restore it into a new<br />> database? Because that'scrazy.<br />><br /><br /></div><div class="gmail_extra"> Yeah... and as Fujji already said another case is whensome operation error occurs in the master (like a wrong "drop database") and we have a time-delayed standby that canbe used to recover the mistake quickly.<br /></div><div class="gmail_extra"><br /><br />--<br />Fabrízio de Royes Mello<br/>Consultoria/Coaching PostgreSQL<br />>> Timbira: <a href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog sobre TI: <a href="http://fabriziomello.blogspot.com">http://fabriziomello.blogspot.com</a><br/> >> Perfil Linkedin: <a href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a></div></div>
Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote: > Robert Haas <robertmhaas@gmail.com> wrote: >> We already have the facilities to stop replay at a defined >> place. But then what? Without this patch, do well tell the >> customer to stop replay, do a pg_dump of the whole database, and >> restore it into a new database? Because that's crazy. > > Yeah... and as Fujji already said another case is when some > operation error occurs in the master (like a wrong "drop > database") and we have a time-delayed standby that can be used to > recover the mistake quickly. I have been in the position of having an ad hoc data fix by someone running raw SQL where they forgot the WHERE clause on a DELETE from the table that just about everything joins to (the "Case" table for a court system). Since we had both PITR backups and logical replication we were able to recover by kicking the users out, doing a PITR recovery up to shortly before the mistake was made, and then replaying the logical transaction stream from that point to the end, excluding the bad transaction. On the face of it, that doesn't sound like a big deal, right? But we had to kick out seven state Supreme Court justices, 16 Court of Appeals judges, and the related support staff for a couple hours. Trust me, with a delayed replica and the option of an immediate promotion of the standby, I would have had a less stressful day. Instead of telling all those people they couldn't use a key tool in their workflow for two hours, I could have told them that there would be a one or two minute outage after which any entries in the last "n" minutes would be delayed in appearing in their view of the data for two hours. The justices would have been a lot happier, and when they are happier, so is everyone else. :-) The suggested feature seems useful to me. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Aug 14, 2014 at 1:49 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> Hi,
>
> I'd like to propose to add new option "--immediate" to pg_ctl promote.
> When this option is set, recovery ignores any WAL data which have not
> been replayed yet and exits immediately. Patch attached.
>
> This promotion is faster than normal one but can cause data loss.
>
> Hi,
>
> I'd like to propose to add new option "--immediate" to pg_ctl promote.
> When this option is set, recovery ignores any WAL data which have not
> been replayed yet and exits immediately. Patch attached.
>
> This promotion is faster than normal one but can cause data loss.
I think there is one downside as well for this proposal that
apart from data loss, it can lead to uncommitted data occupying
space in database which needs to be later cleaned by vacuum.
This can happen with non-immediate promote as well, but the
chances with immediate are more. So the gain we got by doing
immediate promotion can lead to slow down of operations in some
cases. It might be useful if we mention this in docs.
Few comments about patch:
1.
On standby we will see below message:
LOG: received promote request
User will always see above message irrespective of whether it
is immediate promote or any other mode of promote. I think it will
be better to distinguish between different modes and display the
appropriate message.
2.
StartupXLOG()
{
..
+ if (immediate_promote)
+ break;
..
}
Why are you doing this check after pause
(recoveryApplyDelay/recoveryPausesHere) for recovery?
Why can't we do it after ReadRecord()?
3.
! * of "promote" and "immediate_promote"
shouldn't in above sentence 'or' is more appropriate?
On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Thu, Aug 14, 2014 at 1:49 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>
>> Hi,
>>
>> I'd like to propose to add new option "--immediate" to pg_ctl promote.
>> When this option is set, recovery ignores any WAL data which have not
>> been replayed yet and exits immediately. Patch attached.
>>
>> This promotion is faster than normal one but can cause data loss.
>
Thanks for reviewing the patch!
> I think there is one downside as well for this proposal that
> apart from data loss, it can lead to uncommitted data occupying
> space in database which needs to be later cleaned by vacuum.
> This can happen with non-immediate promote as well, but the
> chances with immediate are more. So the gain we got by doing
> immediate promotion can lead to slow down of operations in some
> cases. It might be useful if we mention this in docs.
Yep, the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data of VACUUM. But, OTOH,
I think that the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data which will generate
garbage data. So I'm not sure if it's worth adding that note to the doc.
>
> Few comments about patch:
>
> 1.
> On standby we will see below message:
>
> LOG: received promote request
>
> User will always see above message irrespective of whether it
> is immediate promote or any other mode of promote. I think it will
> be better to distinguish between different modes and display the
> appropriate message.
Agreed. So I'm thinking to change the code as follows.
if (immediate_promote) ereport(LOG, (errmsg("received immediate promote request")));
else ereport(LOG, (errmsg("received promote request")));
Or we should name the normal promotion?
>
> 2.
> StartupXLOG()
> {
> ..
> + if (immediate_promote)
> + break;
> ..
> }
>
> Why are you doing this check after pause
> (recoveryApplyDelay/recoveryPausesHere) for recovery?
>
> Why can't we do it after ReadRecord()?
We can do that check either after ReadRecord() or after pause.
I preferred to add the check after pause because immediate promotion
would be likely to be requested while recovery is being paused.
In this case, if we do that check after ReadRecord(), we need to read
one more WAL record that actually we don't need.
BTW, in the current patch, when immediate promotion is requested while
recovery is being paused, the recovery keeps being paused until it's
manually resumed. But immediate promotion should cause even paused
recovery to end immediately?
> 3.
> ! * of "promote" and "immediate_promote"
> shouldn't in above sentence 'or' is more appropriate?
Yep.
Regards,
--
Fujii Masao
On Mon, Sep 1, 2014 at 4:44 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > I think there is one downside as well for this proposal that
> > apart from data loss, it can lead to uncommitted data occupying
> > space in database which needs to be later cleaned by vacuum.
> > This can happen with non-immediate promote as well, but the
> > chances with immediate are more. So the gain we got by doing
> > immediate promotion can lead to slow down of operations in some
> > cases. It might be useful if we mention this in docs.
>
> Yep, the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data of VACUUM. But, OTOH,
> I think that the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data which will generate
> garbage data.
> >
> > Few comments about patch:
> >
> > 1.
> > On standby we will see below message:
> >
> > LOG: received promote request
> >
> > User will always see above message irrespective of whether it
> > is immediate promote or any other mode of promote. I think it will
> > be better to distinguish between different modes and display the
> > appropriate message.
>
> Agreed. So I'm thinking to change the code as follows.
>
> if (immediate_promote)
> ereport(LOG, (errmsg("received immediate promote request")));
> else
> ereport(LOG, (errmsg("received promote request")));
This seems fine to me.
> Or we should name the normal promotion?
No need.
> >
> > 2.
> > StartupXLOG()
> > {
> > ..
> > + if (immediate_promote)
> > + break;
> > ..
> > }
> >
> > Why are you doing this check after pause
> > (recoveryApplyDelay/recoveryPausesHere) for recovery?
> >
> > Why can't we do it after ReadRecord()?
>
> We can do that check either after ReadRecord() or after pause.
> I preferred to add the check after pause because immediate promotion
> would be likely to be requested while recovery is being paused.
> In this case, if we do that check after ReadRecord(), we need to read
> one more WAL record that actually we don't need.
> BTW, in the current patch, when immediate promotion is requested while
> recovery is being paused, the recovery keeps being paused until it's
> manually resumed. But immediate promotion should cause even paused
> recovery to end immediately?
Yeap, I also think so.
Another issue with immediate promotion is that currently if primary server
> On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > I think there is one downside as well for this proposal that
> > apart from data loss, it can lead to uncommitted data occupying
> > space in database which needs to be later cleaned by vacuum.
> > This can happen with non-immediate promote as well, but the
> > chances with immediate are more. So the gain we got by doing
> > immediate promotion can lead to slow down of operations in some
> > cases. It might be useful if we mention this in docs.
>
> Yep, the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data of VACUUM. But, OTOH,
> I think that the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data which will generate
> garbage data.
This seems arguable, because immediate promotion won't allow
WAL data to be replayed completely which means more chance
that only partial data of transactions will be replayed and commit
for those transactions won't get replayed, so it can lead to garbage
data.
> So I'm not sure if it's worth adding that note to the doc.
No issues, I just want to bring this point to your notice so that if
No issues, I just want to bring this point to your notice so that if
you think it is important enough that we can mention it then we
can update the docs else leave it.
> >
> > Few comments about patch:
> >
> > 1.
> > On standby we will see below message:
> >
> > LOG: received promote request
> >
> > User will always see above message irrespective of whether it
> > is immediate promote or any other mode of promote. I think it will
> > be better to distinguish between different modes and display the
> > appropriate message.
>
> Agreed. So I'm thinking to change the code as follows.
>
> if (immediate_promote)
> ereport(LOG, (errmsg("received immediate promote request")));
> else
> ereport(LOG, (errmsg("received promote request")));
This seems fine to me.
> Or we should name the normal promotion?
No need.
> >
> > 2.
> > StartupXLOG()
> > {
> > ..
> > + if (immediate_promote)
> > + break;
> > ..
> > }
> >
> > Why are you doing this check after pause
> > (recoveryApplyDelay/recoveryPausesHere) for recovery?
> >
> > Why can't we do it after ReadRecord()?
>
> We can do that check either after ReadRecord() or after pause.
> I preferred to add the check after pause because immediate promotion
> would be likely to be requested while recovery is being paused.
> In this case, if we do that check after ReadRecord(), we need to read
> one more WAL record that actually we don't need.
Okay, but for that you need to make sure that pause can detect
promotion request.
> BTW, in the current patch, when immediate promotion is requested while
> recovery is being paused, the recovery keeps being paused until it's
> manually resumed. But immediate promotion should cause even paused
> recovery to end immediately?
Yeap, I also think so.
Another issue with immediate promotion is that currently if primary server
is continuously sending the data, then standby could not detect --immediate
promote request and the reason seems to be below code:
WaitForWALToBecomeAvailable()
{
...
{
/* just make sure source info is correct... */
readSource = XLOG_FROM_STREAM;
XLogReceiptSource = XLOG_FROM_STREAM;
return true;
}
..
{
...
{
/* just make sure source info is correct... */
readSource = XLOG_FROM_STREAM;
XLogReceiptSource = XLOG_FROM_STREAM;
return true;
}
..
if (CheckForStandbyTrigger())
}
}
Basically we won't check for promote request if the data is available.
I have even reproduced this by below test case:
Primary (session-1) -
1. Create table t1 (c1 int, c2 char(500)) with (fillfactor = 10);
Standby -
2. Configure and start standby
3. Just connect with one client
Primary (session-1) -
4. insert into t1 values (generate_series(1,100000), 'aaaaa');
From another window, run command:
5. pg_ctl promote --immediate -D ..\..\Database1
Run step-4 and step-5 at the same time.
Currently standby is promoted only after insert operation
in step-4 is finished which seems to be wrong.
Apart from above issue, I have one question for you regarding
this feature, currently the patch supports immediate promotion
via pg_ctl promote, however we have another mechanism (trigger_file)
which you have not enhanced to support this new feature. Is there
any reason for same?
On Mon, Sep 1, 2014 at 7:14 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> I think there is one downside as well for this proposal that
>> apart from data loss, it can lead to uncommitted data occupying
>> space in database which needs to be later cleaned by vacuum.
>> This can happen with non-immediate promote as well, but the
>> chances with immediate are more. So the gain we got by doing
>> immediate promotion can lead to slow down of operations in some
>> cases. It might be useful if we mention this in docs.
>
> Yep, the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data of VACUUM. But, OTOH,
> I think that the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data which will generate
> garbage data. So I'm not sure if it's worth adding that note to the doc.
-1 for documenting that. This is mostly a general PostgreSQL
phenomenon and has little to do with immediate promotion specifically.
I think anything we write here is likely to be more confusing than
helpful.
> Agreed. So I'm thinking to change the code as follows.
>
> if (immediate_promote)
> ereport(LOG, (errmsg("received immediate promote request")));
> else
> ereport(LOG, (errmsg("received promote request")));
+1 for that version.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 14 August 2014 20:27, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Aug 14, 2014 at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Fujii Masao <masao.fujii@gmail.com> writes: >>> I'd like to propose to add new option "--immediate" to pg_ctl promote. >>> When this option is set, recovery ignores any WAL data which have not >>> been replayed yet and exits immediately. Patch attached. >> >>> This promotion is faster than normal one but can cause data loss. >> >> TBH, I cannot imagine a situation where that would be a sane thing to do. >> If you have WAL, why would you not replay what you have? The purpose >> of a database is to preserve your data, not randomly throw it away. > > I've wanted this a number of times, so I think it's quite sane. "fast" promotion was actually a supported option in r8 of Postgres but this option was removed when we implemented streaming replication in r9.0 The *rough* requirement is sane, but that's not the same thing as saying this exact patch makes sense. If you are paused and you can see that WAL up ahead is damaged, then YES, you do want to avoid applying it. That is possible by setting a PITR target so that recovery stops at a precise location specified by you. As an existing option is it better than the blunt force trauma suggested here. If you really don't care, just shutdown server, resetxlog and start her up - again, no need for new option. Anyway, my view is that this is a blunt instrument to do something we can already do. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Sep 17, 2014 at 7:23 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 14 August 2014 20:27, Robert Haas <robertmhaas@gmail.com> wrote: >> On Thu, Aug 14, 2014 at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Fujii Masao <masao.fujii@gmail.com> writes: >>>> I'd like to propose to add new option "--immediate" to pg_ctl promote. >>>> When this option is set, recovery ignores any WAL data which have not >>>> been replayed yet and exits immediately. Patch attached. >>> >>>> This promotion is faster than normal one but can cause data loss. >>> >>> TBH, I cannot imagine a situation where that would be a sane thing to do. >>> If you have WAL, why would you not replay what you have? The purpose >>> of a database is to preserve your data, not randomly throw it away. >> >> I've wanted this a number of times, so I think it's quite sane. > > "fast" promotion was actually a supported option in r8 of Postgres but > this option was removed when we implemented streaming replication in > r9.0 > > The *rough* requirement is sane, but that's not the same thing as > saying this exact patch makes sense. Granted. Fair point. > If you are paused and you can see that WAL up ahead is damaged, then > YES, you do want to avoid applying it. That is possible by setting a > PITR target so that recovery stops at a precise location specified by > you. As an existing option is it better than the blunt force trauma > suggested here. You can pause at a recovery target, but then what if you want to go read/write at that point? Or what if you've got a time-delayed standby and you want to break replication so that it doesn't replay the DROP TABLE students that somebody ran on the master? It doesn't have to be that WAL is unreadable or corrupt; it's enough for it to contain changes you wish to avoid replaying. > If you really don't care, just shutdown server, resetxlog and start > her up - again, no need for new option. To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" seems like a friendlier interface than making somebody shut down the server, run pg_resetxlog, and start it up again. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 18 September 2014 01:22, Robert Haas <robertmhaas@gmail.com> wrote: >> "fast" promotion was actually a supported option in r8 of Postgres but >> this option was removed when we implemented streaming replication in >> r9.0 >> >> The *rough* requirement is sane, but that's not the same thing as >> saying this exact patch makes sense. > > Granted. Fair point. > >> If you are paused and you can see that WAL up ahead is damaged, then >> YES, you do want to avoid applying it. That is possible by setting a >> PITR target so that recovery stops at a precise location specified by >> you. As an existing option is it better than the blunt force trauma >> suggested here. > > You can pause at a recovery target, but then what if you want to go > read/write at that point? Or what if you've got a time-delayed > standby and you want to break replication so that it doesn't replay > the DROP TABLE students that somebody ran on the master? It doesn't > have to be that WAL is unreadable or corrupt; it's enough for it to > contain changes you wish to avoid replaying. > >> If you really don't care, just shutdown server, resetxlog and start >> her up - again, no need for new option. > > To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" > seems like a friendlier interface than making somebody shut down the > server, run pg_resetxlog, and start it up again. It makes sense to go from paused --> promoted. It doesn't make sense to go from normal running --> promoted, since that is just random data loss. I very much understand the case where somebody is shouting "get the web site up, we are losing business". Implementing a feature that allows people to do exactly what they asked (go live now), but loses business transactions that we thought had been safely recorded is not good. It implements only the exact request, not its actual intention. Any feature that lumps both cases together is wrongly designed and will cause data loss. We go to a lot of trouble to ensure data is successfully on disk and in WAL. I won't give that up, nor do I want to make it easier to lose data than it already is. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Sep 25, 2014 at 5:36 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > We go to a lot of trouble to ensure data is successfully on disk and > in WAL. I won't give that up, nor do I want to make it easier to lose > data than it already is. +1. -- Michael
On Wed, Sep 24, 2014 at 4:36 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >> To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" >> seems like a friendlier interface than making somebody shut down the >> server, run pg_resetxlog, and start it up again. > > It makes sense to go from paused --> promoted. Well, we could certainly require that people call pg_xlog_replay_pause() before they call pg_promote_now(). I don't think I like that better, but it beats not having pg_promote_now(). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2014-09-24 21:36:50 +0100, Simon Riggs wrote: > On 18 September 2014 01:22, Robert Haas <robertmhaas@gmail.com> wrote: > > >> "fast" promotion was actually a supported option in r8 of Postgres but > >> this option was removed when we implemented streaming replication in > >> r9.0 > >> > >> The *rough* requirement is sane, but that's not the same thing as > >> saying this exact patch makes sense. > > > > Granted. Fair point. > > > >> If you are paused and you can see that WAL up ahead is damaged, then > >> YES, you do want to avoid applying it. That is possible by setting a > >> PITR target so that recovery stops at a precise location specified by > >> you. As an existing option is it better than the blunt force trauma > >> suggested here. > > > > You can pause at a recovery target, but then what if you want to go > > read/write at that point? Or what if you've got a time-delayed > > standby and you want to break replication so that it doesn't replay > > the DROP TABLE students that somebody ran on the master? It doesn't > > have to be that WAL is unreadable or corrupt; it's enough for it to > > contain changes you wish to avoid replaying. > > > >> If you really don't care, just shutdown server, resetxlog and start > >> her up - again, no need for new option. I think that should pretty much never be something an admin has to run. It's just about impossible to get this right. In all likelihood just running pg_resetxlog on a database in recovery will have corrupted your database. Which is why pg_resetxlog won't even let you proceed without using -f because it checks for DB_SHUTDOWNED. Rightly so. pg_resetxlog *removes* *all* existing WAL and sets the current control file state to DB_SHUTDOWNED. Thus there will be no recovery when starting afterwards. > > To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" > > seems like a friendlier interface than making somebody shut down the > > server, run pg_resetxlog, and start it up again. > > It makes sense to go from paused --> promoted. > > It doesn't make sense to go from normal running --> promoted, since > that is just random data loss. Why? I don't see what's random in promoting a node in the current state *iff* it's currently consistent. Just imagine something like promoting a current standby to a full node because you want to run some tests on it that require writes. There's absolutely no need to investigate the current state for that. > I very much understand the case where > somebody is shouting "get the web site up, we are losing business". > Implementing a feature that allows people to do exactly what they > asked (go live now), but loses business transactions that we thought > had been safely recorded is not good. It implements only the exact > request, not its actual intention. That seems to be a problem of massively understanding on the part of the user. And I don't see how this is going to be safer by requiring the user to first issue a pause reuest. I think we should attempt to solve this by naming the command appropriately. Something like 'abort_replay_and_promote'. Long, nontrivial to type, and descriptive. > Any feature that lumps both cases together is wrongly designed and > will cause data loss. > > We go to a lot of trouble to ensure data is successfully on disk and > in WAL. I won't give that up, nor do I want to make it easier to lose > data than it already is. I think that's not really related. Such a promotion doesn't cause data loss in the sense of loosing data a *clueful* operator wanted to keep. Yes, it can be used wrongly, but it's far from alone in that. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 2014-09-25 11:13:50 -0400, Robert Haas wrote: > On Wed, Sep 24, 2014 at 4:36 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > >> To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" > >> seems like a friendlier interface than making somebody shut down the > >> server, run pg_resetxlog, and start it up again. > > > > It makes sense to go from paused --> promoted. > > Well, we could certainly require that people call > pg_xlog_replay_pause() before they call pg_promote_now(). I don't > think I like that better, but it beats not having pg_promote_now(). FWIW I think it's a mistake to only allow this on a hot standby. This should be doable using pg_ctl alone. a) works with wal_level=archive, b) sometimes hot_standby=off is a good bit more efficient c) sometimes you don't want to allow connections. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Sep 25, 2014 at 11:34 AM, Andres Freund <andres@2ndquadrant.com> wrote: > On 2014-09-25 11:13:50 -0400, Robert Haas wrote: >> On Wed, Sep 24, 2014 at 4:36 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >> >> To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" >> >> seems like a friendlier interface than making somebody shut down the >> >> server, run pg_resetxlog, and start it up again. >> > >> > It makes sense to go from paused --> promoted. >> >> Well, we could certainly require that people call >> pg_xlog_replay_pause() before they call pg_promote_now(). I don't >> think I like that better, but it beats not having pg_promote_now(). > > FWIW I think it's a mistake to only allow this on a hot standby. This > should be doable using pg_ctl alone. a) works with wal_level=archive, b) > sometimes hot_standby=off is a good bit more efficient c) sometimes you > don't want to allow connections. Good point. Also, a pg_ctl command is more friendly to cluster-ware, of which there is a lot these days. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 25 September 2014 16:29, Andres Freund <andres@2ndquadrant.com> wrote: >> > To me, being able to say "pg_ctl promote_right_now -m yes_i_mean_it" >> > seems like a friendlier interface than making somebody shut down the >> > server, run pg_resetxlog, and start it up again. >> >> It makes sense to go from paused --> promoted. >> >> It doesn't make sense to go from normal running --> promoted, since >> that is just random data loss. > > Why? I don't see what's random in promoting a node in the current state > *iff* it's currently consistent. > > Just imagine something like promoting a current standby to a full node > because you want to run some tests on it that require writes. There's > absolutely no need to investigate the current state for that. > >> I very much understand the case where >> somebody is shouting "get the web site up, we are losing business". >> Implementing a feature that allows people to do exactly what they >> asked (go live now), but loses business transactions that we thought >> had been safely recorded is not good. It implements only the exact >> request, not its actual intention. > > That seems to be a problem of massively understanding on the part of the > user. And I don't see how this is going to be safer by requiring the > user to first issue a pause reuest. > > I think we should attempt to solve this by naming the command > appropriately. Something like 'abort_replay_and_promote'. Long, > nontrivial to type, and descriptive. > >> Any feature that lumps both cases together is wrongly designed and >> will cause data loss. >> >> We go to a lot of trouble to ensure data is successfully on disk and >> in WAL. I won't give that up, nor do I want to make it easier to lose >> data than it already is. > > I think that's not really related. Such a promotion doesn't cause data > loss in the sense of loosing data a *clueful* operator wanted to > keep. Yes, it can be used wrongly, but it's far from alone in that. Yes it does cause data loss. The clueful operator has no idea where they are so there is no "used rightly" in that case. If I were to give this feature a name it would be --discard or --random-data-loss, or --reset-hard The point of pausing is misunderstood. That is close but not quite relevant. If you are at a known location and request promotion, we can presume you know what you are doing, so it is simply Promote. If you are at an unknown location and therefore have clearly not verified any state before promotion, you are clearly making an uninformed decision that will likely result in data loss, for which there is no way of knowing the impact and no mechanism for recovering from. Trying to promote something while it is still recovering proves we don't know the state, we're just picking a random LSN. So if you have a time delayed standby and the master breaks, or there is a bad transaction then the correct action would be to * pause the delayed standby * discover where the master broke, or the xid of the bad transaction * restart recovery to go up to the correct time/xid/lsn * promote standby That is already possible in 9.4 The original patch for pausing contained code to reset the PITR target with functions, which would make the above even easier. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 2014-09-25 18:18:09 +0100, Simon Riggs wrote: > On 25 September 2014 16:29, Andres Freund <andres@2ndquadrant.com> wrote: > > I think that's not really related. Such a promotion doesn't cause data > > loss in the sense of loosing data a *clueful* operator wanted to > > keep. Yes, it can be used wrongly, but it's far from alone in that. > > Yes it does cause data loss. The clueful operator has no idea where > they are so there is no "used rightly" in that case. What? There definitely are cases where you don't need to know that to the T. Just think of the - quite frequently happening - need to promote a standby to run tests or reporting queries that can't be run on a standby. Sure, you shouldn't use it if you expect a very specific set of the data being there, but that's not always necessary. And that's why it should never, ever be the default. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Sep 25, 2014 at 1:30 PM, Andres Freund <andres@2ndquadrant.com> wrote: >> Yes it does cause data loss. The clueful operator has no idea where >> they are so there is no "used rightly" in that case. > > What? There definitely are cases where you don't need to know that to > the T. Just think of the - quite frequently happening - need to promote > a standby to run tests or reporting queries that can't be run on a > standby. > > Sure, you shouldn't use it if you expect a very specific set of the data > being there, but that's not always necessary. Very well put. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 25 September 2014 18:30, Andres Freund <andres@2ndquadrant.com> wrote: > On 2014-09-25 18:18:09 +0100, Simon Riggs wrote: >> On 25 September 2014 16:29, Andres Freund <andres@2ndquadrant.com> wrote: >> > I think that's not really related. Such a promotion doesn't cause data >> > loss in the sense of loosing data a *clueful* operator wanted to >> > keep. Yes, it can be used wrongly, but it's far from alone in that. >> >> Yes it does cause data loss. The clueful operator has no idea where >> they are so there is no "used rightly" in that case. > > What? There definitely are cases where you don't need to know that to > the T. Just think of the - quite frequently happening - need to promote > a standby to run tests or reporting queries that can't be run on a > standby. What do they do with the standby afterwards? Perhaps for testing, but I'd hope that Business Intelligence is done by freezing databases at known target times. So at least you can say, "using a database snapshot of 9am, we had the following results". We seem to be trying to justify something that is dangerous and will destroy data for incautious users. Of course it has uses, but thats not the point, its the danger that is the problem, not the lack of use. We go to a lot of trouble to avoid footguns elsewhere across many years, so I can't see why you'd want to have the --footgun option added here. recovery-target = 'vague' > Sure, you shouldn't use it if you expect a very specific set of the data > being there, but that's not always necessary. And that's why it should > never, ever be the default. -- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services