pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR WAL backups
4+ messages / 3 participants
[nested] [flat]
* PITR WAL backups
@ 2005-02-02 18:55 Mark Reidenbach <m.reidenbach@everytruckjob.com>
2005-02-03 07:35 ` Re: PITR WAL backups Morus Walter <morus.walter@tanto.de>
2005-02-04 05:29 ` Re: PITR WAL backups Simon Riggs <simon@2ndquadrant.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-02 18:55 PITR WAL backups Mark Reidenbach <m.reidenbach@everytruckjob.com>
@ 2005-02-03 07:35 ` Morus Walter <morus.walter@tanto.de>
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-02 18:55 PITR WAL backups Mark Reidenbach <m.reidenbach@everytruckjob.com>
@ 2005-02-04 05:29 ` Simon Riggs <simon@2ndquadrant.com>
2005-02-04 08:43 ` Re: PITR WAL backups Morus Walter <morus.walter@tanto.de>
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-02 18:55 PITR WAL backups Mark Reidenbach <m.reidenbach@everytruckjob.com>
2005-02-04 05:29 ` Re: PITR WAL backups Simon Riggs <simon@2ndquadrant.com>
@ 2005-02-04 08:43 ` Morus Walter <morus.walter@tanto.de>
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