Обсуждение: Standby servers and incrementally updated backups
It is my understanding that once a standby server has reached the point where it is often waiting for wal files to replay, it is pretty much caught up to the primary server, with the differences being in whatever wal files are currently in queue to be archived by the primary. If I'm correct, then for large databases wherein it can take hours to take a base backup, is there anything to be gained by using incrementally updated backups? Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, 2007-06-25 at 13:42 -0500, Erik Jones wrote: > It is my understanding that once a standby server has reached the > point where it is often waiting for wal files to replay, it is pretty > much caught up to the primary server, with the differences being in > whatever wal files are currently in queue to be archived by the > primary. Yes. You can tell by using select pg_xlogfile_name(pg_current_xlog_location()); to see what the current file on the Primary is. > If I'm correct, then for large databases wherein it can > take hours to take a base backup, is there anything to be gained by > using incrementally updated backups? If you are certain there are parts of the database not touched at all between backups. The only real way to be sure is to take file level checksums, or you can trust file dates. Many backup solutions can do this for you. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote: > On Mon, 2007-06-25 at 13:42 -0500, Erik Jones wrote: > >> It is my understanding that once a standby server has reached the >> point where it is often waiting for wal files to replay, it is pretty >> much caught up to the primary server, with the differences being in >> whatever wal files are currently in queue to be archived by the >> primary. > > Yes. You can tell by using > > select pg_xlogfile_name(pg_current_xlog_location()); > > to see what the current file on the Primary is. Thanks for the tip. > >> If I'm correct, then for large databases wherein it can >> take hours to take a base backup, is there anything to be gained by >> using incrementally updated backups? > > If you are certain there are parts of the database not touched at all > between backups. The only real way to be sure is to take file level > checksums, or you can trust file dates. Many backup solutions can do > this for you. Wait, um, what? I'm still not clear on why you would want to run a backup of an already caught up standby server. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote: > On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote: > >> If I'm correct, then for large databases wherein it can > >> take hours to take a base backup, is there anything to be gained by > >> using incrementally updated backups? > > > > If you are certain there are parts of the database not touched at all > > between backups. The only real way to be sure is to take file level > > checksums, or you can trust file dates. Many backup solutions can do > > this for you. > > Wait, um, what? I'm still not clear on why you would want to run a > backup of an already caught up standby server. Sorry, misread your question. While you are running a warm standby config, you will still want to take regular backups for recoverability and DR. These are additional backups, i.e they are not required to maintain the warm standby. You can backup the Primary, or you can backup the Standby, so most people will choose to backup the Standby to reduce the overhead on the Primary. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Jun 25, 2007, at 4:40 PM, Simon Riggs wrote: > On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote: >> On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote: >>>> If I'm correct, then for large databases wherein it can >>>> take hours to take a base backup, is there anything to be gained by >>>> using incrementally updated backups? >>> >>> If you are certain there are parts of the database not touched at >>> all >>> between backups. The only real way to be sure is to take file level >>> checksums, or you can trust file dates. Many backup solutions can do >>> this for you. >> >> Wait, um, what? I'm still not clear on why you would want to run a >> backup of an already caught up standby server. > > Sorry, misread your question. > > While you are running a warm standby config, you will still want to > take > regular backups for recoverability and DR. These are additional > backups, > i.e they are not required to maintain the warm standby. > > You can backup the Primary, or you can backup the Standby, so most > people will choose to backup the Standby to reduce the overhead on the > Primary. Ok, yeah, that's what I was thinking and is where we are headed in the next month or so here at work: we already have a standby running and will be adding a second standby server that we will be using for snapshot backups (packaged with the pertinent wal files...) as well as periodically bringing the second standby up to run dumps from just to cover all of our bases and also to be able to take our main primary server down for maintenance and still have both a production and standby running. I guess I was really just wanting to make sure I wasn't missing some other big usage for incremental backups from the standby. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Jun 25, 2007, at 4:54 PM, Erik Jones wrote: > On Jun 25, 2007, at 4:40 PM, Simon Riggs wrote: >> On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote: >>> On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote: >>>>> If I'm correct, then for large databases wherein it can >>>>> take hours to take a base backup, is there anything to be >>>>> gained by >>>>> using incrementally updated backups? >>>> >>>> If you are certain there are parts of the database not touched >>>> at all >>>> between backups. The only real way to be sure is to take file level >>>> checksums, or you can trust file dates. Many backup solutions >>>> can do >>>> this for you. >>> >>> Wait, um, what? I'm still not clear on why you would want to run a >>> backup of an already caught up standby server. >> >> Sorry, misread your question. >> >> While you are running a warm standby config, you will still want >> to take >> regular backups for recoverability and DR. These are additional >> backups, >> i.e they are not required to maintain the warm standby. >> >> You can backup the Primary, or you can backup the Standby, so most >> people will choose to backup the Standby to reduce the overhead on >> the >> Primary. > > Ok, yeah, that's what I was thinking and is where we are headed in > the next month or so here at work: we already have a standby > running and will be adding a second standby server that we will be > using for snapshot backups (packaged with the pertinent wal > files...) as well as periodically bringing the second standby up to > run dumps from just to cover all of our bases and also to be able > to take our main primary server down for maintenance and still have > both a production and standby running. I guess I was really just > wanting to make sure I wasn't missing some other big usage for > incremental backups from the standby. Note that (currently) once you bring a standby up you can't go back to standby mode without restoring the filesystem level backup you started with and replaying everything. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Jun 29, 2007, at 10:15 AM, Jim Nasby wrote: > On Jun 25, 2007, at 4:54 PM, Erik Jones wrote: >> On Jun 25, 2007, at 4:40 PM, Simon Riggs wrote: >>> On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote: >>>> On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote: >>>>>> If I'm correct, then for large databases wherein it can >>>>>> take hours to take a base backup, is there anything to be >>>>>> gained by >>>>>> using incrementally updated backups? >>>>> >>>>> If you are certain there are parts of the database not touched >>>>> at all >>>>> between backups. The only real way to be sure is to take file >>>>> level >>>>> checksums, or you can trust file dates. Many backup solutions >>>>> can do >>>>> this for you. >>>> >>>> Wait, um, what? I'm still not clear on why you would want to run a >>>> backup of an already caught up standby server. >>> >>> Sorry, misread your question. >>> >>> While you are running a warm standby config, you will still want >>> to take >>> regular backups for recoverability and DR. These are additional >>> backups, >>> i.e they are not required to maintain the warm standby. >>> >>> You can backup the Primary, or you can backup the Standby, so most >>> people will choose to backup the Standby to reduce the overhead >>> on the >>> Primary. >> >> Ok, yeah, that's what I was thinking and is where we are headed in >> the next month or so here at work: we already have a standby >> running and will be adding a second standby server that we will be >> using for snapshot backups (packaged with the pertinent wal >> files...) as well as periodically bringing the second standby up >> to run dumps from just to cover all of our bases and also to be >> able to take our main primary server down for maintenance and >> still have both a production and standby running. I guess I was >> really just wanting to make sure I wasn't missing some other big >> usage for incremental backups from the standby. > > Note that (currently) once you bring a standby up you can't go back > to standby mode without restoring the filesystem level backup you > started with and replaying everything. Right, got that. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Friday 29 June 2007 13:47, Erik Jones wrote:
> On Jun 29, 2007, at 10:15 AM, Jim Nasby wrote:
> > On Jun 25, 2007, at 4:54 PM, Erik Jones wrote:
> >> On Jun 25, 2007, at 4:40 PM, Simon Riggs wrote:
> >>> On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote:
> >>>> On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote:
> >>>>>> If I'm correct, then for large databases wherein it can
> >>>>>> take hours to take a base backup, is there anything to be
> >>>>>> gained by
> >>>>>> using incrementally updated backups?
> >>>>>
> >>>>> If you are certain there are parts of the database not touched
> >>>>> at all
> >>>>> between backups. The only real way to be sure is to take file
> >>>>> level
> >>>>> checksums, or you can trust file dates. Many backup solutions
> >>>>> can do
> >>>>> this for you.
> >>>>
> >>>> Wait, um, what? I'm still not clear on why you would want to run a
> >>>> backup of an already caught up standby server.
> >>>
> >>> Sorry, misread your question.
> >>>
> >>> While you are running a warm standby config, you will still want
> >>> to take
> >>> regular backups for recoverability and DR. These are additional
> >>> backups,
> >>> i.e they are not required to maintain the warm standby.
> >>>
> >>> You can backup the Primary, or you can backup the Standby, so most
> >>> people will choose to backup the Standby to reduce the overhead
> >>> on the
> >>> Primary.
> >>
> >> Ok, yeah, that's what I was thinking and is where we are headed in
> >> the next month or so here at work: we already have a standby
> >> running and will be adding a second standby server that we will be
> >> using for snapshot backups (packaged with the pertinent wal
> >> files...) as well as periodically bringing the second standby up
> >> to run dumps from just to cover all of our bases and also to be
> >> able to take our main primary server down for maintenance and
> >> still have both a production and standby running. I guess I was
> >> really just wanting to make sure I wasn't missing some other big
> >> usage for incremental backups from the standby.
> >
> > Note that (currently) once you bring a standby up you can't go back
> > to standby mode without restoring the filesystem level backup you
> > started with and replaying everything.
>
> Right, got that.
Which is one reason to keep doing incremental backups, so you can discard, or
at least trim, the number of wal log archives you need to keep around.
On a side note, I think we've found a way around this problem, I'll post a
note once I test it a little more.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL