What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL?

Hi everyone,

there's the following example in the docs about hot backups:

> touch /var/lib/pgsql/backup_in_progress
> psql -c "select pg_start_backup('hot_backup');"
> tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
> psql -c "select pg_stop_backup();"
> rm /var/lib/pgsql/backup_in_progress
> tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-TIPS

These are the important things to keep in mind from my understanding:

1. The data directory of Postgres is designed to be crash safe[1] and
unless files are forcefully deleted or otherwise damaged, PG should
recover from a crash. It does so with the help of its WAL files, so
those define to which point PG recovers, which data is actually
available, which state databases have in the end etc.

2. Copying the data directory might take a long time, during which PG
is free to write to any file. Though, "pg_start_backup" guarantees
crash safety again by creating a CHECKPOINT[2] for the data directory
and additional WALs are used to record any changes during copying.
"pg_stop_backup" tells about the last WAL necessary to replay all
changes during the backup, regardless of how the data dir changed at
the same time. Things are based on crash safety of "pg_start_backup".

When having atomic file system snapshots available, the advice is to
simply use those[1], because PG has crash safety. State is frozen this
way and when actually copying files, changes during how long the copy
takes are NOT taken into account anymore because of the snapshot.

Without those snapshots pg_start_backup+copy+pg_stop_backup+WAL is
suggested instead[1]. WAL-archiving assures that all changes during
the copy up until the logical end of the backup issued by executing
"pg_stop_backup" are available.

But what if I'm not interested in the changes during copying the data
directory? This is exactly the same use case like when using file
system snapshots, for which I don't get changes during the backup
process itself as well.

So a process of "pg_start_backup", directly followed by a
"pg_stop_backup" with assuring that the WALs during both calls are
preserved, should result in a valid backup, shouldn't it?

An afterwards copied data directory is crash safe again up to the
point "pg_start_backup" has been called and can recover using the WALs
up to the point "pg_stop_backup" has been called. Regardless how long
copying the data directory takes and which changes Postgres applies.
Those changes are simply discarded during recovery, aren't they?

So one might lose changes during the backup process by not following
the order of the docs, but again, I might not care, like with file
system snapshots.

Or what's the difference when looking at consistency/recovery compared
to some point in time only between FS level snapshots and using
pg_start|stop_backup+WALs in different order?

From my understanding, pg_start_backup+pg_stop_backup+FS-snapshot
should even be the most correct approach, as long as the created WALs
are contained in the snapshot. Other thoughts?

Thanks!

[1]: https://dba.stackexchange.com/a/44262/99683
[2]: https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

Mit freundlichen Grüßen

Thorsten Schöning

--
AM-SoFT IT-Service - Bitstore Hameln GmbH
Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: Thorsten.Schoening@AM-SoFT.de
Web:    http://www.AM-SoFT.de/

Tel:   05151-  9468- 0
Tel:   05151-  9468-55
Fax:   05151-  9468-88
Mobil:  0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil:
Webseite: https://www.am-soft.de

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister
fürIT und TK 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus - Bitstore Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT – Consulting GmbH
NL Piesteritz
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln
Geschäftsführer Janine Galonska