Re: Soften pg_[start|stop]_backup to allow them on a standby?

On 2014-01-15 11:19:52 -0500, Robert Haas wrote:
> On Tue, Jan 14, 2014 at 7:54 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-01-14 12:31:09 +0900, Michael Paquier wrote:
> >> Currently, pg_start_backup and pg_stop_backup cannot run on a standby
> >> because it is not possible to write a backup_label file to disk,
> >> because of the nature of a standby server preventing to write any data
> >> in its PGDATA. Is this thought right? This is what the comments at the
> >> top of do_pg_start_backup make me conclude.
> >
> > No, the actual reason is that a plain pg_stop_backup() writes WAL -
> > which we can't do on a standby. The walsender command gets around this
> > by storing the required data in the backup label itself, but that
> > requires the label to be written after the basebackup actually finished
> > which doesn't work for plain start/stop backup.
>
> This is true, but a better way to say it might be that when we fire up
> postgres and point it at the backup, it needs to begin recovery at a
> checkpoint; otherwise, pages torn by the backup process won't get
> fixed. Maybe there's a way that pg_start_backup() could piggyback on
> the most recent checkpoint rather than performing one itself; if so,
> such a mode could be used on either the master or the standby (but
> would require replaying more WAL, of course).

That's what the walsender variant essentially already does for backups
taken in recovery. What that does not solve is correctly setting the min
recovery point though. I don't immediately see how we could compute that
correctly without either a wal record or a backup label written at the
end of recovery.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services