Thread: Have any tricks not to recreate a standby server to switch to the former primary?

Hello, guys ;-)

Env: PostgreSQL 8.4.5 + CentOS 4.8

I have two servers, one primary and one standby, which doing warm
standby. Every thing works fine at the beginning. The primary
generates the archive WAL log files and the standby fetchs them to
merge.

Then, I want to let the standby become primary. So I stop the
postgresql(service postgresql stop) manually, copy the last WAL file
and files under pg_xlog/ to the standby. The former standby now
becomes primary and the data is ok. BTW, it still started as archive
mode.

Not end yet. After the standby running for a while and generating some
data, I want to switch back to the former primary. And I repeat the
above steps: stop the postgresql service manuall, copy WAL log files
and files under pg_xlog/ to the former primary. I delete the pg_xlog/
and files under archive directory. Then the postgresql starts, but the
data generated after last switching do not come over.

The doucment(24.4.3. paragraph 5) said "To return to normal operation
we must fully recreate a standby server". I understand that, but it is
under failover, not a case like my switching normally. Recreating a
standby server costs too much for us(the initial one is ok). We are
building a high availability system, so I hope the postgresql be able
to switch over and over fast without data losing and inconsistent.

Any ideas are appreciated ;-)
Best regards.

--
Free as freedom, slack as Slackware.
vvoody

On Sat, Dec 18, 2010 at 10:23 PM, vvoody <wxj.g.sh@gmail.com> wrote:
> I have two servers, one primary and one standby, which doing warm
> standby. Every thing works fine at the beginning. The primary
> generates the archive WAL log files and the standby fetchs them to
> merge.
>
> Then, I want to let the standby become primary. So I stop the
> postgresql(service postgresql stop) manually, copy the last WAL file
> and files under pg_xlog/ to the standby. The former standby now
> becomes primary and the data is ok. BTW, it still started as archive
> mode.

I follow you up to here. Presumably, you instantiated the standby
using the steps outlined here:
http://wiki.postgresql.org/wiki/Warm_Standby

> Not end yet. After the standby running for a while and generating some
> data, I want to switch back to the former primary. And I repeat the
> above steps: stop the postgresql service manuall, copy WAL log files
> and files under pg_xlog/ to the former primary. I delete the pg_xlog/
> and files under archive directory. Then the postgresql starts, but the
> data generated after last switching do not come over.

From this description, it sounds like you're trying to shortcut the
process of bringing your old primary server (server A) up-to-date with
the currently-running server (server B). In order to bring server A
up-to-date with B, you'll need to follow *all* the steps on that wiki
page to set server A up as a warm standby of B, particularly the
pg_start_backup(); rsync'ing of PGDATA over to A, etc.

Josh

Josh Kupershmidt <schmiddy@gmail.com> writes:
> From this description, it sounds like you're trying to shortcut the
> process of bringing your old primary server (server A) up-to-date with
> the currently-running server (server B). In order to bring server A
> up-to-date with B, you'll need to follow *all* the steps on that wiki
> page to set server A up as a warm standby of B, particularly the
> pg_start_backup(); rsync'ing of PGDATA over to A, etc.

See repmgr for a way to mitigate than by using 2 standby servers:

  http://projects.2ndquadrant.com/repmgr
  https://github.com/greg2ndQuadrant/repmgr
  http://groups.google.com/group/repmgr

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support