Thread: Postgres Replication
Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo
On Tue, 2007-01-09 at 07:36, dcrespo wrote: > Hi everybody, > > I have two computers with a Postgres Database each. I want one of them > to be the replica of the other one; let's say I want a Master to Master > replication in order to use either one (but only one at a time) as the > main database: in case of failure, switch. The ideal synchronization > way would be Synchronous. However, these two computers are going to be > next to each other, so the asynchronous synchronization would be fast > enough (I don't really know. Can you tell so?) for the case synchronous > sync is not available. > > What I have found so far is Daffodil and Slony-I. Daffodil's name > doesn't even appear in Postgresql.org, which is not the case for > Slony-I. So there's a big point in favor to Slony-I. > > Has anybody researched on this that can point me in the right > direction? Possibly. Depending on your biz requirements, you may be better served with a hot failover setup, where both machines can mount the same storage array and if the primary server fails, the secondary server mounts its partitions and starts up postgresql, and takes over its IPs etc... There are hazards with this kind of setup, because if two postmasters run on the same data store it will corrupt it beyond repair, etc... slony works well for what you're talking about, but you'll need to come up with a switchover plan that meets you needs. You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others.
If you only want to use one database at a time you might look into using DRBD. It's a linux block-level package that is like raid-1 over the network. On Tue, 9 Jan 2007, dcrespo wrote: > Hi everybody, > > I have two computers with a Postgres Database each. I want one of them > to be the replica of the other one; let's say I want a Master to Master > replication in order to use either one (but only one at a time) as the > main database: in case of failure, switch. The ideal synchronization > way would be Synchronous. However, these two computers are going to be > next to each other, so the asynchronous synchronization would be fast > enough (I don't really know. Can you tell so?) for the case synchronous > sync is not available. > > What I have found so far is Daffodil and Slony-I. Daffodil's name > doesn't even appear in Postgresql.org, which is not the case for > Slony-I. So there's a big point in favor to Slony-I. > > Has anybody researched on this that can point me in the right > direction? > > Thanks a lot, > > Daniel Crespo > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: > > Has anybody researched on this that can point me in the right > > direction? > > You could use possibly use pgpool as long as its caveats aren't a show > stopper (can't insert with random, individual inserts with things like > now() might be a little different, insert order might not be the same on > both machines, etc... > > I haven't used daffodil, but have heard of it. > > There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM
Good question. The only concern that I have is the date of the last version (2005-3-7). Do you or anybody know if this software (PGCluster) is stable and works fine? Please, give information on how it fits your needs. Thank you! Daniel km wrote: > On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: > > > Has anybody researched on this that can point me in the right > > > direction? > > > > You could use possibly use pgpool as long as its caveats aren't a show > > stopper (can't insert with random, individual inserts with things like > > now() might be a little different, insert order might not be the same on > > both machines, etc... > > > > I haven't used daffodil, but have heard of it. > > > > There's also c-jdbc and a few others. > what abt pgcluster ? how does it fare with SlonyI ? > regards, > KM > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Thank you, Ben, for your reply. I have read the FAQ of DRBD, but I'm still wondering how an application accessing a database server knows when to switch to the mirror (setting this one as the master). I think I should have an application that provides the connection transparently which determines where to connect. But for that, it must be running in another computer besides the cluster (the two computers). I'm a newbie, so maybe this was a newbie question message. Thanks Daniel Ben wrote: > If you only want to use one database at a time you might look into using > DRBD. It's a linux block-level package that is like raid-1 over the > network. > > On Tue, 9 Jan 2007, dcrespo wrote: > > > Hi everybody, > > > > I have two computers with a Postgres Database each. I want one of them > > to be the replica of the other one; let's say I want a Master to Master > > replication in order to use either one (but only one at a time) as the > > main database: in case of failure, switch. The ideal synchronization > > way would be Synchronous. However, these two computers are going to be > > next to each other, so the asynchronous synchronization would be fast > > enough (I don't really know. Can you tell so?) for the case synchronous > > sync is not available. > > > > What I have found so far is Daffodil and Slony-I. Daffodil's name > > doesn't even appear in Postgresql.org, which is not the case for > > Slony-I. So there's a big point in favor to Slony-I. > > > > Has anybody researched on this that can point me in the right > > direction? > > > > Thanks a lot, > > > > Daniel Crespo > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Look into heartbeat: http://www.linux-ha.org/HeartbeatProgram The idea is that you have a virtual address to be "the database", and that the primary server configures itself for this address as well as whatever address it would normally have. Then, when you want to switch servers (maybe because the primary has died, or because you want to do some maintenance to keep it from dying) the second server takes over the database address with a bunch of ARP packets. Your application sees its postgres connections have died and so gracefully (right?) tries to reconnect, and as long as the primary server is no longer trying to regain control of that virtual address (which it usually isn't, because either you've configured it not to or because it's dead) then everything proceeds just fine on the backup server. On Wed, 10 Jan 2007, dcrespo wrote: > Thank you, Ben, for your reply. > > I have read the FAQ of DRBD, but I'm still wondering how an application > accessing a database server knows when to switch to the mirror (setting > this one as the master). I think I should have an application that > provides the connection transparently which determines where to > connect. But for that, it must be running in another computer besides > the cluster (the two computers). > > I'm a newbie, so maybe this was a newbie question message. > > Thanks > > Daniel > > Ben wrote: >> If you only want to use one database at a time you might look into using >> DRBD. It's a linux block-level package that is like raid-1 over the >> network. >> >> On Tue, 9 Jan 2007, dcrespo wrote: >> >>> Hi everybody, >>> >>> I have two computers with a Postgres Database each. I want one of them >>> to be the replica of the other one; let's say I want a Master to Master >>> replication in order to use either one (but only one at a time) as the >>> main database: in case of failure, switch. The ideal synchronization >>> way would be Synchronous. However, these two computers are going to be >>> next to each other, so the asynchronous synchronization would be fast >>> enough (I don't really know. Can you tell so?) for the case synchronous >>> sync is not available. >>> >>> What I have found so far is Daffodil and Slony-I. Daffodil's name >>> doesn't even appear in Postgresql.org, which is not the case for >>> Slony-I. So there's a big point in favor to Slony-I. >>> >>> Has anybody researched on this that can point me in the right >>> direction? >>> >>> Thanks a lot, >>> >>> Daniel Crespo >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 2: Don't 'kill -9' the postmaster >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
dcrespo wrote: > Good question. The only concern that I have is the date of the last > version (2005-3-7). You will find that their website has not been updated for a while. If you look in pgfoundry you will find that they have releases as recent as a few days ago. The different 1.x versions relate to a different postgres version (1.7.x is 8.2) (1.5.x is 8.1) (1.3.x is 8.0). http://pgfoundry.org/projects/pgcluster > Do you or anybody know if this software (PGCluster) is stable and works > fine? Please, give information on how it fits your needs. I haven't used it myself, just been looking around out of curiosity. > Thank you! > > Daniel > > km wrote: >> On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: >>>> Has anybody researched on this that can point me in the right >>>> direction? >>> You could use possibly use pgpool as long as its caveats aren't a show >>> stopper (can't insert with random, individual inserts with things like >>> now() might be a little different, insert order might not be the same on >>> both machines, etc... >>> >>> I haven't used daffodil, but have heard of it. >>> >>> There's also c-jdbc and a few others. >> what abt pgcluster ? how does it fare with SlonyI ? >> regards, >> KM >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > > -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz