Thread: Warm Standby - log shipping

Warm Standby - log shipping

From
"Mark Steben"
Date:
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






Re: Warm Standby - log shipping

From
"Kevin Grittner"
Date:
>>> "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

Re: Warm Standby - log shipping

From
"Joshua D. Drake"
Date:
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


Re: Warm Standby - log shipping

From
"Mark Steben"
Date:
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


Re: Warm Standby - log shipping

From
"Joshua D. Drake"
Date:
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


Re: Warm Standby - log shipping

From
Simon Riggs
Date:
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


Re: Warm Standby - log shipping

From
Simon Riggs
Date:
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


Re: Warm Standby - log shipping

From
"Kevin Grittner"
Date:
>>> "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

Re: Warm Standby - log shipping

From
"Mark Steben"
Date:
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