Thread: PITR WAL backups

PITR WAL backups

From
Mark Reidenbach
Date:
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

Re: PITR WAL backups

From
Morus Walter
Date:
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


Re: PITR WAL backups

From
"Simon Riggs"
Date:
>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


Re: PITR WAL backups

From
Morus Walter
Date:
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