Message-ID: <32a24a871174e9362225be407a234251e837d9aa.camel@cybertec.at>
Subject: Re: What's the dfifference between
 pg_start_backup+copy+pg_stop_backup+WAL vs.
 pg_start_backup+pg_stop_backup+copy+WAL?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Thorsten =?ISO-8859-1?Q?Sch=F6ning?= <tschoening@am-soft.de>,
	pgsql-admin@lists.postgresql.org
Date: Fri, 23 Jul 2021 18:26:18 +0200
In-Reply-To: <1751752722.20210723161202@am-soft.de>
References: <1208702063.20210723115638@am-soft.de>
	 <399c734c5b9ffe10f68bcb79a69bea000d8d0c21.camel@cybertec.at>
	 <1751752722.20210723161202@am-soft.de>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/32a24a871174e9362225be407a234251e837d9aa.camel%40cybertec.at>
Precedence: bulk

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