Обсуждение: [PATCH] Timestamp for a XLOG_BACKUP_END WAL-record
Hi, I prepared a patch which adds a timestamp into a XLOG_BACKUP_END WAL-record. This functionality is needed in practice when we have to determine a recovery time of specific backup. This code developed in compatibility with WAL segments, which do not have a timestamp in a XLOG_BACKUP_END record. -- Andrey Lepikhov Postgres Professional: https://postgrespro.com The Russian Postgres Company
Вложения
Hi, On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote: > This functionality is needed in practice when we have to determine a > recovery time of specific backup. What do you mean by "recovery time of specific backup"? > This code developed in compatibility with WAL segments, which do not have a > timestamp in a XLOG_BACKUP_END record. I don't understand what "compatibility with WAL segments" could mean? And how are WAL segments related to "XLOG_BACKUP_END record", except as to how every WAL record is related? Are you thinking about the switch records? Greetings, Andres Freund
On 10.07.2018 06:45, Andres Freund wrote: > Hi, > > On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote: >> This functionality is needed in practice when we have to determine a >> recovery time of specific backup. > > What do you mean by "recovery time of specific backup"? > recovery time - is a time point where backup of PostgreSQL database instance was made. Performing database recovery, we want to know what point in time the restored database will correspond to. This functionality refers to improving the usability of pg_basebackup and pg_probackup utilities. > >> This code developed in compatibility with WAL segments, which do not have a >> timestamp in a XLOG_BACKUP_END record. > > I don't understand what "compatibility with WAL segments" could mean? > And how are WAL segments related to "XLOG_BACKUP_END record", except as > to how every WAL record is related? Are you thinking about the switch > records? > In this case 'compatibility' means that patched postgres codes (pg_basebackup, pg_probackup, pg_waldump etc) will correctly read WAL segments which not contains a timestamp field in XLOG_BACKUP_END record. > Greetings, > > Andres Freund > -- Andrey Lepikhov Postgres Professional: https://postgrespro.com The Russian Postgres Company
On Tue, Jul 10, 2018 at 6:41 PM, Andrey V. Lepikhov <a.lepikhov@postgrespro.ru> wrote: > > > On 10.07.2018 06:45, Andres Freund wrote: >> >> Hi, >> >> On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote: >>> >>> This functionality is needed in practice when we have to determine a >>> recovery time of specific backup. >> >> >> What do you mean by "recovery time of specific backup"? >> > > recovery time - is a time point where backup of PostgreSQL database instance > was made. > Performing database recovery, we want to know what point in time the > restored database will correspond to. > This functionality refers to improving the usability of pg_basebackup and > pg_probackup utilities. Why don't you use a backup history file for that purpose? Regards, -- Fujii Masao
On 10.07.2018 22:26, Fujii Masao wrote: > On Tue, Jul 10, 2018 at 6:41 PM, Andrey V. Lepikhov > <a.lepikhov@postgrespro.ru> wrote: >> >> >> On 10.07.2018 06:45, Andres Freund wrote: >>> >>> Hi, >>> >>> On 2018-07-10 06:41:32 +0500, Andrey V. Lepikhov wrote: >>>> >>>> This functionality is needed in practice when we have to determine a >>>> recovery time of specific backup. >>> >>> >>> What do you mean by "recovery time of specific backup"? >>> >> >> recovery time - is a time point where backup of PostgreSQL database instance >> was made. >> Performing database recovery, we want to know what point in time the >> restored database will correspond to. >> This functionality refers to improving the usability of pg_basebackup and >> pg_probackup utilities. > > Why don't you use a backup history file for that purpose? Timestamp in a backup history file not correspond to any WAL record and can't be bind with a time of backup exactly. In my opinion, keeping timestamp in XLOG_BACKUP_END is more reliable, safe and easy way for recovering a database to a specific time. > > Regards, > -- Andrey Lepikhov Postgres Professional: https://postgrespro.com The Russian Postgres Company
On Fri, Jul 13, 2018 at 08:13:39AM +0500, Andrey V. Lepikhov wrote: > Timestamp in a backup history file not correspond to any WAL record and > can't be bind with a time of backup exactly. > In my opinion, keeping timestamp in XLOG_BACKUP_END is more reliable, safe > and easy way for recovering a database to a specific time. Like Andres and Fujii-san, I don't really see the point of complicating the code for that. If your goal is to stop WAL replay once consistency has been reached, then just use recovery_target = 'immediate' if you fetch the data from a WAL archive and that there are still segments after the consistency point. Or just use a self-contained backup which has all the WAL it needs without restore_command. If your goal is to make sure that the timestamp set in recovery.conf is not older than the moment the backup has ended, then the backup history file has what you are looking for. In short, in any case there is no point in duplicating data which already exists. You can as well use recovery_target_lsn to point exactly at the time a backup has ended as returned by pg_stop_backup, and this even saves maths with timestamps. -- Michael