Re: pg_start_backup does not actually allow for consistent, file-level backup

On Mon, Jun 8, 2015 at 9:26 AM, otheus uibk <otheus(dot)uibk(at)gmail(dot)com> wrote:

> On Mon, Jun 8, 2015 at 3:13 PM, otheus uibk <otheus(dot)uibk(at)gmail(dot)com> wrote:
>
>> Thank you, all. The manual for 9.4 is indeed clearer on this point than
>> the 9.1 version.
>>
>
> Just to nit-pick, I see nowhere in either version of the manual the
> indication that it is normal for postgresql to continue to update files in
> its data catalog between pg_start_backup and pg_stop_backup. The closest I
> see comes in this paragraph:
>
>
It may not be explicit because the author assumes that such writing
occurring is self-evident. If it did not then for any reasonably sized
database the system would I/O starve or simply run out of memory as the
changes continue to pile up in the buffers while the backup is running.

The normal operation of the database operates on the same principle. In
between checkpoints the data files are constantly being written. A crash
means that there is more data in WAL to write out to the data files. The
data files cannot be reverted back to their state at the time of the
checkpoint. Instead, the replay of the WAL simply reapplies every change -
even those that did make it out during the period between the checkpoint
and the crash.

​As far as the backup routine is concerned a checkpoint occurs only when
you call pg_start/end_backup and the system ensures that every WAL file
generated during the intervening period is kept around so that the data
files being copied, and changed, in the intervening period can be made
whole. The first checkpoint ensure that all data in present in those files
and a restoration will replay every WAL file to ensure that the final state
of each data file matches the state of the database as of the time the last
WAL file present was created (typically the one closed out at the
pg_end_backup call).

David J.​