Date: Mon, 8 Jul 2019 10:50:25 +0200
From: =?utf-8?Q?Thorsten_Sch=C3=B6ning?= <tschoening@am-soft.de>
Organization: AM-SoFT GmbH IT-Systeme
Message-ID: <171339952.20190708105025@am-soft.de>
To: pgsql-admin@postgresql.org
Subject: Does pg_start_backup changes how Postgres writes data?
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.
During a discussion with one of my coworkers he claimed that after
pg_start_backup executed, no writes into "the file system" happen
anymore. The only exception he makes is writes to the WAL.

From=20what I understand from reading the docs and multiple other
sources, I have a different opinion: The only thing pg_start_backup
changes regarding writes in the data directory of Postgres is that a
checkpoint is done as part of starting the backup, even if no
checkpoint would be needed currently otherwise. After that is done,
from my understanding, Postgres is free to write any file in the
directory at any given time again however it sees fit. That's why
inconsistent files are mentioned at many places:

> We do not need a perfectly consistent file system backup as the
> starting point.[...]

https://www.postgresql.org/docs/current/continuous-archiving.html

> Don=E2=80=99t forget that database activity, including checkpointing,
> continues normally in backup mode!

https://www.cybertec-postgresql.com/en/exclusive-backup-deprecated-what-now=
/#

> Base backup does NOT prevent writes to the data directory.
[...]
> The key point is that the base backup is NOT a consistent copy of
> the database.

https://www.2ndquadrant.com/en/blog/what-does-pg_start_backup-do/

So what's the case: Does pg_start_backup put any writing limitations
for Postgres in place or not? If so, I assume those would be removed
by pg_stop_backup.

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