Thread: about synchronous_standby_names or sync replic
Hi Folks, I wonder if it's possible to have a sync replication only from the the node where the client who update the DB and other node replicated asynchronously ? example : nodeCentral node1 node2 node3 user1 from node3 update db to nodeCentral (master) so nodeCentral replicates synchronously node3 and later other nodes... sorry if my question is irrelevant at this time ;) Thanks Franck -- View this message in context: http://postgresql.1045698.n5.nabble.com/about-synchronous-standby-names-or-sync-replic-tp4831261p4831261.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Sep 22, 2011 at 2:34 PM, e-blokos <infos@e-blokos.com> wrote: > Hi Folks, > > I wonder if it's possible to have a sync replication only from the the node > where the client > who update the DB and other node replicated asynchronously ? > > example : > nodeCentral node1 node2 node3 > > user1 from node3 update db to nodeCentral (master) so nodeCentral > replicates synchronously node3 and later other nodes... > yep, put the names of all the nodes in synchronous_standby_names and set synchronous_commit to local... then when someone sends a write command to the server "set synchronous_commit to on" (of course, this SET is a command you send to the server just like any other SQL command) don't forget to reset synchronous_commit or use set local, otherwise you will let this node as the sync rep until disconnect -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Thanks for your answer. That's great, so for later other nodes replication it needs an asynchronous replic like pg_stat or even a good old rsync ? Franck ----- Original Message ----- From: "Jaime Casanova" <jaime@2ndquadrant.com> To: "e-blokos" <infos@e-blokos.com> Cc: <pgsql-general@postgresql.org> Sent: Thursday, September 22, 2011 11:38 PM Subject: Re: [GENERAL] about synchronous_standby_names or sync replic On Thu, Sep 22, 2011 at 2:34 PM, e-blokos <infos@e-blokos.com> wrote: > Hi Folks, > > I wonder if it's possible to have a sync replication only from the the > node > where the client > who update the DB and other node replicated asynchronously ? > > example : > nodeCentral node1 node2 node3 > > user1 from node3 update db to nodeCentral (master) so nodeCentral > replicates synchronously node3 and later other nodes... > yep, put the names of all the nodes in synchronous_standby_names and set synchronous_commit to local... then when someone sends a write command to the server "set synchronous_commit to on" (of course, this SET is a command you send to the server just like any other SQL command) don't forget to reset synchronous_commit or use set local, otherwise you will let this node as the sync rep until disconnect -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Mmmh sorry for my ignorance, I just learnt that PG 9.0 includes a built in replication. My first PG install was in 1998, what a pleasure how PG became today... long life to PG community Franck ----- Original Message ----- From: "Jaime Casanova" <jaime@2ndquadrant.com> To: "e-blokos" <infos@e-blokos.com> Cc: <pgsql-general@postgresql.org> Sent: Thursday, September 22, 2011 11:38 PM Subject: Re: [GENERAL] about synchronous_standby_names or sync replic On Thu, Sep 22, 2011 at 2:34 PM, e-blokos <infos@e-blokos.com> wrote: > Hi Folks, > > I wonder if it's possible to have a sync replication only from the the > node > where the client > who update the DB and other node replicated asynchronously ? > > example : > nodeCentral node1 node2 node3 > > user1 from node3 update db to nodeCentral (master) so nodeCentral > replicates synchronously node3 and later other nodes... > yep, put the names of all the nodes in synchronous_standby_names and set synchronous_commit to local... then when someone sends a write command to the server "set synchronous_commit to on" (of course, this SET is a command you send to the server just like any other SQL command) don't forget to reset synchronous_commit or use set local, otherwise you will let this node as the sync rep until disconnect -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación