Re: What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL?

On 7/23/21 11:24 AM, Stephen Frost wrote:
> * Thorsten Schöning (tschoening(at)am-soft(dot)de) wrote:
>> Guten Tag Laurenz Albe,
>> am Freitag, 23. Juli 2021 um 12:41 schrieben Sie:
>
>> That recovering is the only difference: In case of file system
>> snapshots it recovers beginning from the last CHECKPOINT, as that is
>> considered safe, and applies additional WALs as available.
>
> Yes, it's understood that everything written to the heap prior to the
> latest checkpoint was sync'd, and trustworthy, and everything written
> after that point is in the WAL, and we know the end of that WAL because
> we read through the WAL that existed at the time and discover what the
> end point was.

There is another wrinkle here. If you copy files outside of
pg_start/stop_backup() then postgres will not replay over any torn (and
in some cases zero) pages, even if a full page write is available. Torn
pages are expected during crash recovery and backup recovery before the
end-of-backup WAL record. Outside of those cases you'll get a fatal error.

In that case your backup would be entirely useless, even if you knew
exactly where to replay to.

Regards,
--
-David
david(at)pgmasters(dot)net