pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes pg_start_backup changes how Postgres writes data?
3+ messages / 3 participants
[nested] [flat]
* Does pg_start_backup changes how Postgres writes data?
@ 2019-07-08 08:50 Thorsten Schöning <tschoening@am-soft.de>
0 siblings, 2 replies; 3+ messages in thread
From: Thorsten Schöning @ 2019-07-08 08:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does pg_start_backup changes how Postgres writes data?
@ 2019-07-08 08:58 Magnus Hagander <magnus@hagander.net>
parent: Thorsten Schöning <tschoening@am-soft.de>
1 sibling, 0 replies; 3+ messages in thread
From: Magnus Hagander @ 2019-07-08 08:58 UTC (permalink / raw)
To: Thorsten Schöning <tschoening@am-soft.de>; +Cc: pgsql-admin
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).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Does pg_start_backup changes how Postgres writes data?
@ 2019-07-08 14:52 Stephen Frost <sfrost@snowman.net>
parent: Thorsten Schöning <tschoening@am-soft.de>
1 sibling, 0 replies; 3+ messages in thread
From: Stephen Frost @ 2019-07-08 14:52 UTC (permalink / raw)
To: Thorsten Schöning <tschoening@am-soft.de>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-07-08 14:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-08 08:50 Does pg_start_backup changes how Postgres writes data? Thorsten Schöning <tschoening@am-soft.de>
2019-07-08 08:58 ` Magnus Hagander <magnus@hagander.net>
2019-07-08 14:52 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox