Postgres Replication

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

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.

If you only want synchronous replication, why not simply use triggers ?
All you would then need is remote query access and two phase commit,
and maybe a little script that helps create the appropriate triggers.

Doing a replicate all or nothing approach that only works synchronous
is imho not flexible enough.

Andreas

Hello

I have hacked up a replication layer for Perl code accessing a
database throught the DBI interface. It works pretty well with MySQL
(I can run pre-bender slashcode replicated, haven't tried the more
recent releases).

Potentially this hack should also work with Pg but I haven't tried
yet. If someone would like to test it out with a complex Pg app and
let me know how it went that would be cool.

The replication layer is based on Eric Newton's Recall replication
library (www.fault-tolerant.org/recall), and requires that all
database accesses be through the DBI interface.

The replicas are live, in that every operation affects all the
replicas in real time. Replica outages are invisible to the user, so
long as a majority of the replicas are functioning. Disconnected
replicas can be used for read-only access.

The only code modification that should be required to use the
replication layer is to change the DSN in connect():

my $replicas = '192.168.1.1:7000,192.168.1.2:7000,192.168.1.3:7000';
my $dbh = DBI->connect("DBI:Recall:database=$replicas");

You should be able to install the replication modules with:

perl -MCPAN -eshell
cpan> install Replication::Recall::DBServer

and then install DBD::Recall (which doesn't seem to be accessible from
the CPAN shell yet, for some reason), by:

wget http://www.cpan.org/authors/id/AGUL/DBD-Recall-1.10.tar.gz
tar xzvf DBD-Recall-1.10.tar.gz
cd DBD-Recall-1.10
perl Makefile.PL
make install

I would be very interested in hearing about your experiences with
this...

Thanks

#!

which I believe is what the rserv implementation in contrib currently does
... no?

its funny ... what is in contrib right now was developed in a weekend by
Vadim, put in contrib, yet nobody has either used it *or* seen fit to
submit patches to improve it ... ?

On Tue, 12 Jun 2001, Zeugswetter Andreas SB wrote:

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.

If you only want synchronous replication, why not simply use triggers ?
All you would then need is remote query access and two phase commit,
and maybe a little script that helps create the appropriate triggers.

Doing a replicate all or nothing approach that only works synchronous
is imho not flexible enough.

Andreas

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

http://www.postgresql.org/search.mpl

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

which I believe is what the rserv implementation in contrib currently

does

... no?

We tried rserv, PG Link (Joseph Conway), and PosrgreSQL Replicator. All
these projects are trigger based asynchronous replication. They all have
some advantages over the current functionality of Postgres-R some of
which I believe can be addressed:

1) Partial replication - being able to replicate just one or part of a
table(s)
2) They make no changes to the PostgreSQL code base. (Postgres-R can't
address this one ;)
3) PostgreSQL Replicator has some very nice conflict resolution schemes.

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.

On Tue, 12 Jun 2001, Zeugswetter Andreas SB wrote:

Doing a replicate all or nothing approach that only works synchronous
is imho not flexible enough.

I agree. Partial and asynchronous replication need to be addressed,
and some of the common functionality of Postgres-R could possibly
be used to meet those needs.

Thanks for your feedback,

Darren

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.

Andreas

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.

Yes there is significant overhead with opening a connection to a
client, so Postgres-R creates a pool of backends at start up,
coupled with the group communication system (Ensemble) that
significantly reduces this issue.

Very good points,

Darren

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.

I don't know about other *async* replication engines but Rserv
keeps track of transactions (if I understood you corectly).
Rserv transfers not individual modified data items but
*consistent* snapshot of changes to move slave database from
one *consistent* state (when all RI constraints satisfied)
to another *consistent* state.

4) The activation of triggers in a database cannot be easily
rolled back or undone.

What do you mean?

Vadim

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.

I don't know about other *async* replication engines but Rserv
keeps track of transactions (if I understood you corectly).
Rserv transfers not individual modified data items but
*consistent* snapshot of changes to move slave database from
one *consistent* state (when all RI constraints satisfied)
to another *consistent* state.

I thought Andreas did a good job of correcting me here. Transaction-
based replication with triggers do not apply to points 1 and 4. I
should have made a distinction between non-transaction and
transaction based replication with triggers. I was not trying to
single out rserv or any other project, and I can see how my wording
implies this misinterpretation (my apologies).

4) The activation of triggers in a database cannot be easily
rolled back or undone.

What do you mean?

Once the trigger fires, it is not an easy task to abort that
execution via rollback or undo. Again this is not an issue
with a transaction-based trigger approach.

Sincerely,

Darren

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
__________________________________________________

reinoud@xs4all.nl (Reinoud van Leeuwen) writes:

Well as I read back the thread I see 2 different approaches to
replication:
...
I can think of some scenarios where I would definitely want to
*choose* one of the options.

Yes. IIRC, it looks to be possible to support a form of async
replication using the Postgres-R approach: you allow the cluster
to break apart when communications fail, and then rejoin when
your link comes back to life. (This can work in principle, how
close it is to reality is another question; but the rejoin operation
is the same as crash recovery, so you have to have it anyway.)

So this seems to me to allow getting most of the benefits of the async
approach. OTOH it is difficult to see how to go the other way: getting
the benefits of a synchronous solution atop a basically-async
implementation doesn't seem like it can work.

regards, tom lane

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

I do not understand that point, if it is trigger based, you
have all the flexibility you need. (only some tables, only some rows,
different rows to different targets ....),
(or do you mean not all targets, that could also be achieved with triggers)

- what happens if one server is down? (or the network between) are
commits still possible

No, updates are not possible if one target is not reachable,
that would not be synchronous and would again need business rules
to resolve conflicts.

Allowing updates when a target is not reachable would require admin
intervention.

Andreas

- only full replication possible

I do not understand that point, if it is trigger based, you
have all the flexibility you need. (only some tables, only some rows,
different rows to different targets ....),
(or do you mean not all targets, that could also be achieved with

triggers)

Currently with Postgres-R, it is one database replicating all tables to
all servers in the group communication system. There are some ways
around
this by invoking the -r option when a SQL statement should be replicated,
and leaving the -r option off for non-replicated scenarios. IMHO this is
not a good solution.

A better solution will need to be implemented, which involves a
subscription table(s) with relation/server information. There are two
ideas for subscribing and receiving replicated data.

1) Receiver driven propagation - A simple solution where all
transactions are propagated and the receiving servers will reference
the subscription information before applying updates.

2) Sender driven propagation - A more optimal and complex solution
where servers do not receive any messages regarding data items for
which they have not subscribed

- what happens if one server is down? (or the network between) are
commits still possible

No, updates are not possible if one target is not reachable,

AFAIK, Postgres-R can still replicate if one target is not reachable,
but only to the remaining servers ;).

There is a scenario that could arise if a server issues a lock
request then fails or goes off line. There is code that checks
for this condition, which needs to be merged with the branch we have.

that would not be synchronous and would again need business rules
to resolve conflicts.

Yes the failed server would not be synchronized, and getting this
failed server back in sync needs to be addressed.

Allowing updates when a target is not reachable would require admin
intervention.

In its current state yes, but our goal would be to eliminate this
requirement as well.

Darren