pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PITR WAL backups
4+ messages / 3 participants
[nested] [flat]

* PITR WAL backups
@ 2005-02-02 18:55  Mark Reidenbach <m.reidenbach@everytruckjob.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Mark Reidenbach @ 2005-02-02 18:55 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: PITR WAL backups
@ 2005-02-03 07:35  Morus Walter <morus.walter@tanto.de>
  parent: Mark Reidenbach <m.reidenbach@everytruckjob.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Morus Walter @ 2005-02-03 07:35 UTC (permalink / raw)
  To: Mark Reidenbach <m.reidenbach@everytruckjob.com>

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: PITR WAL backups
@ 2005-02-04 05:29  Simon Riggs <simon@2ndquadrant.com>
  parent: Mark Reidenbach <m.reidenbach@everytruckjob.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Simon Riggs @ 2005-02-04 05:29 UTC (permalink / raw)
  To: Mark Reidenbach <m.reidenbach@everytruckjob.com>; pgsql-admin

>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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: PITR WAL backups
@ 2005-02-04 08:43  Morus Walter <morus.walter@tanto.de>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Morus Walter @ 2005-02-04 08:43 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Simon Riggs <simon@2ndquadrant.com>

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-02-04 08:43 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-02 18:55 PITR WAL backups Mark Reidenbach <m.reidenbach@everytruckjob.com>
2005-02-03 07:35 ` Morus Walter <morus.walter@tanto.de>
2005-02-04 05:29 ` Simon Riggs <simon@2ndquadrant.com>
2005-02-04 08:43   ` Morus Walter <morus.walter@tanto.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox