Re: WAL and master multi-slave replication

Thanks,

Scot Kreienkamp
La-Z-Boy Inc.
skreien(at)la-z-boy(dot)com
734-242-1444 ext 6379

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alvaro Herrera
Sent: Wednesday, June 24, 2009 1:51 PM
To: Eduardo Morras
Cc: Scott Marlowe; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] WAL and master multi-slave replication

Eduardo Morras escribió:
> At 19:25 24/06/2009, you wrote:
>> On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras(at)s21sec(dot)com> wrote:
>> > Yes, there will be 3 masters recolleting data (doing updates, inserts and
>> > deletes) for now and 5 slaves where we will do the searches. The
>> slaves must
>> > have all the data recollected by the 3 masters and the system must be
>> easily
>> > upgradable, adding new masters and new slaves.
>>
>> You know you can't push WAL files from > 1 server into a slave, right?
>
> No, i didn't know that.

I guess you don't know either that you can't query a slave while it is
on recovery (so it's only a "warm" standby, not hot). And if you bring
it up you can't afterwards continue applying more segments later.

What you can do is grab a filesystem snapshot before bringing it online,
and then restoring that snapshot when you want to apply some more
segments to bring it up to date (so from Postgres' point of view it
seems like it was never brought up in the first place).

That is what I do. I actually have two separate copies of Postgres running at any given time on one of my mirrors. The first is running recovery constantly. The second is an LVM snapshot that is mounted on a different directory that listens on the network IP address. Every hour I have a script that shuts down both copies of Postgres, re-creates and remounts the new snapshot, alters the Postgresql.conf listen address, brings the LVM snapshot Postgres out of recovery, and then starts both copies of Postgres again. It takes about 60 seconds for the whole process with a few sleep statements to smooth things out. It guarantees my PITR mirror is still running and allows the mirror to be queryable. That's the best solution I could figure out to fit my requirements.

BTW, PITRtools is very nice. I had it scripted in 8.2, when 8.3 came out I switched to PITRtools so it would delete the WAL logs I no longer needed. Very nice, and much easier than my old scripts.