Thread: Cluster/Replication

I believe this has come up before, and I am still researching how to
do this and figured asking was probably a good idea as hopefully I
can either get some direction or someone can point me at something I
haven't seen yet.

We are setting up a new database system that is going to act as our
central database. We are planning to have a Master/Slave setup,
originally with one slave but there is a strong possibility of adding
a second slave at our second site. We could run Mulit-Master, it just
wasn't the original plan. (The original plan was MySQL log shipping).
We do not want the possibility of loosing data in the event of
loosing the master node so synchronous replication is preferable. We
are using BLOBS, so the replication has to be able to handle that.
The slave we want to have useable but it would be used for reporting
only under normal usage.

Slony is out because of the BLOB requirement and it's asynchronous.
We are looking at pgCluster, but the replication server looks like a
single point of failure itself. Also we're not really clear on how
the replication server or load balancer operates for determining the
load and size of the system it would have to run them on.

There are a few other things like how to handle postgresql version
upgrades but thats probably best left to specific forums once a
solution is chosen.

Is pgCluster the way to go or are there other projects we might want
to look at?

Thank you in advance.

On Fri, 19 Oct 2007 12:43:40 -0400
Sean Brown <sbrown@eaglepress.com> wrote:

> I believe this has come up before, and I am still researching how to
> do this and figured asking was probably a good idea as hopefully I
> can either get some direction or someone can point me at something I
> haven't seen yet.

Why do you have a synchronous requirement?

>
> We are setting up a new database system that is going to act as our
> central database. We are planning to have a Master/Slave setup,
> originally with one slave but there is a strong possibility of
> adding a second slave at our second site. We could run Mulit-Master,
> it just wasn't the original plan. (The original plan was MySQL log
> shipping). We do not want the possibility of loosing data in the
> event of loosing the master node so synchronous replication is
> preferable. We are using BLOBS, so the replication has to be able to
> handle that. The slave we want to have useable but it would be used
> for reporting only under normal usage.
>
> Slony is out because of the BLOB requirement and it's asynchronous.
> We are looking at pgCluster, but the replication server looks like a
> single point of failure itself. Also we're not really clear on how
> the replication server or load balancer operates for determining the
> load and size of the system it would have to run them on.
>
> There are a few other things like how to handle postgresql version
> upgrades but thats probably best left to specific forums once a
> solution is chosen.
>
> Is pgCluster the way to go or are there other projects we might want
> to look at?
>
> Thank you in advance.
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: if posting/reading
> through Usenet, please send an appropriate subscribe-nomail command
> to majordomo@postgresql.org so that your message can get through to
> the mailing list cleanly
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On 10/19/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> On Fri, 19 Oct 2007 12:43:40 -0400
> Sean Brown <sbrown@eaglepress.com> wrote:
>
> > I believe this has come up before, and I am still researching how to
> > do this and figured asking was probably a good idea as hopefully I
> > can either get some direction or someone can point me at something I
> > haven't seen yet.
>
> Why do you have a synchronous requirement?

He said something about losing any data due to the loss of the master
being unacceptable, so synchronous was the only way to go.

But if the machines are separated by any real distance, the speed /
latency of the link will be the deciding factor in the write
performance of the whole system.

I think they might be better off having a local synchronous clustering
solution (i.e. two machines running in failover on shared storage or
something) and then async rep cross country if there's any distance to
the other server.  What to do about slony not handling LOBs I don't
know.

Please cc the list, as someone with a lot more experience with
pgcluster than I might be reading it. :)

On 10/19/07, Sean Brown <sbrown@eaglepress.com> wrote:
>
> On Oct 19, 2007, at 2:30 PM, Scott Marlowe wrote:
>
> > On 10/19/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> >> On Fri, 19 Oct 2007 12:43:40 -0400
> >> Sean Brown <sbrown@eaglepress.com> wrote:
> >>
> >>> I believe this has come up before, and I am still researching how to
> >>> do this and figured asking was probably a good idea as hopefully I
> >>> can either get some direction or someone can point me at something I
> >>> haven't seen yet.
> >>
> >> Why do you have a synchronous requirement?
> >
> > He said something about losing any data due to the loss of the master
> > being unacceptable, so synchronous was the only way to go.
> >
> > But if the machines are separated by any real distance, the speed /
> > latency of the link will be the deciding factor in the write
> > performance of the whole system.
> >
> > I think they might be better off having a local synchronous clustering
> > solution (i.e. two machines running in failover on shared storage or
> > something) and then async rep cross country if there's any distance to
> > the other server.  What to do about slony not handling LOBs I don't
> > know.
>
> That can be done, the two nodes that we are first implementing will
> be in the same datacenter with a 1Gb link between them. The third
> possible node would be accessible over a 768Kb VPN but isn't being
> installed in the near future.

Yeah, that sounds like a good setup for local synch, remote asynch

> Slony is probably what we would have done if it wasn't for the BLOB
> requirement.

Well, if you're married to the BLOB, you're gonna have to find another
way to replicating.  Are your blobs mostly static data?  If so, you
might be able to back them up every so often and ship them across the
link to the remote machine.

> Is pgCluster as irritating to set up as it seems to be? Does the
> Replication server do a lot, does it need a lot in the way of resources?

Don't know, I haven't set it up before, only read about it.

Le Fri, 19 Oct 2007 12:43:40 -0400, Sean Brown a écrit :

> We are setting up a new database system that is going to act as our
> central database. We are planning to have a Master/Slave setup,
> originally with one slave but there is a strong possibility of adding a
> second slave at our second site. We could run Mulit-Master, it just
> wasn't the original plan. (The original plan was MySQL log shipping). We
> do not want the possibility of loosing data in the event of loosing the
> master node so synchronous replication is preferable. We are using
> BLOBS, so the replication has to be able to handle that. The slave we
> want to have useable but it would be used for reporting only under
> normal usage.

Have a look at pgpool-I or pgpool-II in replication mode.
Pgpool proxies SQL requests, so that requests on one server are replayed
on the other. pgpool-I is limited to two nodes, whereas pgpool-II is not.
Latency is important for write performance. You can do failover, you just
need to detect by yourself when one node is dead.

There is also PITR recovery, but as it batches the transaction logs you
may lose a bit of data (up to a configured data size / duration).

If you want to use synchronous replication look at the link

http://www.postgresql.at/english/downloads_e.html

this is an enhanced and tested version of pgcluster - it's open source you can use it
it is also working on windows

--
Ewald Geschwinde

cho wrote:
> Le Fri, 19 Oct 2007 12:43:40 -0400, Sean Brown a écrit :
>
> Have a look at pgpool-I or pgpool-II in replication mode.
> Pgpool proxies SQL requests, so that requests on one server are replayed
> on the other. pgpool-I is limited to two nodes, whereas pgpool-II is not.
> Latency is important for write performance. You can do failover, you just
> need to detect by yourself when one node is dead.
>
> There is also PITR recovery, but as it batches the transaction logs you
> may lose a bit of data (up to a configured data size / duration).
>
>
PITR does not allow for the 'slave' server to be on-line, meaning you
can't do SELECTs on the slave while shipping WALs.

    Mike