Обсуждение: Soften pg_[start|stop]_backup to allow them on a standby?
Hi all, This is perhaps something that has already been discussed on hackers, I just could not find anything in the archives. 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. Could we consider soften the rules of pg_start_backup and pg_stop_backup (aka allow creation of a backup_label file) to be able to run them on a standby? Or not? This could accelerate taking backups from standbys when taking backups locally. Another idea would be to send the backup label file directly as the output of pg_start_backup such as client application can grab it and reuse it. Any thoughts about that as well? Note that pg_basebackup uses the command BASE_BACKUP to bypass that and send directly the label file through a stream without writing it to disk, satisfying this condition and making possible the creation of backups from a standby. Regards, -- Michael
Hi, 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. > Another idea would be to send the backup label file directly as the > output of pg_start_backup such as client application can grab it and > reuse it. Any thoughts about that as well? Yea, I think extending the "protocols" available is the way to go here. We need to be able to send the backup label after the actual base backup finished. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Jan 14, 2014 at 7:54 AM, Andres Freund <andres@2ndquadrant.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
On 2014-01-15 11:19:52 -0500, Robert Haas wrote: > On Tue, Jan 14, 2014 at 7:54 AM, Andres Freund <andres@2ndquadrant.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