Thread: Postgres Replication
We have been researching replication for several months now, and I have some opinions to share to the community for feedback, discussion, and/or participation. Our goal is to get a replication solution for PostgreSQL that will meet most needs of users and applications alike (mission impossible theme here :). My research work along with others contributors has been collected and presented here http://www.greatbridge.org/genpage?replication_top If there is something missing, especially PostgreSQL related work, I would like to know about it, and my apologies to any one who got left off the list. This work is ongoing and doesn't draw a conclusion, which IMHO should be left up to the user, but I'm offering my opinions to spur discussion and/or feed back from this list, and try not to offend any one. Here's my opinion: of the approaches we've surveyed, the most promising one is the Postgres-R project from the Information and Communication Systems Group, ETH in Zurich, Switzerland, originally produced by Bettina Kemme, Gustavo Alonso, and others. Although Postgres-R is a synchronous approach, I believe it is the closest to the goal mentioned above. Here is an abstract of the advantages. 1) Postgres-R is built on the PostgreSQL-6.4.2 code base. The replication functionality is an optional parameter, so there will be insignificant overhead for non replication situations. The replication and communication managers are the two new modules added to the PostgreSQL code base. 2) The replication manager's main function is controlling the replication protocol via a message handling process. It receives messages from the local and remote backends and forwards write sets and decision messages via the communication manager to the other servers. The replication manager controls all the transactions running on the local server by keeping track of the states, including which protocol phase (read, send, lock, or write) the transaction is in. The replication manager maintains a two way channel implemented as buffered sockets to each backend. 3) The main task of the communication manager is to provide simple socket based interface between the replication manager and the group communication system (currently Ensemble). The communication system is a cluster of servers connected via the communication manager. The replication manager also maintains three one-way channels to the communication system: a broadcast channel to send messages, a total-order channel to receive totally orders write sets, and a no-order channel to listen for decision messages from the communication system. Decision messages can be received at any time where the reception of totally ordered write sets can be blocked in certain phases. 4) Based on a two phase locking approach, all dead lock situations are local and detectable by Postgres-R code base, and aborted. 5) The write set messages used to send database changes to other servers, can use either the SQL statements or the actual tuples changed. This is a parameter based on number of tuples changed by a transaction. While sending the tuple changes reduces overhead in query parse, plan and execution, there is a negative effect in sending a large write set across the network. 6) Postgres-R uses a synchronous approach that keeps the data on all sites consistent and provides serializability. The user does not have to bother with conflict resolution, and receives the same correctness and consistency of a centralized system. 7) Postgres-R could be part of a good fault-resilient and load distribution solution. It is peer-to-peer based and incurs low overhead propagating updates to the other cluster members. All replicated databases locally process queries. 8) Compared to other synchronous replication strategies (e.g., standard distributed 2-phase-locking + 2-phase-commit), Postgres-R has much better performance using 2-phase-locking. There are some issues that are not currently addressed by Postgres-R, but some enhancements made to PostgreSQL since the 6.4.2 tree are very favorable to addressing these short comings. 1) The addition of WAL in 7.1 has the information for recovering failed/off-line servers, currently all the servers would have to be stopped, and a copy would be used to get all the servers synchronized before starting again. 2)Being synchronous, Postgres-R would not be a good solution for off line/WAN scenarios where asynchronous replication is required. There are some theories on this issue which involve servers connecting and disconnecting from the cluster. 3)As in any serialized synchronous approach there is change in the flow of execution of a transaction; while most of these changes can be solved by calling newly developed functions at certain time points, synchronous replica control is tightly coupled with the concurrency control. Hence, especially in PostgreSQL 7.2 some parts of the concurrency control (MVCC) might have to be adjusted. This can lead to a slightly more complicated maintenance than a system that does not change the backend. 4)Partial replication is not addressed. Any feedback on this post will be appreciated. Thanks, Darren
On Mon, 11 Jun 2001 19:46:44 GMT, you wrote: >We have been researching replication for several months now, and >I have some opinions to share to the community for feedback, >discussion, and/or participation. Our goal is to get a replication >solution for PostgreSQL that will meet most needs of users >and applications alike (mission impossible theme here :). > >My research work along with others contributors has been collected >and presented here http://www.greatbridge.org/genpage?replication_top >If there is something missing, especially PostgreSQL related >work, I would like to know about it, and my apologies to any >one who got left off the list. This work is ongoing and doesn't >draw a conclusion, which IMHO should be left up to the user, >but I'm offering my opinions to spur discussion and/or feed back >from this list, and try not to offend any one. > >Here's my opinion: of the approaches we've surveyed, the most >promising one is the Postgres-R project from the Information and >Communication Systems Group, ETH in Zurich, Switzerland, originally >produced by Bettina Kemme, Gustavo Alonso, and others. Although >Postgres-R is a synchronous approach, I believe it is the closest to >the goal mentioned above. Here is an abstract of the advantages. > >1) Postgres-R is built on the PostgreSQL-6.4.2 code base. The >replication >functionality is an optional parameter, so there will be insignificant >overhead for non replication situations. The replication and >communication >managers are the two new modules added to the PostgreSQL code base. > >2) The replication manager's main function is controlling the >replication protocol via a message handling process. It receives >messages from the local and remote backends and forwards write >sets and decision messages via the communication manager to the >other servers. The replication manager controls all the transactions >running on the local server by keeping track of the states, including >which protocol phase (read, send, lock, or write) the transaction is >in. The replication manager maintains a two way channel >implemented as buffered sockets to each backend. what does "manager controls all the transactions" mean? I hope it does *not* mean that a bug in the manager would cause transactions not to commit... > >3) The main task of the communication manager is to provide simple >socket based interface between the replication manager and the >group communication system (currently Ensemble). The >communication system is a cluster of servers connected via >the communication manager. The replication manager also maintains >three one-way channels to the communication system: a broadcast >channel to send messages, a total-order channel to receive >totally orders write sets, and a no-order channel to listen for >decision messages from the communication system. Decision >messages can be received at any time where the reception of >totally ordered write sets can be blocked in certain phases. > >4) Based on a two phase locking approach, all dead lock situations >are local and detectable by Postgres-R code base, and aborted. Does this imply locking over different servers? That would mean a grinding halt when a network outage occurs... >5) The write set messages used to send database changes to other >servers, can use either the SQL statements or the actual tuples >changed. This is a parameter based on number of tuples changed >by a transaction. While sending the tuple changes reduces >overhead in query parse, plan and execution, there is a negative >effect in sending a large write set across the network. > >6) Postgres-R uses a synchronous approach that keeps the data on >all sites consistent and provides serializability. The user does not >have to bother with conflict resolution, and receives the same >correctness and consistency of a centralized system. > >7) Postgres-R could be part of a good fault-resilient and load >distribution >solution. It is peer-to-peer based and incurs low overhead propagating >updates to the other cluster members. All replicated databases locally >process queries. > >8) Compared to other synchronous replication strategies (e.g., standard >distributed 2-phase-locking + 2-phase-commit), Postgres-R has much >better performance using 2-phase-locking. Coming from a Sybase background I have some experience with replication. The way it works in Sybase Replication server is as follows: - for each replicated database, there is a "log reader" process that reads the WAL and captures only *committed transactions* to the replication server. (it does not make much sense to replicate other things IMHO :-). - the replication server stores incoming data in a que ("stable device"), until it is sure it has reached its final destination - a replication server can send data to another replication server in a compact (read: WAN friendly) way. A chain of replication servers can be made, depending on network architecture) - the final replication server makes a almost standard client connection to the target database and translates the compact transactions back to SQL statements. By using masks, extra functionality can be built in. This kind of architecture has several advantages: - only committed transactions are replicated which saves overhead - it does not have very much impact on performance of the source server (apart from reading the WAL) - since every replication server has a stable device, data is stored when the network is down and nothing gets lost (nor stops performing) - because only the log reader and the connection from the final replication server are RDBMS specific, it is possible to replicate from MS to Oracle using a Sybase replication server (or different versions etc). I do not know how much of this is patented or copyrighted, but the architecture seems elegant and robust to me. I have done implementations of bi-directional replication too. It *is* possible but does require some funky setup and maintenance. (but it is better that letting offices on different continents working on the same database :-) just my 2 EURO cts :-) -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud@xs4all.nl http://www.xs4all.nl/~reinoud __________________________________________________
On Mon, 11 Jun 2001, Reinoud van Leeuwen wrote:
> On Mon, 11 Jun 2001 19:46:44 GMT, you wrote:
> what does "manager controls all the transactions" mean? I hope it does
> *not* mean that a bug in the manager would cause transactions not to
> commit...
Well yeah it does. Bugs are a fact of life. :)
> >4) Based on a two phase locking approach, all dead lock situations
> >are local and detectable by Postgres-R code base, and aborted.
>
> Does this imply locking over different servers? That would mean a
> grinding halt when a network outage occurs...
Don't know, but see below.
> Coming from a Sybase background I have some experience with
> replication. The way it works in Sybase Replication server is as
> follows:
> - for each replicated database, there is a "log reader" process that
> reads the WAL and captures only *committed transactions* to the
> replication server. (it does not make much sense to replicate other
> things IMHO :-).
> - the replication server stores incoming data in a que ("stable
> device"), until it is sure it has reached its final destination
>
> - a replication server can send data to another replication server in
> a compact (read: WAN friendly) way. A chain of replication servers can
> be made, depending on network architecture)
>
> - the final replication server makes a almost standard client
> connection to the target database and translates the compact
> transactions back to SQL statements. By using masks, extra
> functionality can be built in.
>
> This kind of architecture has several advantages:
> - only committed transactions are replicated which saves overhead
> - it does not have very much impact on performance of the source
> server (apart from reading the WAL)
> - since every replication server has a stable device, data is stored
> when the network is down and nothing gets lost (nor stops performing)
> - because only the log reader and the connection from the final
> replication server are RDBMS specific, it is possible to replicate
> from MS to Oracle using a Sybase replication server (or different
> versions etc).
>
> I do not know how much of this is patented or copyrighted, but the
> architecture seems elegant and robust to me. I have done
> implementations of bi-directional replication too. It *is* possible
> but does require some funky setup and maintenance. (but it is better
> that letting offices on different continents working on the same
> database :-)
Yes, the above architecture is what almost every vendor of replication
software uses. And I'm sure if you worked much with Sybase, you hate the
garbage that their repserver is :).
The architecture of postgres-r and repserver are fundamentally different
for a good reason: repserver only wants to replicate committed
transactions, while postgres-r is more of a 'clustering' solution (albeit
they don't say this word), and is capable to do much more than simple rep
server.
I.E. you can safely put half of your clients to second server in a
replicated postgres-r cluster without being worried that a conflict (or a
wierd locking situation) may occur.
Try that with sybase, it is fundamentally designed for one-way
replication, and the fact that you can do one-way replication in both
directions doesn't mean its safe to do that!
I'm not sure how postgres-r handles network problems. To be useful, a good
replication solution must have an option of "no network->no updates" as
well as "no network->queue updates and send them later". However, it is
far easier to add queuing to a correct 'eager locking' database than it is
to add proper locking to a queue-based replicator.
-alex
Thanks for the feedback. I'll try to address both your issues here.
>> what does "manager controls all the transactions" mean?
>
The replication manager controls the transactions by serializing the
write set messages.
This ensures all transactions are committed in the same order on each
server, so bugs
here are not allowed ;-)
>> I hope it does
>> *not* mean that a bug in the manager would cause transactions not to
>> commit...
>
> Well yeah it does. Bugs are a fact of life. :
>
>>> 4) Based on a two phase locking approach, all dead lock situations
>>> are local and detectable by Postgres-R code base, and aborted.
>>
>> Does this imply locking over different servers? That would mean a
>> grinding halt when a network outage occurs...
>
> Don't know, but see below.
There is a branch of the Postgres-R code that has some failure detection
implemented,
so we will have to merge this functionality with the version of
Postgres-R we have, and
test this issue. I'll let you the results.
>>
>> - the replication server stores incoming data in a que ("stable
>> device"), until it is sure it has reached its final destination
>
I like this idea for recovering servers that have been down a short
period of time, using WAL
to recover transactions missed during the outage.
>>
>> This kind of architecture has several advantages:
>> - only committed transactions are replicated which saves overhead
>> - it does not have very much impact on performance of the source
>> server (apart from reading the WAL)
>> - since every replication server has a stable device, data is stored
>> when the network is down and nothing gets lost (nor stops performing)
>> - because only the log reader and the connection from the final
>> replication server are RDBMS specific, it is possible to replicate
>> from MS to Oracle using a Sybase replication server (or different
>> versions etc).
>
There are some issues with the "log reader" approach:
1) The databases are not synchronized until the log reader completes its
processing.
2) I'm not sure about Sybase, but the log reader sends SQL statements to
the other servers
which are then parsed, planned and executed. This over head could be
avoided if only
the tuple changes are replicated.
3) Works fine for read only situations, but peer-to-peer applications
using this approach
must be designed with a conflict resolution scheme.
Don't get me wrong, I believe we can learn from the replication
techniques used by commercial
databases like Sybase, and try to implement the good ones into
PostgreSQL. Postgres-R is
a synchronous approach which out performs the traditional approaches to
synchronous replication.
Being based on PostgreSQL-6.4.2, getting this approach in the 7.2 tree
might be better than
reinventing the wheel.
Thanks again,
Darren
Thanks again,
Darren