Re: AW: AW: Postgres Replication - Mailing list pgsql-hackers

On Tue, 12 Jun 2001 15:50:09 +0200, you wrote:

>
>> Here are some disadvantages to using a "trigger based" approach:
>> 
>> 1) Triggers simply transfer individual data items when they 
>> are modified, they do not keep track of transactions.
>> 2) The execution of triggers within a database imposes a performance 
>> overhead to that database.
>> 3) Triggers require careful management by database administrators.  
>> Someone needs to keep track of all the "alarms" going off.
>> 4) The activation of triggers in a database cannot be easily 
>> rolled back or undone.
>
>Yes, points 2 and 3 are a given, although point 2 buys you the functionality
>of transparent locking across all involved db servers.
>Points 1 and 4 are only the case for a trigger mechanism that does 
>not use remote connection and 2-phase commit. 
>
>Imho an implementation that opens a separate client connection to the 
>replication target is only suited for async replication, and for that a WAL 
>based solution would probably impose less overhead.

Well as I read back the thread I see 2 different approaches to
replication:

1: tight integrated replication. 
pro:
- bi-directional (or multidirectional): updates are possible
everywhere
- A cluster of servers allways has the same state. 
- it does not matter to which server you connect
con:
- network between servers will be a bottleneck, especially if it is a
WAN connection
- only full replication possible
- what happens if one server is down? (or the network between) are
commits still possible

2: async replication
pro:
- long distance possible
- no problems with network outages
- only changes are replicated, selects do not have impact 
- no locking issues accross servers
- partial replication possible (many->one (datawarehouse), or one-many
(queries possible everywhere, updates only central) 
- goof for failover situations (backup server is standing by)
con:
- bidirectional replication hard to set up (you'll have to implement
conflict resolution according to your business rules)
- different servers are not guaranteed to be in the same state.

I can think of some scenarios where I would definitely want to
*choose* one of the options. A load-balanced web environment would
likely want the first option, but synchronizing offices in different
continents might not work with 2-phase commit over the network....

And we have not even started talking about *managing* replicated
environments. A lot of fail-over scenarios stop planning after the
backup host has take control. But how to get back? 
-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen       reinoud@xs4all.nl
http://www.xs4all.nl/~reinoud
__________________________________________________