Date: Fri, 23 Jul 2021 14:10:16 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Thorsten =?iso-8859-1?Q?Sch=F6ning?= <tschoening@am-soft.de>
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: <20210723181016.GN20766@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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="E5DfLqqzHtf91MeV"
Content-Disposition: inline
In-Reply-To: <562203777.20210723194435@am-soft.de>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210723181016.GN20766%40tamriel.snowman.net>
Precedence: bulk


--E5DfLqqzHtf91MeV
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 Stephen Frost,
> am Freitag, 23. Juli 2021 um 17:24 schrieben Sie:
>=20
> > [...]Similar, once you reach the BACKUP_END record of
> > a backup you know the database is consistent- but only if all of the
> > files that PG was writing to were copied between the start checkpoint
> > of the backup and the BACKUP_END record in the WAL.
>=20
> So I am correct that the recovery process starts at what is documented
> as CHECKPOINT LOCATION in the file backup_label? If the data directory
> looks like a more current checkpoint might be available, that is NOT
> used? Instead, all available WALs are replayed?

Yes, the recovery has to start from the checkpoint in the backup label.
The fact that pg_control might have a more recent checkpoint is exactly
the problem- any files which were copied before the checkpoint in
pg_control wouldn't have the correct contents and wouldn't be fixed by
the recovery process.  Furthermore, if PG were to stop replaying before
getting to the correct backup-end location then the database might not
be consistent at that point because some records had been written into
the WAL as committed but hadn't made it to the heap yet.  There's risk
on both sides.

> But how come the state of other data files into the recovery process?
> that's what Laurenz Albe's example is about: A data file which is more
> current than expected because that file is only copied, but the
> associated changes are stored in WALs after pg_stop_backup and
> therefore need to be considered lost.

I'm not sure that you're quite understanding the example because we
always write to WAL first and sync that before writing to the heap, so
there isn't a case of a "more recent" file which is somehow ahead of
what's in the WAL.

> Why is the data file with that additional row in B used at all? The
> additional row is associated with a transaction number not available
> when restoring based on CHECKPOINT LOCATION + WALs of pg_stop_backup.

The data file is copied because it's part of the database..?  If you
didn't copy it, then you'd be missing a lot of data.  As for the
question about if that row would be visible, it certainly might be- but
it depends on when the transaction log files were copied, or if perhaps
the tuple ended up being frozen somehow.

> So why is that row used at all instead of simply ignored? That would
> actually revert the cluster to the state of pg_stop_backup, but as
> said, backup outdate anyway. I still don't get why the result is not
> as consistent as e.g. a crash at the same point in time instead of
> doing a backup.

How would you know to ignore it if that tuple was frozen?

A crashed PG cluster isn't consistent until WAL replay from the last
checkpoint to the ending point of WAL has completed.  That's exactly the
same with a backup, but the checkpoint you have to start from is the one
stored in the backup label and the point in the WAL that has to be
reached for consistency is the one that's at the backup-end location in
the WAL.  Otherwise they're both the same.  The reason you have to start
=66rom the start-backup checkpoint is exactly because the data files are
being copied over a period of time and not all at once- unlike with a
crash where everything stops at the same time (and if that doesn't
happen, you definitely do have the risk of having corruption from such a
partial-crash, though we've tried our best to detect such cases and deal
with them, but we need the kernel to actually tell us when there's a
problem and that's been something of an interesting challenge..).

> Mit freundlichen Gr=FC=DFen
>=20
> Thorsten Sch=F6ning

Seriously, when your signature is twice the size of your email, it's too
much.  Please cut it down when posting to these lists, we certainly
don't need hundreds of copied of it in our archives or everyone's mail
boxes.

Thanks,

Stephen

--E5DfLqqzHtf91MeV
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJg+wYIAAoJEO1sijiDR2RVmRUP/AxCRPt7kmVCplpsuE7fzGfV
lgQzdJrK57MWmBvGe6/+6JHo5NQM3WNDlxR9n1M9bAXntoDMUJWS75k8PAmWJvvc
BaGcL5LIoN+HupLVdBVSHBSl2049wlsdarPJDLYneNDK5ZcQvEN2eUggfPQR3lig
8RaobITAuiKUo0dRCN8ROYwMaZKYyYOmPhBthFfx9PpFoA84Ld9HIZ8R2THjovnj
3jgebo3/L3Rmcuec3Yht5Qnizl0XUY/vZ/GjL/3yja91IZShzao34zru51zvWEmO
n9ih8MrI1JkXspVQ3mWgZQ+T16ptMO9M9Xje5N1dLIoH8E2IIZEg0NjsqbXsOqbA
dALH+jMcRtDwtsvRF89JI/i89cQA1x3Jwf6SPsqZLiw9rGqXHbx1q08Y0OWVHU0S
PIWmTolGuN07qOL61C1pO8wXomRKGHjLw2UsCxY6E/VFdXMq1baPmGp2R7lE2v5d
G4Iw7didoCe+xfCwhIobtsnXGOkQpyxUgk+uIyxzhntxugHz2x3hBhAythLKCgDN
hOTfgBPb2wBfmEN3cjxM5E3mKszqVmt0X2p1dEof8ikGhznhY/NBaB4sR0MCQNql
m/gePceEozrVL3+jCdenOBmiXQJDUvVqhFfWDAyaaHDJPWI4mP1gsNpe1i02iDkv
kzINKa1/8SY9Zvb2iLJ+
=uCox
-----END PGP SIGNATURE-----

--E5DfLqqzHtf91MeV--