Lists: | pgsql-adminpgsql-generalpgsql-hackers |
---|
From: | David Jantzen <djantzen(at)ql2(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Reversing flow of WAL shipping |
Date: | 2009-10-22 03:52:59 |
Message-ID: | C5ED6D02-1C03-4A6B-A3C6-3262FE6E3CF1@ql2.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
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
From: | David Jantzen <djantzen(at)ql2(dot)com> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 16:34:42 |
Message-ID: | 0E8AE22F-F733-4F95-9073-F25C16C9A697@ql2.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
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(dot)com<mailto:djantzen(at)ql2(dot)com>>
Date: October 21, 2009 8:52:59 PM PDT
To: "pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)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
From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | David Jantzen <djantzen(at)ql2(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 16:42:49 |
Message-ID: | 1256229769.3378.6.camel@jd-desktop.unknown.charter.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
> >
> > 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
From: | David Jantzen <djantzen(at)ql2(dot)com> |
---|---|
To: | "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 17:28:19 |
Message-ID: | 79117255-5CB7-44A3-8DF8-74D647632BA8@ql2.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
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
>
From: | Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> |
---|---|
To: | David Jantzen <djantzen(at)ql2(dot)com> |
Cc: | "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 17:36:58 |
Message-ID: | d3ab2ec80910221036v2e4f2982y236fa40c73280068@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
On Thu, Oct 22, 2009 at 1:28 PM, David Jantzen <djantzen(at)ql2(dot)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
From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
---|---|
To: | "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com>, "David Jantzen" <djantzen(at)ql2(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 17:41:59 |
Message-ID: | 4AE05317020000250002BDE8@gw.wicourts.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
David Jantzen <djantzen(at)ql2(dot)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
From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | "<pgsql-hackers(at)postgresql(dot)org>" <pgsql-hackers(at)postgresql(dot)org> |
Cc: | David Jantzen <djantzen(at)ql2(dot)com> |
Subject: | Re: Fwd: Reversing flow of WAL shipping |
Date: | 2009-10-22 18:20:09 |
Message-ID: | 407d949e0910221120w141a458l809b54d8250432e2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
[moving to pgsql-hackers]
On Thu, Oct 22, 2009 at 10:28 AM, David Jantzen <djantzen(at)ql2(dot)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?
This is definitely something that needs to be addressed in future
versions of Postgres, especially once we have Hot
Standby/Read-Only-Slaves and Sync-Replication. People will want to
have dozens of active slaves and rebuilding them all every time a
failover happens will be unbearable. Not to mention that in the
meantime they have to live with no redundancy.
--
greg
From: | Yaroslav Tykhiy <yar(at)barnet(dot)com(dot)au> |
---|---|
To: | David Jantzen <djantzen(at)ql2(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Reversing flow of WAL shipping |
Date: | 2009-10-22 22:35:57 |
Message-ID: | A0FD7E31-4AE5-406A-9E91-E40366F9330C@barnet.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
Hi David,
On 22/10/2009, at 2:52 PM, David Jantzen wrote:
> 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?
I did a similar trick more than once with a mission-critical database
by starting with 1 master/provider server (A) and 2 warm standby
servers (B, C), then failing over from A to B, then shipping WAL files
from the new master B to C. However I might be (ab)using the feature
or bug in Postgresql 8.0 that it wouldn't switch to a new timeline in
my environment. (I'd love to find out why it was so.) Please see http://archives.postgresql.org/pgsql-general/2009-07/msg00215.php
for details.
Yar