Re: Contents of "backup_label" and "*.backup" in pg_wal location

> On Fri, Nov 4, 2016 at 7:04 PM, Venkata B Nagothi <nag1010(at)gmail(dot)com>
> wrote:
> > Sure. I will look at the possibility of using XLOG_BACKUP_END in my
> patch.
> > I am looking at the possibility of keeping the backup_label at source
> until
> > pg_stop_backup()
> > is executed and then write the STOP information and then move it across
> to
> > the backup location.
>
> Non-exclusive backups already do that, except that as the backup is
> already stopped at the moment the backup_label information is sent
> back to the caller, and it is expected that it will be the caller that
> will write its contents into the backed up PGDATA in a file named as
> backup_label. Anyway, any design in this area for exclusive backups
> would be really inconsistent. For example take the case of
> pg_start_backup -> cp -> pg_stop_backup for an exclusive backup, when
> are you going to update the backup_label file with the stop info?
>

I was wondering if writing the stop info in the backup_label at all. I am
not sure about this possibility.

> I see that when the START/STOP information is written to the WAL history
> > file,
> > the content from the backup_label file is being copied and I am thinking
> to
> > do the same other way around.
> >
> > Am i making sense ? is that anyway not possible ?
> >
> > If this makes sense, then i would start working on an optimal design and
> > look at the possibility of achieving this.
>
> Before writing any code, I would be curious about the need behind
> that, and you give no reason where this would help in this thread. Do
> you actually want to time the timestamp when backup ends? This could
> be added as a new field of pg_stop_backup for both the exclusive and
> non-exclusive cases. Just an idea.
>

Sure. If the time stamp, (and possibly lsn as well) could be added as a new
field to the pg_stop_backup. Will work on this.
I am writing a patch (which is being discussed in the other thread) which
will ensure appropriate errors/hints
are thrown in various scenarios when recovering to a particular target like
time, lsn or xid (as suggested by Stephen which makes a lot of sense).

Example :

*Scenario 1*

- If you mention a recovery_target_time which falls prior to the
backup-start-time, then, errors/hints will generated without initiating the
recovery process which not the case now. PostgreSQL prefers recovering till
immediate consistent recovery target or end-of-the-wal. This can achieved
and is already addressed in the patch. So, no issues.

*Scenario 2*

- Similarly, if the specified recovery_target_time is later than the
backup-start-time and is falling before backup-end-time, then, error/hint
would be generated which says "recovery target time is well before the
consistent recovery point..". The same could be done for
recovery_target_lsn as well. To achieve this, it would be good to have
backup-end-time, backup-end-wal-position in the backup_label. This needs to
be addressed and i am exploring ways to achieve this.

Regards,

Venkata B N
Database Consultant

Fujitsu Australia