Обсуждение: Do results of pg_start_backup work without WAL segments created during backup?
Do results of pg_start_backup work without WAL segments created during backup?
От
Thorsten Schöning
Дата:
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
Re: Do results of pg_start_backup work without WAL segments createdduring backup?
От
Magnus Hagander
Дата:
On Mon, Jul 8, 2019 at 11:57 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. 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?
Then you have a corrupt and unusable backup.
The pg_start_backup/pg_stop_backup method for backups can *only* be used together with a working log archive.
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
Not just loose the data, your cluster will be corrupt.
OR are the copied files so fundamentally broken that Postgres is not
able to operate at all without the WAL-archives during backup?
This would be the case.
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.
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.
The information about which WAL blocks are necessary are generated during pg_stop_backup, not pg_start_backup.
There is a reason these functions are labeled "low level APIs". They are designed to work together with other parts, like the log archiving, not to be a complete solution on their own. There are other tools available that provide all the plumbing, such as pg_basebackup, pgbackrest and pgbarman. 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.
/Magnus
Re: Do results of pg_start_backup work without WAL segments created during backup?
От
Thorsten Schöning
Дата:
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
Re: Do results of pg_start_backup work without WAL segments createdduring backup?
От
David Steele
Дата:
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
Re: Do results of pg_start_backup work without WAL segments created during backup?
От
Thorsten Schöning
Дата:
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.
Re: Do results of pg_start_backup work without WAL segments createdduring backup?
От
David Steele
Дата:
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
Re: Do results of pg_start_backup work without WAL segments created during backup?
От
Andrew Gierth
Дата:
>>>>> "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)
Re: Do results of pg_start_backup work without WAL segments createdduring backup?
От
Achilleas Mantzios
Дата:
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
Re: Do results of pg_start_backup work without WAL segments createdduring backup?
От
Rui DeSousa
Дата:
> 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().