Обсуждение: PITR WAL backups
The PITR documentation recommends that a user's archive command should not overwrite an existing wal archive file, but it appears you need at least the wal archive file that matches the .backup marker for the recovery process to succeed. This makes sense because the recovery process needs to replay what happened during the time it took to make the backup. The problem I'm having is that you need to backup this wal file before it's been archived to have a backup which is consistent, but the documentation also states you need to make sure not to overwrite existing files in your archive command. Is the solution to copy this wal file to a directory other than the archive directory and make sure it's part of the base backup. If you run a cron job to save the current wal file every minute or so, should this file also be copied to a different directory than the archive directory to prevent the check for existing files from failing? Is this how other people have their systems set up? If this is correct, maybe the documentation should mention a separate directory should be used for the wal file that matches the .backup file and for the files copied by cron jobs so that checks for existing files won't find the partially wal files and fail to overwrite them. It might also state you need to copy the wal file matching the .backup marker to the archive directory if it wasn't archived through the system's archive command and that the file copied by the cron job also needs to be copied to your archive directory before running recovery. Is any of this correct or am I messing up the procedure entirely? Thanks, Mark Reidenbach
Mark Reidenbach writes: > The PITR documentation recommends that a user's archive command should > not overwrite an existing wal archive file, but it appears you need at > least the wal archive file that matches the .backup marker for the > recovery process to succeed. This makes sense because the recovery > process needs to replay what happened during the time it took to make > the backup. The problem I'm having is that you need to backup this wal > file before it's been archived to have a backup which is consistent, but > the documentation also states you need to make sure not to overwrite > existing files in your archive command. > ... > Is any of this correct or am I messing up the procedure entirely? > I'd like to know that as well, see the 'Online Backup and WAL archives' thread. I'm pretty sure that one thing you got wrong, is to say having the WAL named after the .backup is sufficient for restoring the backup. At least that's what I understood from your mail. Depending on the amount of database changes during the backup there may be a number of WALs created during the backup and I doubt that you can miss any of them. The question, how to make sure, to have enough WALs that the backup is usable, is still open to me. The general idea of WAL archiving is to keep all WALs so you can do PITR to any point up to the failure. But even then, it would be useful to know at which time sufficient WALs are saved to make the backup useful. Morus
>Mark Reidenbach wrote >The problem I'm having is that you need to > backup this wal > file before it's been archived to have a backup which is > consistent Not sure I agree. Can you explain some more? Is it that you have a very low transaction rate and log files take a long time to fill? Please give a blow-by-blow time/event description, so we can understand what the issue is and where the window of exposure is that you can see. Best regards, Simon Riggs
Simon Riggs writes: > >Mark Reidenbach wrote > > >The problem I'm having is that you need to > > backup this wal > > file before it's been archived to have a backup which is > > consistent > > Not sure I agree. Can you explain some more? > > Is it that you have a very low transaction rate and log files take a > long time to fill? > Independently from the transaction rate it's not clear to me, at which point (that is after saving which WAL) an online backup will be safe to be usable. I start the backup, save the files, end the backup. Ok. Now there are transactions during the backup. They are written to WALs. Some of these WALs are archived but the last transaction might be stored in WALs that are not archived yet, when the backup ends. So when can I be sure, to have all transactions that occured during the backup in archived WALs? Do I just have to wait that there is one further WAL archived after the backup? If that's the case, then online backups at low transaction rates might take some time. I guess one can live with that. If you keep the previous backup and the WALs so far, you can still recover from that, while waiting for the current backup to become safe. But the documentation it didn't make clear to me, at which point I can rely on the backup. Morus