Thread: Forcing WAL switch
I can't find a way to force a WAL to switch other than just generating a lot of changes in the database. I'd like to be able to switch to the next WAL before a hot backup, and then switch out one after the hot backup. The docs indicate this is not possible.
Does anyone have a way to do this (other than creating a lot of data changes)?
Thanks
Jed.
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> I can't find a way to force a WAL to switch other than just generating a
> lot of changes in the database. I'd like to be able to switch to the
> next WAL before a hot backup, and then switch out one after the hot
> backup.
What's the point?
regards, tom lane
Walker, Jed S wrote: > I can't find a way to force a WAL to switch other than just generating a > lot of changes in the database. I'd like to be able to switch to the > next WAL before a hot backup, and then switch out one after the hot > backup. The docs indicate this is not possible. > > Does anyone have a way to do this (other than creating a lot of data > changes)? No, sorry. We know we need this functionality, particularly for point-in-time recovery, and that is on the TODO list. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
To have a good hot backup. It is worthless if I don't have the log that
was being written to at the time the backup was occurring correct? If
I'm wrong please explain.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, August 11, 2005 9:04 AM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Forcing WAL switch
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> I can't find a way to force a WAL to switch other than just generating
> a lot of changes in the database. I'd like to be able to switch to the
> next WAL before a hot backup, and then switch out one after the hot
> backup.
What's the point?
regards, tom lane
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> To have a good hot backup. It is worthless if I don't have the log that
> was being written to at the time the backup was occurring correct?
Right, but how does forcing a segment switch make that any easier? You
still have to determine which segment is current at the time you start
the backup.
regards, tom lane
If the backup completes, I switch the database out of "backup mode", and
then switch a WAL and it archives, then I know my backup directory
contains the backup and the WAL(s) needed to bring the database to a
consistent state. So, if I can switch a WAL after the backup, then I
truly have a complete backup.
Let's say
10:00 enter backup mode (writing to WAL 0000A8)
10:01 backup database
10:30 exit backup mode (writing to WAL 0000A9)
15:30 "data" storage disk is trashed (still writing to WAL 0000A9)
I have a backup, but I don't have WAL 0000A9 which I need to recover the
database, so my backup is useless (unless as suggested in docu, I keep
multiple backups). If I could have archived WAL 0000A9 after exiting
backup mode it would be in my backup storage and I'd be OK.
Which log was being written to at the start of the backup doesn't really
matter to me as long as it got archived and is in the backup directory
too.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, August 11, 2005 3:38 PM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Forcing WAL switch
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> To have a good hot backup. It is worthless if I don't have the log
> that was being written to at the time the backup was occurring
correct?
Right, but how does forcing a segment switch make that any easier? You
still have to determine which segment is current at the time you start
the backup.
regards, tom lane
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> If the backup completes, I switch the database out of "backup mode", and
> then switch a WAL and it archives, then I know my backup directory
> contains the backup and the WAL(s) needed to bring the database to a
> consistent state. So, if I can switch a WAL after the backup, then I
> truly have a complete backup.
How is "switch a WAL" an essential component of that scheme? You can
archive the latest active segment just as well.
regards, tom lane
How do you do that?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, August 11, 2005 4:12 PM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Forcing WAL switch
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
> If the backup completes, I switch the database out of "backup mode",
> and then switch a WAL and it archives, then I know my backup directory
> contains the backup and the WAL(s) needed to bring the database to a
> consistent state. So, if I can switch a WAL after the backup, then I
> truly have a complete backup.
How is "switch a WAL" an essential component of that scheme? You can
archive the latest active segment just as well.
regards, tom lane
Walker, Jed S wrote: > How do you do that? > > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, August 11, 2005 4:12 PM > To: Walker, Jed S > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Forcing WAL switch > > "Walker, Jed S" <Jed_Walker@cable.comcast.com> writes: > > If the backup completes, I switch the database out of "backup mode", > > and then switch a WAL and it archives, then I know my backup directory > > > contains the backup and the WAL(s) needed to bring the database to a > > consistent state. So, if I can switch a WAL after the backup, then I > > truly have a complete backup. > > How is "switch a WAL" an essential component of that scheme? You can > archive the latest active segment just as well. Ah, but that will be over-written later, so you have to store it somewhere safe, rather than just forcing closure of the current WAL file and forcing an archive of it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
>> How is "switch a WAL" an essential component of that scheme? You can
>> archive the latest active segment just as well.
> How do you do that?
Look into the backup history file created by pg_stop_backup --- it will
show the starting and ending WAL locations for the backup. Make sure
you have copies of the WAL segments between those locations inclusive,
and you're done.
The file containing the ending WAL location is probably still active,
and will need to be archived via the normal PITR mechanism when it's
complete (assuming you're doing real PITR archiving), but I don't see
what that has to do with building a standalone backup fileset.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> How is "switch a WAL" an essential component of that scheme? You can
>> archive the latest active segment just as well.
> Ah, but that will be over-written later, so you have to store it
> somewhere safe, rather than just forcing closure of the current
> WAL file and forcing an archive of it.
So? I still don't see the operational benefit.
If you are running a true PITR operation, that is you are archiving off
the complete WAL sequence, then forced WAL switches aren't buying you
anything except wasted archive space. You're still going to want to
archive the active segment when it's done.
If you're not really doing PITR but just want to use a filesystem-level
backup, then you can copy the last WAL segment when you're done whether
it's still active or not.
I honestly think that WAL-switching is a solution in search of a
problem.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> How is "switch a WAL" an essential component of that scheme? You can > >> archive the latest active segment just as well. > > > Ah, but that will be over-written later, so you have to store it > > somewhere safe, rather than just forcing closure of the current > > WAL file and forcing an archive of it. > > So? I still don't see the operational benefit. > > If you are running a true PITR operation, that is you are archiving off > the complete WAL sequence, then forced WAL switches aren't buying you > anything except wasted archive space. You're still going to want to > archive the active segment when it's done. > > If you're not really doing PITR but just want to use a filesystem-level > backup, then you can copy the last WAL segment when you're done whether > it's still active or not. > > I honestly think that WAL-switching is a solution in search of a > problem. The issue for me is that you really have to store the partially-filled WAL file some other than the default archive location because when it is later archived, you don't want it to be overwritten and perhaps fail in the middle of the write. Making the WAL file switch solves that issue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
You mentioned that this was on the list of ToDos. Can we expect this in 8.1? (It looks like it can be worked around, but having a simple way to get a clean complete backup shouldn't have to be a workaround.) -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Thursday, August 11, 2005 6:25 PM To: Tom Lane Cc: Walker, Jed S; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Forcing WAL switch Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> How is "switch a WAL" an essential component of that scheme? You > >> can archive the latest active segment just as well. > > > Ah, but that will be over-written later, so you have to store it > > somewhere safe, rather than just forcing closure of the current WAL > > file and forcing an archive of it. > > So? I still don't see the operational benefit. > > If you are running a true PITR operation, that is you are archiving > off the complete WAL sequence, then forced WAL switches aren't buying > you anything except wasted archive space. You're still going to want > to archive the active segment when it's done. > > If you're not really doing PITR but just want to use a > filesystem-level backup, then you can copy the last WAL segment when > you're done whether it's still active or not. > > I honestly think that WAL-switching is a solution in search of a > problem. The issue for me is that you really have to store the partially-filled WAL file some other than the default archive location because when it is later archived, you don't want it to be overwritten and perhaps fail in the middle of the write. Making the WAL file switch solves that issue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Walker, Jed S wrote: > You mentioned that this was on the list of ToDos. Can we expect this in > 8.1? > > (It looks like it can be worked around, but having a simple way to get a > clean complete backup shouldn't have to be a workaround.) I don't think it will be in 8.1. Someone was going to work on it but I think ran out of time. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073