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

Martha Stewart called it a Good Thing when david(at)fetter(dot)org (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")
http://linuxdatabases.info/info/slony.html
"...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