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

On Fri, 2021-07-23 at 16:12 +0200, Thorsten Schöning wrote:
> I think your example is bad: If both rows are added in independent
> transactions, possibly inconsistent table data simply needs to be
> accepted. No backup mechanism will change anything on that, because
> the second row might always be inserted "too late".

Stephen has already pointed out the flaws in your reasoning, so let
me illustrate my example in more detail:

You add the rows in A and B in two different transactions: first
the row in A, then the dependent row in B.

At that time the data file for A has already been backed up.
After both rows are committed, the data file for B is backed up.

Now if you replay WAL to a point in time before all that took
place (which you can do, because your BACKUP_END record was written
before you started your backup), you will have the row in B, but
not the row in A. Data corruption.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com