Re: backup_label during crash recovery: do we know how to solve it? - Mailing list pgsql-hackers

On Thu, Dec 1, 2011 at 3:47 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Nov 29, 2011 at 9:10 PM, Daniel Farina <daniel@heroku.com> wrote:
>> Reviving a thread that has hit its second birthday:
>>
>> http://archives.postgresql.org/pgsql-hackers/2009-11/msg00024.php
>>
>> In our case not being able to restart Postgres when it has been taken
>> down in the middle of a base backup is starting to manifest as a
>> serious source of downtime: basically, any backend crash or machine
>> restart will cause postgres not to start without human intervention.
>> The message delivered is sufficiently scary and indirect enough
>> (because of the plausible scenarios that could cause corruption if
>> postgres were to make a decision automatically in the most general
>> case) that it's not all that attractive to train a general operator
>> rotation to assess what to do, as it involves reading and then,
>> effectively, ignoring some awfully scary error messages and removing
>> the backup label file.  Even if the error messages weren't scary
>> (itself a problem if one comes to the wrong conclusion as a result),
>> the time spent digging around under short notice to confirm what's
>> going on is a high pole in the tent for improving uptime for us,
>> taking an extra five to ten minutes per common encounter.
>>
>> Our problem is compounded by having a lot of databases that take base
>> backups at attenuated rates in an unattended way, and therefore a
>> human who may have been woken up from a sound sleep will have to
>> figure out what was going on before they've reached consciousness,
>> rather than a person with prior knowledge of having started a backup.
>> Also, fairly unremarkable databases can take so long to back up that
>> they may well have a greater than 20% chance of encountering this
>> problem at any particular time:  20% of a day is less than 5 hours per
>> day taken to do on-line backups.  Basically, we -- and anyone else
>> with unattended physical backup schemes -- are punished rather
>> severely by the current design.
>>
>> This issue has some more recent related incarnations, even if for
>> different reasons:
>>
>> http://archives.postgresql.org/pgsql-hackers/2011-01/msg00764.php
>>
>> Because backup_label "coming or going?" confusion in Postgres can have
>> serious consequences, I wanted to post to the list first to solicit a
>> minimal design to solve this problem.  If it's fairly small in its
>> mechanics then it may yet be feasible for the January CF.
>
> In some ways, I feel like this problem is unsolvable by definition.
> If a backup is designed to be an exact copy of the data directory
> taken between pg_start_backup() and pg_stop_backup(), then by
> definition you can't distinguish between the original and the copy.
> That's what a copy *is*.
>
> Now, we could fix this by requiring an additional step when creating a
> backup.  For example, we could create backup_label.not_really on the
> master and require the person taking the backup to rename it to
> backup_label on the slave before starting the postmaster.  This could
> be an optional behavior, to preserve backward compatibility.  Now the
> slave *isn't* an exact copy of the master, so PostgreSQL can
> distinguish.

I actually think such a protocol should be chosen.  As is I cannot say
"yeah, restarting postgres is always designed to work" in the presence
of backups.  Prior suggestions -- I think rejected -- were to use the
recovery.conf as such a sentinel file suggesting "I am restoring, not
being backed up".

> But it seems that this could also be worked around outside the
> database.  We don't have built-in clusterware, so there must be
> something in the external environment that knows which server is
> supposed to be the master and which is supposed to be the standby.
> So, if you're on the master, remove the backup label file before
> starting the postmaster.  If you're on the standby, don't.

Fundamentally this is true, but taking a backup should not make
database restart a non-automatic process.  By some definition one
could adapt their processes to remove backup_label at all these times,
but I think this should be codified; I cannot think of any convincing
reason have that much freedom (or homework, depending how you look at
it) to write one's own protocol for this from scratch.  From an arm's
length view, a database that cannot do a clean or non-clean restart at
any time regardless of the existence of a concurrent on-line backup
has a clear defect.

Here's a protocol: have pg_start_backup() write a file that just means
"backing up".  Restarts are OK, because that's all it means, it has no
meaning to a recovery/restoration process.

When one wishes to restore, one must touch a file -- not unlike the
trigger file in recovery.conf (some have argued in the past this
*should* be recovery.conf, except perhaps for its tendency to be moved
to recovery.done) to have that behavior occur.

How does that sound?  All fundamentally possible right now, but the
cause of slivers in my and other people's sides for years.

--
fdr