Date: Thu, 30 Mar 2023 06:59:12 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: oernii+pg@axonpro.sk, pgsql-admin@lists.postgresql.org
Subject: Re: pg15 and VM snapshots with pg_backup_start
Message-ID: <ZCVrgDoofK5DHruW@tamriel.snowman.net>
References: <1bd4e56c-f6ad-f711-255a-4432b5f20a62@axonpro.sk>
 <99815bc4cd2622115ea417f6f1e3ceebf8a5247e.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="2Wg5nmR/dOc+6LQa"
Content-Disposition: inline
In-Reply-To: <99815bc4cd2622115ea417f6f1e3ceebf8a5247e.camel@cybertec.at>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/ZCVrgDoofK5DHruW%40tamriel.snowman.net>
Precedence: bulk


--2Wg5nmR/dOc+6LQa
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> On Thu, 2023-03-30 at 11:50 +0200, oernii+pg@axonpro.sk wrote:
> > Hi, we run a lot of PG14 and lower. We do VM snapshots, which we backup.
> > Before the snapshot we run a quiesce script which also runs pg_start_ba=
ckup().
> > After a few seconds the snapshot if finished and we call pg_stop_backup.
> > This is working file for us, but pg15 removed this function and replace=
d it
> > with pg_backup_start(), which however requires the session to be open, =
otherwise we get:
> > =A0=A0=A0 WARNING:=A0 aborting backup due to backend exiting before pg_=
backup_stop was called

This was removed because between the start and stop backup calls if the
system crashed, you would be in an ugly state where a backup_label file
exists but it isn't actually a backup, and the database system wouldn't
start.  While you may not have ever had this happen, the risk was
certainly there and you were just lucky to not have hit it yet.

> > For pg15 do I need to keep the session open, will the snapshot contain =
uncorruped data?
>=20
> If the snapshot is atomic, that is, represents a state that the file syst=
em had
> at sume point in time, PostgreSQL can always use it for recovery.

Right- but the entire set of database files must be included in the
single atomic snapshot and you really shouldn't try to use those for
PITR because you absolutely must replay all of the WAL from the snapshot
to the end to be sure you reach consistency.

> Otherwise, you cannot use this backup, because you only get "backup_label=
" from
> "pg_backup_stop()", and without "backup_label", you cannot recover the ba=
ckup.

You must absolutely also be doing WAL archiving properly and verify that
all of the WAL generated between the backup start and backup stop were
stored to be sure that you have a restorable backup.  Then you also need
to be sure to have the correct backup_label for the specific backup and
to restore that file when performing the restore.

All-in-all, this approach of using a snapshot and then copying it has
little advantage and many disadvantages over using a more traditional
approach to backing up the data, so I don't really recommend it.  I'd
strongly recommend using one of the built backup solutions which exist
and provide things like verifyable backups that have checksums for the
data backed up, ensure that all necessary WAL was copied, etc.

Thanks,

Stephen

--2Wg5nmR/dOc+6LQa
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmQla30ACgkQ7WyKOINH
ZFU/xRAAlqrvqoZ64C4m1isQyJ7tm8jnGUcB0L3oopCpL3/GjZNJuyAusXpX5QS8
na0QVPn7Z98/QmcQAtyEG7W6xOvabtfDl0J89O5kGjdgZ/enqbQF3vIWeDz07IJX
IcyzyAnUjKA6KzRFIrYncStnKoE2U6lpJOVbBJJjavDXdBR76PIPwcQXAQSiZMYQ
/rZ5p+hVyO0zUkxpoYERzuOUIC7P6UCGJrVcaPpRxXLlGVnbJbV8TmOBUnOO1iMi
RLNcji0fxf9Mhstt0kKJwMH7N9TFMcoOj01tjSdlkH+y9a0puejLPa3h8lF+B/5b
FooxKnOnLw3cQKhOM9vWOk064mC2Ri6ScsgmgU7ejUSD++0rhJi3Tg36OkeuvOAd
xFRd1MgqFnhKICKTKFCw7LZRprbR002BP0wiRq5vFa8oOSPjqoixuSNUEJS+NyTW
9ShfaLvix5iJTzYR4WSAS+rkcM+3ZNY4Hp/ZGqDypb/N7g8H4SUr8gVqLnnXtklD
q23WuScAgBPhSTiLvb8zz1uptRRqUrgwjOITsUSsQ/TGUkzH4p2SpMvZ0L5/c4H3
+tthxEw0/DprBrcgPB6ux50plstKqoLRuWlCsu8w4k9NSyspyokgVTdOVh8v9S5U
BLR0JswIirTL2ezw9GDVCuowRfWGL1HdQpgf9lC0IkhlD0iC6ZM=
=nDWA
-----END PGP SIGNATURE-----

--2Wg5nmR/dOc+6LQa--