Is there a peer-to-peer server solution with PG?

[Mike Nolan]

I have need to set up a 2nd database server for a client in their new
offices in another state this month. We will be shutting down the old
offices later this year but we really don't want to have 2-3 days of
downtime while we physically transfer equipment 800 miles.

We should have decent data connections between the two offices starting
next week, but I was wonding if there is a good peer-to-peer option for
PostgreSQL at this time.

As I understand Slony, it is master-slave only.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[David Fetter]

On Thu, Feb 03, 2005 at 06:25:50PM -0600, Mike Nolan wrote:

> I have need to set up a 2nd database server for a client in their
> new offices in another state this month. We will be shutting down
> the old offices later this year but we really don't want to have 2-3
> days of downtime while we physically transfer equipment 800 miles.
>
> We should have decent data connections between the two offices
> starting next week, but I was wonding if there is a good
> peer-to-peer option for PostgreSQL at this time.
>
> As I understand Slony, it is master-slave only.

Slony-1 is perfectly capable of replicating to a slave database, then
letting you decide to promote it to master, which is just what you'd
need. Why are you asking about multi-master?

Cheers,
D
--
David Fetter [email]david@fetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mike Nolan]

> Slony-1 is perfectly capable of replicating to a slave database, then

> letting you decide to promote it to master, which is just what you'd
> need. Why are you asking about multi-master?

I am concerned that if I have to support the traffic to keep the slave
unit in sync PLUS support general database use from the 'slaved' office
to the master one, on the same comm line, I might start running into
congestion issues.

We will have people actively working the database in both office for
a period of several weeks to several months, depending on how the final
transfer plan unfolds.

Master/Slave is probably an acceptable solution, I was just wondering if
there was a multi-master one available yet.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly

[David Fetter]

On Thu, Feb 03, 2005 at 07:03:36PM -0600, Mike Nolan wrote:

> > Slony-1 is perfectly capable of replicating to a slave database,
> > then letting you decide to promote it to master, which is just
> > what you'd need. Why are you asking about multi-master?

>
> I am concerned that if I have to support the traffic to keep the
> slave unit in sync PLUS support general database use from the
> 'slaved' office to the master one, on the same comm line, I might
> start running into congestion issues.

Slony-1 does its level best to ship transactions in a compact way.
Any write operations are done as the net result of the write
transaction, not necessarily all the steps in between. IOW, don't
worry too much :)

> We will have people actively working the database in both office for
> a period of several weeks to several months, depending on how the
> final transfer plan unfolds.

Sounds like a fit for Slony-1. Just make sure that nobody tries to
write to a slave, as such writes will fail.

> Master/Slave is probably an acceptable solution, I was just
> wondering if there was a multi-master one available yet.

Not really. If you *must* have multi-master, you probably have to get
Oracle or DB2 and pay /mucho dinero/.

Cheers,
D
--
David Fetter [email]david@fetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

[Christopher Browne]

Martha Stewart called it a Good Thing when [email]nolan@gw.tssi.com[/email] (Mike Nolan) wrote:

>> Slony-1 is perfectly capable of replicating to a slave database,
>> then letting you decide to promote it to master, which is just what
>> you'd need. Why are you asking about multi-master?

>
> I am concerned that if I have to support the traffic to keep the
> slave unit in sync PLUS support general database use from the
> 'slaved' office to the master one, on the same comm line, I might
> start running into congestion issues.
>
> We will have people actively working the database in both office for
> a period of several weeks to several months, depending on how the
> final transfer plan unfolds.
>
> Master/Slave is probably an acceptable solution, I was just
> wondering if there was a multi-master one available yet.

There is an effort under way; in planning stages at this point. Don't
expect that to be "productized" next month...

Let me wag a finger at one of your assumptions...

You should re-examine assumptions with great care if you start
imagining that you'll get more throughput out of a general purpose
"multimaster" system. (Something designed specifically for your
application is quite another matter, particularly if your application
turns out to be, in some fashion "embarassingly parallelizable.")

Synchronization can't _conceivably_ come for free; it has _got_ to
have some cost in terms of decreasing overall performance.

If you have so much update load that one server cannot accomodate that
load, then you should wonder why you'd expect that causing every one
of these updates to be applied to (say) 3 servers would "diminish"
this burden.

Each of the 3 servers may only have to take on 1/3 of the updates from
the outside, but they surely have to accomodate the other 2/3 as well.

This not to say that there can't be some benefits from multimaster
replication; that's why such projects are proceeding.

But it's NOT a panacea; it's NOT an easy "general purpose solution."

I was in a room with The Thinkers; I got the sense that the lights
dimmed for blocks around when they put their thinking caps on :-). To
this group of Rather Smart Folk, perceiving the array of concurrency
and locking problems required great attention on their part. 'Easy'
is definitely not the right word...
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
[url]http://linuxdatabases.info/info/slony.html[/url]
Rules of the Evil Overlord #31. "All naive, busty tavern wenches in my
realm will be replaced with surly, world-weary waitresses who will
provide no unexpected reinforcement and/or romantic subplot for the
hero or his sidekick." <http://www.eviloverlord.com/>

[Mike Nolan]

> If you have so much update load that one server cannot accomodate that

> load, then you should wonder why you'd expect that causing every one
> of these updates to be applied to (say) 3 servers would "diminish"
> this burden.

The update/query load isn't the real issue here, it's that these two
servers will be 800 miles apart and there are some advantages in having
each office connect to its local database rather than having one of
them connect to the remote master.

The Slony-1 approach will work, assuming I've got suffient network
bandwidth to support it plus the traffic from the remote office plus
exixting outside traffic from our public website.

That's one of those things you just don't know will work until you
have it built, so I'm looking for other options now while I have time
to consider them. Once I get on-site in two weeks it'll a lot more hectic.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Christopher Browne]

Martha Stewart called it a Good Thing when [email]david@fetter.org[/email] (David Fetter) wrote:

> On Thu, Feb 03, 2005 at 07:03:36PM -0600, Mike Nolan wrote:

>> > Slony-1 is perfectly capable of replicating to a slave database,
>> > then letting you decide to promote it to master, which is just
>> > what you'd need. Why are you asking about multi-master?

>>
>> I am concerned that if I have to support the traffic to keep the
>> slave unit in sync PLUS support general database use from the
>> 'slaved' office to the master one, on the same comm line, I might
>> start running into congestion issues.

>
> Slony-1 does its level best to ship transactions in a compact way.
> Any write operations are done as the net result of the write
> transaction, not necessarily all the steps in between. IOW, don't
> worry too much :)

Sorta. If there were SQL queries involving in _preparing_ for the
writes, those queries do not need to be run again.

On the other hand, if you run a SQL query like:

delete from t1 where id in (select id from t1 limit
7000);

(Which is a query I have recently used for some testing...)

you'll discover that ultimately turns into somewhere around 7000
delete statements when it hits the replica.

-> An insert of 7000 rows becomes 7000 insert statements
-> A delete of 7000 rows becomes 7000 delete statements
-> An update to 7000 rows becomes 7000 update statements

Mass updates can therefore get fairly expensive, alas.

>> We will have people actively working the database in both office
>> for a period of several weeks to several months, depending on how
>> the final transfer plan unfolds.

> Sounds like a fit for Slony-1. Just make sure that nobody tries to
> write to a slave, as such writes will fail.

.... Which is actually a Remarkably Good Feature. I once pointed a
report that wanted to update data to a wrong node, and would have been
Seriously Chagrined if it had silently gone along with the updates...
--
output = ("cbbrowne" "@" "acm.org")
[url]http://linuxdatabases.info/info/slony.html[/url]
"...In my phone conversation with Microsoft's lawyer I copped to the
fact that just maybe his client might see me as having been in the
past just a bit critical of their products and business
practices. This was too bad, he said with a sigh, because they were
having a very hard time finding a reporter who both knew the industry
well enough to be called an expert and who hadn't written a negative
article about Microsoft." -- Robert X. Cringely

[Marco Colombo]

On Fri, 4 Feb 2005, Mike Nolan wrote:

>> If you have so much update load that one server cannot accomodate that
>> load, then you should wonder why you'd expect that causing every one
>> of these updates to be applied to (say) 3 servers would "diminish"
>> this burden.

>
> The update/query load isn't the real issue here, it's that these two
> servers will be 800 miles apart and there are some advantages in having
> each office connect to its local database rather than having one of
> them connect to the remote master.

Considering that the two masters need to be connected, I don't see the
advantage. Any write on the _local_ master will have to wait for the
_remote_ master to complete as well.

> The Slony-1 approach will work, assuming I've got suffient network
> bandwidth to support it plus the traffic from the remote office plus
> exixting outside traffic from our public website.

Local read-only access won't travel on the network, both with multi-master
and with master-slave.

Write traffic _will_ be transmitted over the wire, both with multi-master
and with master-slave. With multi-master _every write operation_ will be
remotely duplicated, _both ways_, _synchronously_. That is, master-1 has
to wait for master-2 and vice versa. If you're concerned with network
performances, multi-master will only make it worse.

With master-slave, _only_ the write operations performed on
the slave side need to travel, since clients will perform them directly
on the master. On the master side writes are only local.
Of course, the _results_ of the writes will have to be propagated to the
slave (and thus they travel on the network as well), but that's another
matter (delay is usually acceptable, and fits MVCC - the semantics not
broken).

If you're willing to break semantics, you may run two splitted masters
and find a way to synchronize them. But that requires application level
knowledge, if not human intervention, to resolve conflicts.

> That's one of those things you just don't know will work until you
> have it built, so I'm looking for other options now while I have time
> to consider them. Once I get on-site in two weeks it'll a lot more hectic.
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ [email]Colombo@ESI.it[/email]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster