Postgres Replication

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:

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:

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:

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:

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