FW: Warm Standby - log shipping - Mailing list pgsql-admin

From Mark Steben
Subject FW: Warm Standby - log shipping
Date
Msg-id 3D26BFF728E34240830680A64DBB2FC9@dei26g028534
Whole thread Raw
List 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






pgsql-admin by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: vacuum monitoring question
Next
From: Raj Mathur
Date:
Subject: [DOCUMENT] Migrating Oracle to PostgresSQL