Re: Immediate standby promotion

On Thu, Aug 14, 2014 at 10:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)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