pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cluster/Replication
7+ messages / 6 participants
[nested] [flat]

* Cluster/Replication
@ 2007-10-19 16:43 Sean Brown <sbrown@eaglepress.com>
  2007-10-19 17:06 ` Re: Cluster/Replication Joshua D. Drake <jd@commandprompt.com>
  2007-10-22 08:34 ` Re: Cluster/Replication cho <tobutaz@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Sean Brown @ 2007-10-19 16:43 UTC (permalink / raw)
  To: pgsql-admin

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
@ 2007-10-19 17:06 ` Joshua D. Drake <jd@commandprompt.com>
  2007-10-19 18:30   ` Re: Cluster/Replication Scott Marlowe <scott.marlowe@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Joshua D. Drake @ 2007-10-19 17:06 UTC (permalink / raw)
  To: Sean Brown <sbrown@eaglepress.com>; +Cc: pgsql-admin

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/

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
  2007-10-19 17:06 ` Re: Cluster/Replication Joshua D. Drake <jd@commandprompt.com>
@ 2007-10-19 18:30   ` Scott Marlowe <scott.marlowe@gmail.com>
  2007-10-19 19:05     ` Re: Cluster/Replication Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2007-10-19 18:30 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Sean Brown <sbrown@eaglepress.com>; pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
  2007-10-19 17:06 ` Re: Cluster/Replication Joshua D. Drake <jd@commandprompt.com>
  2007-10-19 18:30   ` Re: Cluster/Replication Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-10-19 19:05     ` Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Scott Marlowe @ 2007-10-19 19:05 UTC (permalink / raw)
  To: Sean Brown <sbrown@eaglepress.com>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
@ 2007-10-22 08:34 ` cho <tobutaz@gmail.com>
  2007-10-22 09:05   ` Re: Cluster/Replication Ewald Geschwinde <egeschwinde@gmail.com>
  2007-10-22 09:44   ` Re: Cluster/Replication Mario Splivalo <mario.splivalo@megafon.hr>
  1 sibling, 2 replies; 7+ messages in thread

From: cho @ 2007-10-22 08:34 UTC (permalink / raw)
  To: pgsql-admin

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).





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
  2007-10-22 08:34 ` Re: Cluster/Replication cho <tobutaz@gmail.com>
@ 2007-10-22 09:05   ` Ewald Geschwinde <egeschwinde@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Ewald Geschwinde @ 2007-10-22 09:05 UTC (permalink / raw)
  To: pgsql-admin

On 10/22/07, cho <tobutaz@gmail.com> wrote:
>
> 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.
>
>
>
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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cluster/Replication
  2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
  2007-10-22 08:34 ` Re: Cluster/Replication cho <tobutaz@gmail.com>
@ 2007-10-22 09:44   ` Mario Splivalo <mario.splivalo@megafon.hr>
  1 sibling, 0 replies; 7+ messages in thread

From: Mario Splivalo @ 2007-10-22 09:44 UTC (permalink / raw)
  To: cho <tobutaz@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2007-10-22 09:44 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-19 16:43 Cluster/Replication Sean Brown <sbrown@eaglepress.com>
2007-10-19 17:06 ` Joshua D. Drake <jd@commandprompt.com>
2007-10-19 18:30   ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-19 19:05     ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-22 08:34 ` cho <tobutaz@gmail.com>
2007-10-22 09:05   ` Ewald Geschwinde <egeschwinde@gmail.com>
2007-10-22 09:44   ` Mario Splivalo <mario.splivalo@megafon.hr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox