Date: Mon, 8 Jul 2019 11:57:07 +0200
From: =?utf-8?Q?Thorsten_Sch=C3=B6ning?= <tschoening@am-soft.de>
Organization: AM-SoFT GmbH IT-Systeme
Message-ID: <488977827.20190708115707@am-soft.de>
To: pgsql-admin@postgresql.org
Subject: Do results of pg_start_backup work without WAL segments created
 during backup?
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Hi all,

we are reviewing our current backup process based on the low level
pg_start_backup and pg_stop_backup using the exclusive approach. I
wonder how important the WAL-archives created during backup really are
in terms of if they are necessary to get Postgres up and working at
all.

The docs mention that after pg_start_backup has been issued, files of
the data directory of Postgres can be copied however one likes. The
important point seems to be that pg_start_backup does checkpointing,
so that all data until the start of the backup gets written to disk.
Afterwards, additional writes can happen to any file at any given time
and changes are recorded using the WAL like normal.

What happens WITHOUT the WAL-archives created during the backup when a
cluster needs to be restored?

From=20my understanding, the cluster restores as normal, but only up
until the point when pg_start_backup executed. Without additionally
shipping the WAL-archives later, one would simply loose the data
created after pg_start_backup has been called. But once the data
directory has been copied, one can add any WAL-archives later whenever
one likes and they get recognized during restore. In this case, the
WAL-archives created during backup are simply the same incremental
changes like all other WAL-archives created after pg_stop_backup.

OR are the copied files so fundamentally broken that Postgres is not
able to operate at all without the WAL-archives during backup?

Wouldn't make much sense to me, because Postgres needs to operate
properly already to replay the WAL-archives. It needs to know from
which checkpoint to start, which is available after using
pg_start_backup. From my understanding, there's no info created by
pg_start_backup about additionally necessary WAL-archives blocking
bringing up the cluster successfully if not present. If none are
available, nothing gets replayed, but things still work.

Or am I getting the importance of the additional WAL-archives during
backup completely wrong? Thanks!

Mit freundlichen Gr=C3=BC=C3=9Fen,

Thorsten Sch=C3=B6ning

--=20
Thorsten Sch=C3=B6ning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Gesch=C3=A4ftsf=C3=BChrer: Andreas Muchow