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

[Venkata B Nagothi]

> On Fri, Nov 4, 2016 at 7:04 PM, Venkata B Nagothi 
> 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

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

[Michael Paquier]

On Fri, Nov 4, 2016 at 7:04 PM, Venkata B Nagothi  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 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.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Venkata B Nagothi]

On Fri, Nov 4, 2016 at 3:44 PM, Michael Paquier 
wrote:

> On Fri, Nov 4, 2016 at 1:18 PM, Venkata B Nagothi 
> wrote:
> > I see the following contents in the file
> > "00010044.0060.backup" which was generated in the
> pg_wal
> > location during the online backup. When pg_stop_backup() is executed, the
> > following content is written which includes the content copied from the
> > backup_label file.
> >
> > [...]
> >
> > Can someone please help me know the importance of the above file?
>
> It is not actually critical, and useful for debugging (you could say
> the same about backup_label.old).
>
> > How about having the same contents in the backup_label file as well?
>
> > As of now, the backup_label file does not have any information related to
> > when and at what position the backup actually completed.
>
> Yes, and it is not actually possible to write the stop information
> because when a backup finishes the backup_label is simply removed and
> it has been included in the backup before it finishes. The role of
> this file is to provide the LSN start location from which the backup
> is able to replay things cleanly. The stop position, aka when
> everything on disk is consistent, is determined at replay by the
> XLOG_BACKUP_END record. This stop position is not something you can
> know when the backup_label file is generated. And I am of course
> talking about exclusive backups here.
>

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.

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.

Regards,

Venkata B N
Database Consultant

Fujitsu Australia

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

[Michael Paquier]

On Fri, Nov 4, 2016 at 1:18 PM, Venkata B Nagothi  wrote:
> I see the following contents in the file
> "00010044.0060.backup" which was generated in the pg_wal
> location during the online backup. When pg_stop_backup() is executed, the
> following content is written which includes the content copied from the
> backup_label file.
>
> [...]
>
> Can someone please help me know the importance of the above file?

It is not actually critical, and useful for debugging (you could say
the same about backup_label.old).

> How about having the same contents in the backup_label file as well?

> As of now, the backup_label file does not have any information related to
> when and at what position the backup actually completed.

Yes, and it is not actually possible to write the stop information
because when a backup finishes the backup_label is simply removed and
it has been included in the backup before it finishes. The role of
this file is to provide the LSN start location from which the backup
is able to replay things cleanly. The stop position, aka when
everything on disk is consistent, is determined at replay by the
XLOG_BACKUP_END record. This stop position is not something you can
know when the backup_label file is generated. And I am of course
talking about exclusive backups here.

For non-exclusive backups, it could be possible to add some
information related to the backup stop locations directly in the
backup_label file generated and returned to the caller, though the
information it would contain does not seem much pertinent as what
really matters is the LSN end location, and that's already a piece of
information available.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Contents of "backup_label" and "*.backup" in pg_wal location

[Venkata B Nagothi]

Hello Hackers,

I have a question regarding the contents being written to the backup_label
file and the .backup file in the pg_wal location generated when the online
backup is done.

backup_label file contents are as follows, which do not contain backup stop
position (timestamp and STOP WAL location). The below information is
written when pg_start_backup() is done.

START WAL LOCATION: 0/4460 (file 00010044)
CHECKPOINT LOCATION: 0/4498
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-11-04 14:24:25 AEDT
LABEL: 222

I see the following contents in the file
"00010044.0060.backup" which was generated in the
pg_wal location during the online backup. When pg_stop_backup() is
executed, the following content is written which includes the content
copied from the backup_label file.


START WAL LOCATION: 0/4460 (file 00010044)
STOP WAL LOCATION: 0/44000168 (file 00010044)
CHECKPOINT LOCATION: 0/4498
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-11-04 14:24:25 AEDT
LABEL: 222
STOP TIME: 2016-11-04 14:25:09 AEDT


Can someone please help me know the importance of the above file ?
How about having the same contents in the backup_label file as well ?

I am working on another patch and was thinking it would be good if the same
contents can be written to backup_label file as well. Actually, it would be
more beneficial to do so.

As of now, the backup_label file does not have any information related to
when and at what position the backup actually completed.

Any thoughts / apprehensions ?

Regards,

Venkata B N
Database Consultant

Fujitsu Australia