Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m70kX-0000Tz-76 for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 19:22:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m70kW-0007mC-3G for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 19:22:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m70kV-0007m4-Qc for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 19:22:19 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m70kP-0004q5-I0 for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 19:22:18 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id CC9105F799; Fri, 23 Jul 2021 15:22:12 -0400 (EDT) Date: Fri, 23 Jul 2021 15:22:12 -0400 From: Stephen Frost To: Thorsten =?iso-8859-1?Q?Sch=F6ning?= Cc: pgsql-admin@lists.postgresql.org Subject: Re: What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL? Message-ID: <20210723192212.GO20766@tamriel.snowman.net> References: <1208702063.20210723115638@am-soft.de> <399c734c5b9ffe10f68bcb79a69bea000d8d0c21.camel@cybertec.at> <1751752722.20210723161202@am-soft.de> <20210723152418.GK20766@tamriel.snowman.net> <562203777.20210723194435@am-soft.de> <6780C96C-3016-47C8-AD06-BF860283266C@elevated-dev.com> <1837868909.20210723211517@am-soft.de> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="mAF9Hyy2Le5u3UCW" Content-Disposition: inline In-Reply-To: <1837868909.20210723211517@am-soft.de> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --mAF9Hyy2Le5u3UCW Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Thorsten Sch=F6ning (tschoening@am-soft.de) wrote: > Guten Tag Scott Ribe, > am Freitag, 23. Juli 2021 um 20:07 schrieben Sie: >=20 > > Because it is in the file. WAL can re-do changes, it cannot undo them. >=20 > But isn't visibility of rows associated to transaction numbers as > well? I would have expected the transaction number for the row in B > simply not being available, because it was never part of the WALs up > to and including pg_stop_backup when not following the official > ordering of commands. So I expected that row to simply not be visible > at all and vacuumed at some point or whatever. The information about what transactions have committed and what haven't isn't just in the WAL- it's in the tuple header for frozen tuples, or all visible tuples, and it's in the transaction log files otherwise, so it would depend on when those are copied. Thanks, Stephen --mAF9Hyy2Le5u3UCW Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJg+xbkAAoJEO1sijiDR2RVVuQP/iD55Os3zZ1BuYtzUBJ0xRCg ezLFXN/Ql6r+aTfQvU1XT/R2U2UmDPoZtryaPNLkbtLG/QSH+fGdnGAPWFTRJHFl XZ2bk9ecT2OQ7coFSXQXPG7vbE51jInisSPrXvZ1wDcPQHjOJLLPlwbJvILFVP8i Ez0lnduN34E7oz89XF5qvpQHGFAkaj+BpLXGgVXLHsXLPFSAcGTlWuMWucgZfhya XMhUGOmrvYQy/QyNW2q7NHD+MnsW5yi2u7kpBFX8DXgR8vgfsDUAl5bfzo+lTFQy 2jJg+iflrKppl6XQZ51K7y9CJY1DCCWJ8gIa2DhMRvoA/ImI8lunyx+KOMbJM+d9 pJfDKMl9lsLPVMT/og8pobMIh3a27A2tZLwsLmWVxoHZuqLOzhlyD5keJpyPxgVY /x8mQ6gcUGdIzOZlM2qrLN05p85OCEMzKsI3Zx3EgVYziujGebquJ1ipYRDbotJM tzxAMi/3HfbQm+If5Q/zmWRxz5f0WNug/WFxBCDD0GmJ5B+6+FdQ2I8HW9xB2dWV SwjgEmMebj54RnW9a59nSy0FIzvXuZC89Ew71RhjN9eu/kC0vY3P45D5gOyi2tSr 67bQ4mro+kyD1jcHjtXc6lOt+sAt62cV53QzPmLksIvuqx6hlJYJkpxsWGkwhuDh IzFJMLaL8SURKH1HWbq5 =s0U6 -----END PGP SIGNATURE----- --mAF9Hyy2Le5u3UCW--