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

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).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company