pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWarm Standby - log shipping
10+ messages / 4 participants
[nested] [flat]
* Warm Standby - log shipping
@ 2008-12-18 21:43 Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-18 22:20 ` Re: Warm Standby - log shipping Joshua D. Drake <jd@commandprompt.com>
2008-12-19 17:11 ` Re: Warm Standby - log shipping Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 3 replies; 10+ messages in thread
From: Mark Steben @ 2008-12-18 21:43 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
@ 2008-12-18 22:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2 siblings, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2008-12-18 22:19 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; pgsql-admin
>>> "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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-12-19 14:14 ` Mark Steben <msteben@autorevenue.com>
2008-12-19 16:51 ` Re: Warm Standby - log shipping Joshua D. Drake <jd@commandprompt.com>
2008-12-19 17:35 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 10+ messages in thread
From: Mark Steben @ 2008-12-19 14:14 UTC (permalink / raw)
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
@ 2008-12-19 16:51 ` Joshua D. Drake <jd@commandprompt.com>
2008-12-19 17:12 ` Re: Warm Standby - log shipping Simon Riggs <simon@2ndQuadrant.com>
1 sibling, 1 reply; 10+ messages in thread
From: Joshua D. Drake @ 2008-12-19 16:51 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; +Cc: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-19 16:51 ` Re: Warm Standby - log shipping Joshua D. Drake <jd@commandprompt.com>
@ 2008-12-19 17:12 ` Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2008-12-19 17:12 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Mark Steben <msteben@autorevenue.com>; 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
@ 2008-12-19 17:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 18:09 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
1 sibling, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2008-12-19 17:35 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; pgsql-admin
>>> "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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Re: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-19 17:35 ` Re: Warm Standby - log shipping Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-12-19 18:09 ` Mark Steben <msteben@autorevenue.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mark Steben @ 2008-12-19 18:09 UTC (permalink / raw)
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
@ 2008-12-18 22:20 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2008-12-18 22:20 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm Standby - log shipping
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
@ 2008-12-19 17:11 ` Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2008-12-19 17:11 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* FW: Warm Standby - log shipping
@ 2008-12-26 16:49 Mark Steben <msteben@autorevenue.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mark Steben @ 2008-12-26 16:49 UTC (permalink / raw)
To: pgsql-admin
Another question - if the xlogs are 'out of sync' between master and standby
e.g. the master is trying to copy a 000----81 log to the the standby and
the standby is waiting for a 000----7F log is there a mechanism short of
setting up another base copy to tell the standby to expect a 000----81 log?
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: Mark Steben [mailto:msteben@autorevenue.com]
Sent: Thursday, December 18, 2008 4:43 PM
To: 'pgsql-admin@postgresql.org'
Subject: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-12-26 16:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-18 21:43 Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
2008-12-18 22:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 14:14 ` Mark Steben <msteben@autorevenue.com>
2008-12-19 16:51 ` Joshua D. Drake <jd@commandprompt.com>
2008-12-19 17:12 ` Simon Riggs <simon@2ndQuadrant.com>
2008-12-19 17:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-19 18:09 ` Mark Steben <msteben@autorevenue.com>
2008-12-18 22:20 ` Joshua D. Drake <jd@commandprompt.com>
2008-12-19 17:11 ` Simon Riggs <simon@2ndQuadrant.com>
2008-12-26 16:49 FW: Warm Standby - log shipping Mark Steben <msteben@autorevenue.com>
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