Обсуждение: Warm Standby - log shipping
Hi folks, I'm learning to use log shipping in our development environment with the expectation to use it In production next month. We are at postgresql 8.2.5 Our master is in Massachusetts and our standby is in Norfolk, Va. We plan on using the Norfolk server not so much as a recovery failover but as a replicated database To run reports and establish a data warehousing environment. As such the plan is to run the Standby in recovery state for the majority of the day, then 'complete' recovery there, bring it Online, perform our reporting and data warehousing functions (in read-only mode of course), Then bring it back into recovery mode, letting the updates catch up for the next days processing. My questions are: 1. Is this a proper usage of log shipping? 2. If yes, what are the preferred mechanisms for forcing a standby server to 'complete' a recovery, then to force it back into recovery mode once everything is finished. So far, I have simply stopped the server, renamed the recovery.conf file then restarted the server. When done, I go the other way. This doesn't always work as there are files sometimes being copied from master to standby, then get only partially copied into the pg_xlog directory on the standby. I do use a script in recovery.conf that 'waits' for xlogs to be copied over but the problem occurs when I am not in recovery mode and the recovery.conf file is not being invoked. 3. I am currently in a state where a log got partially copied and postgres cannot find a valid checkpoint to restart. What is the best way to remedy this situation? Pg_resetxlog perhaps? Thanks for your help Mark Steben│Database Administrator│ @utoRevenue-R- "Join the Revenue-tion" COME SEE US AT NADA BOOTH #1021, HALL B, IN NEW ORLEANS! 95 Ashley Ave. West Springfield, MA., 01089 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion Enterprises
>>> "Mark Steben" <msteben@autorevenue.com> wrote: > We are at postgresql 8.2.5 You really should update to 8.2.11 or consider going to 8.3.5. http://www.postgresql.org/support/versioning > We plan on using the Norfolk server not so much as a recovery failover but > as a replicated database > To run reports and establish a data warehousing environment. As such the > plan is to run the > Standby in recovery state for the majority of the day, then 'complete' > recovery there, bring it > Online, perform our reporting and data warehousing functions (in read-only > mode of course), > Then bring it back into recovery mode, letting the updates catch up for the > next days processing. > > My questions are: > > 1. Is this a proper usage of log shipping? Once you complete recovery you don't have a good way back, short of getting a new base backup. If you have space, you could stop the replica server without leaving recovery state, do a copy of the cluster to another directory, restart your replica, start the copied cluster's server, complete recovery, and start running your reports. > 3. I am currently in a state where a log got partially copied and postgres > cannot find a valid checkpoint to restart. What is the best way to remedy > this situation? Pg_resetxlog perhaps? I'd get a fresh base backup from which to start. -Kevin
On Thu, 2008-12-18 at 16:43 -0500, Mark Steben wrote: > Hi folks, > recovery mode, letting the updates catch up for the > next days processing. > > My questions are: > > 1. Is this a proper usage of log shipping? Sure but you will have to run a base backup every night. You can't exit recovery, run queries, resume recovery. You have to exit recovery, run queries, perform based backup, resume recovery. > 3. I am currently in a state where a log got partially copied and postgres > cannot find a valid checkpoint to restart. What is the best way to remedy > this situation? Pg_resetxlog perhaps? > How did you do that? What are you using to copy the logs? You may want to look at pitr tools. It will make your life easier: https://projects.commandprompt.com/public/pitrtools Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
What I'm hearing is that I have to perform a base backup on my master in Mass. after recovery completes, send that over a secure network To Virginia, and lay it down there. Simple enough but the time to travel Over the network becomes an issue - 12 - 13 hours at best. If we have to do this then we will. I just want to make sure I'm understanding your advice. Thanks Mark Steben│Database Administrator│ @utoRevenue-R- "Join the Revenue-tion" COME SEE US AT NADA BOOTH #1021, HALL B, IN NEW ORLEANS! 95 Ashley Ave. West Springfield, MA., 01089 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion Enterprises -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Thursday, December 18, 2008 5:20 PM To: Mark Steben; pgsql-admin@postgresql.org Subject: Re: Warm Standby - log shipping >>> "Mark Steben" <msteben@autorevenue.com> wrote: > We are at postgresql 8.2.5 You really should update to 8.2.11 or consider going to 8.3.5. http://www.postgresql.org/support/versioning > We plan on using the Norfolk server not so much as a recovery failover but > as a replicated database > To run reports and establish a data warehousing environment. As such the > plan is to run the > Standby in recovery state for the majority of the day, then 'complete' > recovery there, bring it > Online, perform our reporting and data warehousing functions (in read-only > mode of course), > Then bring it back into recovery mode, letting the updates catch up for the > next days processing. > > My questions are: > > 1. Is this a proper usage of log shipping? Once you complete recovery you don't have a good way back, short of getting a new base backup. If you have space, you could stop the replica server without leaving recovery state, do a copy of the cluster to another directory, restart your replica, start the copied cluster's server, complete recovery, and start running your reports. > 3. I am currently in a state where a log got partially copied and postgres > cannot find a valid checkpoint to restart. What is the best way to remedy > this situation? Pg_resetxlog perhaps? I'd get a fresh base backup from which to start. -Kevin
On Fri, 2008-12-19 at 09:14 -0500, Mark Steben wrote: > What I'm hearing is that I have to perform a base backup on my master in > Mass. after recovery completes, send that over a secure network > To Virginia, and lay it down there. Simple enough but the time to travel > Over the network becomes an issue - 12 - 13 hours at best. > If we have to do this then we will. I just want to make sure I'm > understanding your advice. Yes. Joshua D. Drake > > Thanks > > Mark Steben│Database Administrator│ > @utoRevenue-R- "Join the Revenue-tion" > COME SEE US AT NADA BOOTH #1021, HALL B, IN NEW ORLEANS! > 95 Ashley Ave. West Springfield, MA., 01089 > 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax) > @utoRevenue is a registered trademark and a division of Dominion Enterprises > > > > -----Original Message----- > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] > Sent: Thursday, December 18, 2008 5:20 PM > To: Mark Steben; pgsql-admin@postgresql.org > Subject: Re: Warm Standby - log shipping > > >>> "Mark Steben" <msteben@autorevenue.com> wrote: > > > We are at postgresql 8.2.5 > > You really should update to 8.2.11 or consider going to 8.3.5. > > http://www.postgresql.org/support/versioning > > > We plan on using the Norfolk server not so much as a recovery > failover but > > as a replicated database > > To run reports and establish a data warehousing environment. As such > the > > plan is to run the > > Standby in recovery state for the majority of the day, then > 'complete' > > recovery there, bring it > > Online, perform our reporting and data warehousing functions (in > read-only > > mode of course), > > Then bring it back into recovery mode, letting the updates catch up > for the > > next days processing. > > > > My questions are: > > > > 1. Is this a proper usage of log shipping? > > Once you complete recovery you don't have a good way back, short of > getting a new base backup. If you have space, you could stop the > replica server without leaving recovery state, do a copy of the > cluster to another directory, restart your replica, start the copied > cluster's server, complete recovery, and start running your reports. > > > 3. I am currently in a state where a log got partially copied and > postgres > > cannot find a valid checkpoint to restart. What is the best way to > remedy > > this situation? Pg_resetxlog perhaps? > > I'd get a fresh base backup from which to start. > > -Kevin > > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Thu, 2008-12-18 at 16:43 -0500, Mark Steben wrote: > 3. I am currently in a state where a log got partially copied and > postgres > cannot find a valid checkpoint to restart. What is the best way to > remedy > this situation? Pg_resetxlog perhaps? Now, pg_resetxlogs, but in future don't delete WAL files that may be needed for recovery. You can use pg_controldata test | grep REDO | cut -d: -f 2 to find out the LSN of the last restartpoint, which can then be converted into an xlogfilename using pg_xlogfile_name(). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Fri, 2008-12-19 at 08:51 -0800, Joshua D. Drake wrote: > On Fri, 2008-12-19 at 09:14 -0500, Mark Steben wrote: > > What I'm hearing is that I have to perform a base backup on my master in > > Mass. after recovery completes, send that over a secure network > > To Virginia, and lay it down there. Simple enough but the time to travel > > Over the network becomes an issue - 12 - 13 hours at best. > > If we have to do this then we will. I just want to make sure I'm > > understanding your advice. > > Yes. Yes, but use rsync, so you don't need to wait for 13 hours. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
>>> "Mark Steben" <msteben@autorevenue.com> wrote: > What I'm hearing is that I have to perform a base backup on my master in > Mass. after recovery completes, send that over a secure network > To Virginia, and lay it down there. I'm not sure we're understanding each other. I was suggesting that you needed to make a new base backup in Mass. and send it to Virginia. Recovery doesn't start until you get that. There is one way you might avoid that, though -- if you saved a copy of the original base backup and all WAL files since then you could start over and roll all the way to current. > Simple enough but the time to travel > Over the network becomes an issue - 12 - 13 hours at best. As already suggested, if you're not using rsync with a daemon, you should look at that. For us, at least, it typically cuts the copy time by an order of magnitude. If you have the room and follow the advice given in my previous post, your warm standby should never come out of recovery mode. You can stop and start the server without that happening. What I was suggesting was that you periodically (daily?) you stop the warm standby in Virginia, copy the data directory to another location, restart the warm standby in recovery mode, then start up the copy of the warm standby in recovery mode, take that out of recovery mode, and use it for your reports. -Kevin
Thanks for the clarifications Kevin, Josh, Simon I am trying out Kevin's suggestion to create a second standby copy now. I know I have to create the base copy and send it over, at least For the first time to start recovery. I will look at rsync to do that. Thanks for all the help -- Mark I'm not sure we're understanding each other. I was suggesting that you needed to make a new base backup in Mass. and send it to Virginia. Recovery doesn't start until you get that. There is one way you might avoid that, though -- if you saved a copy of the original base backup and all WAL files since then you could start over and roll all the way to current. > Simple enough but the time to travel > Over the network becomes an issue - 12 - 13 hours at best. As already suggested, if you're not using rsync with a daemon, you should look at that. For us, at least, it typically cuts the copy time by an order of magnitude. If you have the room and follow the advice given in my previous post, your warm standby should never come out of recovery mode. You can stop and start the server without that happening. What I was suggesting was that you periodically (daily?) you stop the warm standby in Virginia, copy the data directory to another location, restart the warm standby in recovery mode, then start up the copy of the warm standby in recovery mode, take that out of recovery mode, and use it for your reports. -Kevin