Обсуждение: Does pg_start_backup changes how Postgres writes data?
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 what 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’t 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üßen, Thorsten Schöning -- Thorsten Schöning 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äftsführer: Andreas Muchow
On Mon, Jul 8, 2019 at 10:50 AM Thorsten Schöning <tschoening@am-soft.de> wrote:
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.
That's incorrect. Writes still happen to the filesystem. Otherwise, the system might not be able to function properly if the backups take a long time.
The only difference to writing that pg_start_backup() does is that it forces full_page_writes to be on during the backup. The only difference to the non-wal part of the filesystem is the checkpoint, as you mention below.
(Also, you should really look into using the non-exclusive version of pg_start_backup(), as the exclusive one has several dangers to it. See the warnings and documentation under 25.3.3.2 at https://www.postgresql.org/docs/12/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP -- the same issues apply to previous versions, but the docs were less clear).
From what 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:
This is correct.
--
Greetings, * Thorsten Schöning (tschoening@am-soft.de) wrote: > From what 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, Magnus' already replied to most of this, but I wanted to point out that you can control if pg_start_backup forces an immediate checkpoint or if it waits for the checkpoint to complete naturally. Thanks, Stephen