Re: pg_start_backup without checkpoint patch (a part of Synch Rep)

Fujii Masao wrote:
> On Mon, Dec 29, 2008 at 6:08 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> Fujii Masao wrote:
>>> Attached is the self-contained patch to skip checkpoint at
>>> pg_start_backup.
>>> This is a part of Synch Rep patches, and was discussed in the following
>>> thread.
>>>
>>> http://archives.postgresql.org/message-id/3f0b79eb0812240710j7e613f3atfd6b6fc27403546e@mail.gmail.com
>> I'm not convinced that this is necessary for the replication patch. It is an
>> orthogonal, new feature, and should be considered for 8.5, IMHO.
>
> Synch Rep forces online-backup in some scenes (e.g. catchup after failover),
> so I've argued from the beginning that the cost of it should be reduced.

Sure, but it's just an optimization. Synch rep still works fine without it.

>>> Specifically, pg_start_backup uses the last checkpoint instead of doing a
>>> new checkpoint if full_page_writes = on since the last checkpoint, which
>>> guarantees that all the full-pages required for PITR are written.
>> That assumes that the DBA has kept all the WAL segments that have been
>> archived since last checkpoint. So this would no longer be safe:
>>
>> 1. rm <archivedir>/*
>> 2. SELECT pg_start_backup();
>> 3. tar cvzf backup.tar.gz <datadir>
>> 4. SELECT pg_stop_backup();
>
> Umm... I can't believe that there is the DBA to carry out such unsafe
> operation. If disk crash occurs between 1 and 2, a database might
> not recover *regardless of this patch* because of some missing xlogs.

I use the above rm+start+tar+stop procedure all the time, when testing PITR.

You might be taking a one-off online backup, in which case you don't
care about the history. See section 24.3.5.1 on "Standalone hot
backups":
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP

> I don't think that the patch itself is unsafe. In this patch, the DBA
> can still judge safely which file can be removed, from a backup history
> file or the return value of pg_start_backup.

Sure, it could be addressed with appropriate documentation. You'd
effectively get the current behavior by doing a manual CHECKPOINT just
before pg_start_backup(), for example. It does still make it harder to
use the feature correctly, though.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com