Обсуждение: Do results of pg_start_backup work without WAL segments created during backup?

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 my 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üß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

Guten Tag Magnus Hagander,
am Montag, 8. Juli 2019 um 12:02 schrieben Sie:

> Then you have a corrupt and unusable backup.

But corrupt and unusable in what way? It obviously can't be that
corrupt that Postgres doesn't start at all or it wouldn't even be able
to replay WAL-archives, if present. But that's how the process works.
So if Postgres is designed to start with the copied files only, what's
the internal state it has? Why is it not a fully functional state with
only the data available when pg_start_backup ran?

> The pg_start_backup/pg_stop_backup method for backups can *only* be used
> together with a working log archive.

PITR is defined by having some arbitrary past data directory and
applying WAL-changes up until the point of interest. So not applying
WAL-archives is part of the whole design. Why are the few WALs created
during backup any special?

> No, without the WAL generated betweens tart and stop backup, the cluster
> will be incomplete and corrupt. In your description you are for example not
> counting for activity that happens *during* the checkpoint.

But why do I need to care? From my understanding, Postgres does
checkpointing to flush buffers and persist data into actual table
files to get a consistent state of its data. Exactly that's what
pg_start_backup guarantees as well, it only doesn't guarantee that no
changes are applied afterwards, but there is one consistent state.

If it was otherwise, how could crash recovery of Postgres work at all
if things always depend on WAL-segments? Don't failures to write
WAL-segements only result in missing data instead of a completely
broken cluster? From my understanding, what gets copied after
pg_start_backup is a cluster in somewhat the same state like after a
crash of Postgres.

> [...]If there are any doubts whatsoever on how they interact in your
> environment, you should *really* be looking at one of those higher level
> tools.

I am, something like barman on my NAS would be great. But currently
I'm left with a backup script only doing pg_start|stop_backup, copying
(parts of) the data directory and not taking the WAL-archives created
during the backup into account. So I'm trying to understand what I
have there, if the backup is completely useless that way or not.

From my point of view it looks like some usable full backup simply
missing incremental parts, which are the WAL-archives. So adding some
download of all WAL-archives available at some point in time without
caring if they have been created during the full backup or afterwards,
would make the backup more current easily.

If the full backup works at all... :-) And if it doesn't, I would like
to know what I don't understand yet. So in any case thanks for your
arguments!

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 7/8/19 6:56 AM, Thorsten Schöning wrote:
> 
> If the full backup works at all... :-) And if it doesn't, I would like
> to know what I don't understand yet.

It doesn't.

pg_start_backup() does a checkpoint, but then the database continues
writing as you copy the files in whatever order you choose.  You may
copy a file that has a partial write or copy some files involved in a
transaction before it happens and others afterwards -- in fact this is
normal and expected.

The checkpoint constrains the range of WAL that you need, but that WAL
is absolutely needed to reconstruct the changes that happened during the
backup.

pg_basebackup is a better alternative than rolling your own and there
are third-party solutions that offer more functionality.

Above all be sure to test your backups.  If you test the backups you are
currently taking you will quickly find that they do not work, i.e. they
will not recover to consistency.

Regards,
-- 
-David
david@pgmasters.net

Guten Tag David Steele,
am Montag, 8. Juli 2019 um 14:12 schrieben Sie:

> pg_start_backup() does a checkpoint, but then the database continues
> writing as you copy the files in whatever order you choose.  You may
> copy a file that has a partial write or copy some files involved in a
> transaction before it happens and others afterwards -- in fact this is
> normal and expected.

And because that's expected, Postgres can successfully restore from
that, e.g. having used checkpoints before:

> [...]This log exists primarily for crash-safety purposes: if the
> system  crashes, the database can be restored to consistency by
> “replaying” the log entries made since the last checkpoint.

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

"since the last checkpoint": Missing WAL-segments mean a loss of data
only. It doesn't mean that formerly "checkpointed" data gets magically
broken, else crash recovery wouldn't work like described in the docs.

> The checkpoint constrains the range of WAL that you need, but that WAL
> is absolutely needed to reconstruct the changes that happened during the
> backup.

Which makes sense if all WAL-archives are simply considered to be
incremental changes based on some former full backup. But that's the
point: I don't see how WAL-archives created between pg_start- and
pg_stop_backup are any different to later ones. Of course one needs
those to not loose data at all, but that doesn't tell anything about
how usable the data directory in itself is already without those.

Postgres seems to have simply defined that they additionally care
about the time when a backup is running. Which is fine of course, but
I still don't see any technical or conceptual limitation of not
following that decision. If I backup some VM using snapshots, I don't
necessarily care about the changes made within the VM during the
backup as well. Those are simply handled by the next backup. But there
are additional products streaming all changes to the VM somewhere, if
one needs that.

OTOH, it's of course good to have two other opinions to mine when my
boss asks if things are OK the way they are. :-)

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 Jul 8, 2019, at 7:10 AM, Thorsten Schöning <tschoening@am-soft.de> wrote:
>
> "since the last checkpoint": Missing WAL-segments mean a loss of data
> only. It doesn't mean that formerly "checkpointed" data gets magically
> broken, else crash recovery wouldn't work like described in the docs.

Not true. The database files are probably written to between checkpoints, and certainly during subsequent checkpoints
whichmay occur during the backup. 

> I don't see how WAL-archives created between pg_start- and
> pg_stop_backup are any different to later ones.

They are necessary to straighten out changes that were occurring during the backup.

> If I backup some VM using snapshots, I don't
> necessarily care about the changes made within the VM during the
> backup as well.

A snapshot is an entirely different thing, since it's an atomic grab of state at one point, very different than
sequentiallycopying files while they are being modified in a order that is in no way related to the order of copying. 

On 7/8/19 9:10 AM, Thorsten Schöning wrote:
> Guten Tag David Steele,
> am Montag, 8. Juli 2019 um 14:12 schrieben Sie:
> 
>> pg_start_backup() does a checkpoint, but then the database continues
>> writing as you copy the files in whatever order you choose.  You may
>> copy a file that has a partial write or copy some files involved in a
>> transaction before it happens and others afterwards -- in fact this is
>> normal and expected.
> 
> And because that's expected, Postgres can successfully restore from
> that, e.g. having used checkpoints before:

No.  The data files continue to be modified after the checkpoint while
you are copying.  The checkpoint is invalidated at the *very first* change.

If you start copying files after the pg_start_backup() you will *not*
get a copy of the files as they were right after the checkpoint.  The
database writes continuously, so you will get some invalid state in
between the starting checkpoint and the end state (there's no checkpoint
at the end).

>> The checkpoint constrains the range of WAL that you need, but that WAL
>> is absolutely needed to reconstruct the changes that happened during the
>> backup.
> 
> Which makes sense if all WAL-archives are simply considered to be
> incremental changes based on some former full backup. But that's the
> point: I don't see how WAL-archives created between pg_start- and
> pg_stop_backup are any different to later ones. Of course one needs
> those to not loose data at all, but that doesn't tell anything about
> how usable the data directory in itself is already without those.

The WAL does not change during a backup.  But, these WAL are required to
reconstruct the broken state that you get when copying files that are
being actively modified.

> Postgres seems to have simply defined that they additionally care
> about the time when a backup is running. 

Yes, we care about it because the backup will be inconsistent without
those WAL.

> Which is fine of course, but
> I still don't see any technical or conceptual limitation of not
> following that decision. If I backup some VM using snapshots, I don't 
> necessarily care about the changes made within the VM during the
> backup as well. Those are simply handled by the next backup. But there
> are additional products streaming all changes to the VM somewhere, if
> one needs that.

Snapshots are a different story, but they come with their own baggage.

> OTOH, it's of course good to have two other opinions to mine when my
> boss asks if things are OK the way they are. :-)

Seems to be three now.

-- 
-David
david@pgmasters.net

>>>>> "Thorsten" == Thorsten Schöning <tschoening@am-soft.de> writes:

 Thorsten> "since the last checkpoint": Missing WAL-segments mean a loss
 Thorsten> of data only.

No. Missing WAL-segments means that data is inconsistent (and there are
no bounds on the degree of inconsistency: it's quite possible to lose
all your data).

For crash recovery, consistency is guaranteed by the WAL-before-data
rule, which (providing fsync is working correctly) forces WAL records to
be persistently stored _before_ the corresponding data file changes can
be written back to the filesystem.

For base backups you don't have this: it will contain data pages that
were modified after the start of the backup. If the WAL for these
modifications is not replayed, then (unless your filesystem copy is a
perfectly atomic snapshot) these will be inconsistent with other data
pages copied at different times; the state of the files will not
correspond to a valid state of the database. This might not be
immediately noticable; it might cause incorrect data to be returned; it
might cause errors accessing some data; it might cause loss of entire
tables or databases.

I can't stress this strongly enough: the results of a base backup are
NOT USABLE unless you have every matching WAL record between the backup
start and end locations.

(If you're doing genuinely atomic filesystem snapshots - which are a
valid way to do backups, though you must snapshot the data dir, all
tablespaces, AND the WAL directory in one atomic operation - then you
don't need pg_start_backup and generally shouldn't use it.)

 Thorsten> It doesn't mean that formerly "checkpointed" data gets
 Thorsten> magically broken, else crash recovery wouldn't work like
 Thorsten> described in the docs.

Crash recovery has the advantage of knowing that the WAL-before-data
rule was being enforced (and if it was not, for example if you turned
fsync off or ran with unsafe caching, then it can easily fail resulting
in total effective loss of the data).

 Thorsten> Which makes sense if all WAL-archives are simply considered
 Thorsten> to be incremental changes based on some former full backup.
 Thorsten> But that's the point: I don't see how WAL-archives created
 Thorsten> between pg_start- and pg_stop_backup are any different to
 Thorsten> later ones.

They are not any different (other than having full-page-writes forced
on, which is the default anyway). What's different is the state of the
data files.

--
Andrew (irc:RhodiumToad)

On 8/7/19 4:10 μ.μ., Thorsten Schöning wrote:
> Guten Tag David Steele,
> am Montag, 8. Juli 2019 um 14:12 schrieben Sie:
>
>> pg_start_backup() does a checkpoint, but then the database continues
>> writing as you copy the files in whatever order you choose.  You may
>> copy a file that has a partial write or copy some files involved in a
>> transaction before it happens and others afterwards -- in fact this is
>> normal and expected.
> And because that's expected, Postgres can successfully restore from
> that, e.g. having used checkpoints before:
>
>> [...]This log exists primarily for crash-safety purposes: if the
>> system  crashes, the database can be restored to consistency by
>> “replaying” the log entries made since the last checkpoint.
> https://www.postgresql.org/docs/current/continuous-archiving.html
>
> "since the last checkpoint": Missing WAL-segments mean a loss of data
> only. It doesn't mean that formerly "checkpointed" data gets magically
> broken, else crash recovery wouldn't work like described in the docs.
The checkpoint is what brings WALs and data files in sync. If checkpoints are far between then crash recovery is
slower,if checkpoints are too frequent then your system gets slower. You gotta 
 
understand the checkpoint concepts before you even touch backups and PITR.  So yes, after the event of crash, missing
orcorrupted WAL files will get your system unusable. (read about 
 
pg_reset_xlog/wal ).
>> The checkpoint constrains the range of WAL that you need, but that WAL
>> is absolutely needed to reconstruct the changes that happened during the
>> backup.
> Which makes sense if all WAL-archives are simply considered to be
> incremental changes based on some former full backup. But that's the
> point: I don't see how WAL-archives created between pg_start- and
> pg_stop_backup are any different to later ones. Of course one needs
> those to not loose data at all, but that doesn't tell anything about
> how usable the data directory in itself is already without those.
You gotta understand the distinction :
- indefinite uninterrupted sequence of WAL files : an nice "luxury" enabling PITR at any point in time
(well of course this is not luxury for average serious environments but any way, pls read on)
- uninterrupted sequence of WAL files defined by pg_start/stop_backup : A necessity for backup recovery! A restore for
thisbackup will need AT LEAST *ALL* those files!
 

> Postgres seems to have simply defined that they additionally care
> about the time when a backup is running. Which is fine of course, but
> I still don't see any technical or conceptual limitation of not
> following that decision. If I backup some VM using snapshots, I don't
> necessarily care about the changes made within the VM during the
> backup as well. Those are simply handled by the next backup. But there
> are additional products streaming all changes to the VM somewhere, if
> one needs that.
If your VM snapshot guarantees an atomic snapshot of all file systems then it is fine as a full file-level backup
solution.
However if you need PITR in between full backups then it is fine to combine your VM snapshots with pg_start/stop_backup
andhave both full backups and PITR. We employ this solution as well as 
 
pgbackrest for various servers.
>
> OTOH, it's of course good to have two other opinions to mine when my
> boss asks if things are OK the way they are. :-)
>
> Mit freundlichen Grüßen,
>
> Thorsten Schöning
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

> On Jul 8, 2019, at 9:10 AM, Thorsten Schöning <tschoening@am-soft.de> wrote:
>
> Guten Tag David Steele,
> am Montag, 8. Juli 2019 um 14:12 schrieben Sie:
>
>> pg_start_backup() does a checkpoint, but then the database continues
>> writing as you copy the files in whatever order you choose.  You may
>> copy a file that has a partial write or copy some files involved in a
>> transaction before it happens and others afterwards -- in fact this is
>> normal and expected.
>
> And because that's expected, Postgres can successfully restore from
> that, e.g. having used checkpoints before:

Thorsten,

A checkpoint can not sync data that has already been backed up.  Also, the files are being actively written too while
theback is occurring thus the backup contains suspected pages.   

Postgres needs the WALs to resolve any issues that were created by the backup.  Without the WALs you have a
corrupted/suspectdatabase. 

It’s just like crash recovery; the database has to replay everything since the last checkpoint.  However, with a
backup…the checkpoint is pg_start_backup().