Fwd: Reversing flow of WAL shipping

--_000_0E8AE22FF7334F959073F25C16C9A697ql2com_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi, I sent this to general but haven't seen a response. I'm guessing this =
list is more appropriate.

Thanks.

Begin forwarded message:

From: David Jantzen <djantzen [at] ql2.com<mailto:djantzen [at] ql2.com>>
Date: October 21, 2009 8:52:59 PM PDT
To: "pgsql-general [at] postgresql.org<mailto:pgsql-general [at] postgresql.org>" <pg=
sql-general [at] postgresql.org<mailto:pgsql-general [at] postgresql.org>>
Subject: Reversing flow of WAL shipping

Hey Folks,

I want to run a warm standby scenario by you. I'm pretty sure it'll
work, but it's a very large database so even the slightest mistake can
mean a major setback.

Scenario:

Server A is the provider node, shipping WAL files to Server B. Server
B is destined to become the provider node (newer hardware), and has a
sibling Server C that will be the warm standby.

Here's the question: when I turn Server B into the production/provider
node, it's going to switch into the next timeline. If I then attempt
to put Server A (or Server C) into recovery mode, will it switch to
the new current timeline, or complain about being in the older
timeline? Do I have to take another full backup of Server B after
it's become the production/provider node?

Thanks,
David

--_000_0E8AE22FF7334F959073F25C16C9A697ql2com_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html><head></head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode:=
space; -webkit-line-break: after-white-space; ">Hi, I sent this to general=
but haven't seen a response. I'm guessing this list is more appropri=
ate.<div><br></div><div>Thanks.<br><div><br><div>Begin forwarded message:</=
div><br class=3D"Apple-interchange-newline"><blockquote type=3D"cite"><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-lef=
t: 0px;"><span style=3D"font-family:'Helvetica'; font-size:medium; color:rg=
ba(0, 0, 0, 1);"><b>From: </b></span><span style=3D"font-family:'Helvetica'=
; font-size:medium;">David Jantzen <<a href=3D"mailto:djantzen [at] ql2.com">=
djantzen [at] ql2.com</a>><br></span></div><div style=3D"margin-top: 0px; mar=
gin-right: 0px; margin-bottom: 0px; margin-left: 0px;"><span style=3D"font-=
family:'Helvetica'; font-size:medium; color:rgba(0, 0, 0, 1);"><b>Date: </b=
></span><span style=3D"font-family:'Helvetica'; font-size:medium;">October =
21, 2009 8:52:59 PM PDT<br></span></div><div style=3D"margin-top: 0px; marg=
in-right: 0px; margin-bottom: 0px; margin-left: 0px;"><span style=3D"font-f=
amily:'Helvetica'; font-size:medium; color:rgba(0, 0, 0, 1);"><b>To: </b></=
span><span style=3D"font-family:'Helvetica'; font-size:medium;">"<a href=3D=
"mailto:pgsql-general [at] postgresql.org">pgsql-general [at] postgres ql.org</a>" <=
;<a href=3D"mailto:pgsql-general [at] postgresql.org">pgsql-general [at] postgresql.o=
rg</a>><br></span></div><div style=3D"margin-top: 0px; margin-right: 0px=
; margin-bottom: 0px; margin-left: 0px;"><span style=3D"font-family:'Helvet=
ica'; font-size:medium; color:rgba(0, 0, 0, 1);"><b>Subject: </b></span><sp=
an style=3D"font-family:'Helvetica'; font-size:medium;"><b>Reversing flow o=
f WAL shipping</b><br></span></div><br><div>Hey Folks,<br><br>I want to run=
a warm standby scenario by you. I'm pretty sure it'll <br>work=
, but it's a very large database so even the slightest mistake can <b=
r>mean a major setback.<br><br>Scenario:<br><br>Server A is the provider no=
de, shipping WAL files to Server B. Server <br>B is destined to=
become the provider node (newer hardware), and has a <br>sibling Ser=
ver C that will be the warm standby.<br><br>Here's the question: when I tur=
n Server B into the production/provider <br>node, it's going to switc=
h into the next timeline. If I then attempt <br>to put Server A=
(or Server C) into recovery mode, will it switch to <br>the new curr=
ent timeline, or complain about being in the older <br>timeline? &nbs=
p;Do I have to take another full backup of Server B after <br>it's be=
come the production/provider node?<br><br>Thanks,<br>David<br><br></div></b=
lockquote></div><br></div></body></html>=

--_000_0E8AE22FF7334F959073F25C16C9A697ql2com_--

> >
> > Here's the question: when I turn Server B into the
> > production/provider
> > node, it's going to switch into the next timeline. If I then
> > attempt
> > to put Server A (or Server C) into recovery mode, will it switch to
> >
> > the new current timeline, or complain about being in the older
> > timeline? Do I have to take another full backup of Server B after
> > it's become the production/provider node?

You will need to do a new base backup.

Joshua D. Drake

> >
> > Thanks,
> > David
> >
> >
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Thanks for the quick response Joshua, much appreciated. Is there any
way to avoid or minimize a period without a warm standby when I switch =

to Server B for production? What about rsyncing the data directory
from Server B to Server C after B goes live?

On Oct 22, 2009, at 9:42 AM, Joshua D. Drake wrote:

>
>>>
>>> Here's the question: when I turn Server B into the
>>> production/provider
>>> node, it's going to switch into the next timeline. If I then
>>> attempt
>>> to put Server A (or Server C) into recovery mode, will it switch to
>>>
>>> the new current timeline, or complain about being in the older
>>> timeline? Do I have to take another full backup of Server B after
>>> it's become the production/provider node?
>
> You will need to do a new base backup.
>
> Joshua D. Drake
>
>>>
>>> Thanks,
>>> David
>>>
>>>
>>
>>
>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
> Consulting, Training, Support, Custom Development, Engineering
> If the world pushes look it in the eye and GRR. Then push back
> harder. - Salamander
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--000e0cd21764d977d50476898a52
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Oct 22, 2009 at 1:28 PM, David Jantzen <djantzen [at] ql2.com> wrote:

> Thanks for the quick response Joshua, much appreciated. Is there any
> way to avoid or minimize a period without a warm standby when I switch
> to Server B for production? What about rsyncing the data directory
> from Server B to Server C after B goes live?
>

I have seen it work before if server B's archive_command puts logs into the
same directory that server A was putting them. After the switch you need to
stop and then restart server C, it should pick up the new timeline.

Note: I did try this on 8.3 and it works... I would however note that you
have many cogs with this and that (my preferred) safest strategy is to do a
resync just as JD described. Using what I just talked about may end up
causing more downtime in the event of a failure then it would take to just
resync the database in general. I should also say that I only tried this on
a database of about 3 GB with only about 1 day's worth usage.

--Scott

--000e0cd21764d977d50476898a52
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><div class=3D"gmail_quote">On Thu, Oct 22, 2009 at 1:28 PM, David Jantz=
en <span dir=3D"ltr"><<a href=3D"mailto:djantzen [at] ql2.com">djantzen [at] ql2.c=
om</a>></span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"bord=
er-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-l=
eft: 1ex;">
Thanks for the quick response Joshua, much appreciated. =A0Is there any<br>
way to avoid or minimize a period without a warm standby when I switch<br>
to Server B for production? =A0What about rsyncing the data directory<br>
from Server B to Server C after B goes live?<br></blockquote></div><br>I ha=
ve seen it work before if server B's archive_command puts logs into the=
same directory that server A was putting them.=A0 After the switch you nee=
d to stop and then restart server C, it should pick up the new timeline.<br=
>
<br><br>Note:=A0 I did try this on 8.3 and it works... I would however note=
that you have many cogs with this and that (my preferred) safest strategy =
is to do a resync just as JD described.=A0 Using what I just talked about m=
ay end up causing more downtime in the event of a failure then it would tak=
e to just resync the database in general.=A0 I should also say that I only =
tried this on a database of about 3 GB with only about 1 day's worth us=
age.<br>
<br>--Scott<br><br>

--000e0cd21764d977d50476898a52--

David Jantzen <djantzen [at] ql2.com> wrote:

> Thanks for the quick response Joshua, much appreciated. Is there
> any way to avoid or minimize a period without a warm standby when I
> switch to Server B for production? What about rsyncing the data
> directory from Server B to Server C after B goes live?

In my experience, proper use of rsync just before the switch, combined
with use of rsync as your PITR base backup technique, can make for a
very short period without warm standby.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Fwd: Reversing flow of WAL shipping

Re: Fwd: Reversing flow of WAL shipping

Re: Fwd: Reversing flow of WAL shipping

Re: Fwd: Reversing flow of WAL shipping

Re: Fwd: Reversing flow of WAL shipping