Reversing flow of WAL shipping

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@ql2.com<mailto:djantzen@ql2.com>>
Date: October 21, 2009 8:52:59 PM PDT
To: "pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>" <pgsql-general@postgresql.org<mailto:pgsql-general@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

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

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:

On Thu, Oct 22, 2009 at 1:28 PM, David Jantzen <djantzen@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

David Jantzen <djantzen@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

[moving to pgsql-hackers]

On Thu, Oct 22, 2009 at 10:28 AM, David Jantzen <djantzen@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?

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

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