Slony1 PostgreSQL Replication - How can I avoid the delay

Author

How can I avoid the delay

Marco Canderle

2006-03-30, 8:28 pm

Hi, I'm new here so hello everyone. I've been experimenting with Slony-I to
deploy a replication system for the database at my work.

My objective is to have a master server in headquarter office, replicating
to every slave server at the satellite offices. The idea is that users
located in headquarters read from and write to master server. The users
located in satellite offices read from the local slave server and write
directly to the master server (which then replicates to all slaves
databases, including the one who originate the write).
This has to be done this way mainly for two reasons:
-If the master gets offline, the remote users can continue accessing
information because they read from the local slave server.
-An increase in the response time: The system is web-based (implemented
in PHP). For the remote users, in the other end of the country, the response
time is much more slower than for the users near the master server, due to
the Internet connection. And the majority of the database access are reads
not writes.

As I said, Reads are solved locally by every local slave server, and writes
are routed directly to the master server, which then replicates the changes
made.
Here is where my problem arise. If a user is connected to a slave server and
needs to make changes to de database, the writes will be passed to the
master server. The master will make the corresponding changes and Slony-I
will replicate this changes back to the slave who passed the writes (and all
other slaves). All this steps adds a delay until the remote user can see the
changes he/she made to the database reflected in his screen. For example if
the user adds a new product to the database, he won't see it in the products
list until the replication takes place.
This kind of situations can make the system show wrong information, which is
dangerous in some scenarios of my system.

So I ask: Are there any solutions that may help me reduce/avoid this delay
or at least prevent the slave server to respond to the user until the
replication have propagated to all the slaves (or at least the one which
originates the writes)? Maybe some kind of combination between asynchronous
replication (provided by Slony-I) and synchronous replication?

I hope I've explained this clear enough. Thanks for your time!

********************
************
Marco A. Canderle
marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org
********************
************

Christopher Browne

2006-03-30, 8:28 pm

"Marco Canderle" <marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org> writes:
> Hi, I'm new here so hello everyone. I've been experimenting with
> Slony-I to deploy a replication system for the database at my work.

> My objective is to have a master server in headquarter office,
> replicating to every slave server at the satellite offices. The idea
> is that users located in headquarters read from and write to master
> server. The users located in satellite offices read from the local
> slave server and write directly to the master server (which then
> replicates to all slaves databases, including the one who originate
> the write).

> This has to be done this way mainly for two reasons:

> =A0=A0 -If the master gets offline, the remote users can continue
> accessing information because they read from the local slave server.

> =A0=A0 -An increase in the response time: The system is web-based
> (implemented in PHP). For the remote users, in the other end of the
> country, the response time is much more slower than for the users
> near the master server, due to the Internet connection. And the
> majority of the database access are reads not writes.

> As I said, Reads are solved locally by every local slave server, and
> writes are routed directly to the master server, which then
> replicates the changes made.

> Here is where my problem arise. If a user is connected to a slave
> server and needs to make changes to de database, the writes will be
> passed to the master server. The master will make the corresponding
> changes and Slony-I will replicate this changes back to the slave
> who passed the writes (and all other slaves). All this steps adds a
> delay until the remote user can see the changes he/she made to the
> database reflected in his screen. For example if the user adds a new
> product to the database, he won't see it in the products list until=A0
> the replication takes place. This kind of situations can make the
> system show wrong information, which is dangerous in some scenarios
> of my system.

> So I ask: Are there any solutions that may help me reduce/avoid this
> delay or at least prevent the slave server to respond to the user
> until the replication have propagated to all the slaves (or at least
> the one which originates the writes)? Maybe some kind of combination
> between asynchronous replication (provided by Slony-I) and
> synchronous replication?

> I hope I've explained this clear enough. Thanks for your time!

I think you have explained it clearly enough...

It doesn't seem to me that there is any possible resolution to your
problem.

1. As an asynchronous system, Slony-I inherently separates updates on
the master system from when they are applied downstream.

You can't use the "slave" for reads and be certain that there aren't
updates pending.

In effect, for the problem you outline at the end, Slony-I isn't an
answer.

2. You can't have the synchronization you require without going back
to reference "master" data on the "master" node.

This points towards the thought that what you want is quite likely to
be *unachievable*.

That may be unfortunate, but it's kind of useful to know, ahead of
time, what sorts of things are actually impossible. It means you
don't need to waste time trying to solve them, only to be more or less
assured of failure...

It sounds as though your application really and truly needs to
reference "master" data, which means that if the master is offline,
your applications cannot get trustworthy results.

Let me point out the *troublesome* case...

- Suppose the connection goes down.

- Then, suppose someone at head office updates their system, promising
inventory to someone, or removing a product from the list. =

Update A.

- Suppose, during the network outage, someone at the satellite site
promises that same inventory to someone, or promises that product that
head office just removed.

Update B. Which is in conflict with Update A.

The only way to prevent these dueling updates from occuring is via
"sychronicity," where both are applied directly to the "head office"
system. Unfortunately, that won't work here either, because the
connection to head office is down.

An asynchronous system is inherently vulnerable to these conflicting
updates, well, conflicting.

A synchronous system is inherently vulnerable to the connection going
down and preventing the remote site from doing business.

There isn't a third answer to give :-(.
-- =

output =3D reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Hannu Krosing

2006-03-30, 8:28 pm

w5xoZWwga2VuYWwgcMOk
ZXZhbCwgTiwgMjAwNi0w
My0zMCBrZWxsIDE5OjEw
LCBraXJqdXRhcyBN
YXJjbyBDYW5kZXJsZToK
PiBIaSwgSSdtIG5ldyBo
ZXJlIHNvIGhlbGxvIGV2
ZXJ5b25lLiBJJ3Zl
IGJlZW4gZXhwZXJpbWVu
dGluZyB3aXRoCj4gU2xv
bnktSSB0byBkZXBsb3kg
YSByZXBsaWNhdGlv
biBzeXN0ZW0gZm9yIHRo
ZSBkYXRhYmFzZSBhdCBt
eSB3b3JrLiAKPiAKPiBN
eSBvYmplY3RpdmUg
aXMgdG8gaGF2ZSBhIG1h
c3RlciBzZXJ2ZXIgaW4g
aGVhZHF1YXJ0ZXIgb2Zm
aWNlLAo+IHJlcGxp
Y2F0aW5nIHRvIGV2ZXJ5
IHNsYXZlIHNlcnZlciBh
dCB0aGUgc2F0ZWxsaXRl
IG9mZmljZXMuIFRo
ZSBpZGVhCj4gaXMgdGhh
dCB1c2VycyBsb2NhdGVk
IGluIGhlYWRxdWFydGVy
cyByZWFkIGZyb20g
YW5kIHdyaXRlIHRvIG1h
c3Rlcgo+IHNlcnZlci4g
VGhlIHVzZXJzIGxvY2F0
ZWQgaW4gc2F0ZWxs
aXRlIG9mZmljZXMgcmVh
ZCBmcm9tIHRoZSBsb2Nh
bAo+IHNsYXZlIHNlcnZl
ciBhbmQgd3JpdGUg
ZGlyZWN0bHkgdG8gdGhl
IG1hc3RlciBzZXJ2ZXIg
KHdoaWNoIHRoZW4KPiBy
ZXBsaWNhdGVzIHRv
IGFsbCBzbGF2ZXMgZGF0
YWJhc2VzLCBpbmNsdWRp
bmcgdGhlIG9uZSB3aG8g
b3JpZ2luYXRlCj4g
dGhlIHdyaXRlKS4gCj4g
VGhpcyBoYXMgdG8gYmUg
ZG9uZSB0aGlzIHdheSBt
YWlubHkgZm9yIHR3
byByZWFzb25zOiAKPiAg
ICAtSWYgdGhlIG1hc3Rl
ciBnZXRzIG9mZmxpbmUs
IHRoZSByZW1vdGUg
dXNlcnMgY2FuIGNvbnRp
bnVlCj4gYWNjZXNzaW5n
IGluZm9ybWF0aW9uIGJl
Y2F1c2UgdGhleSBy
ZWFkIGZyb20gdGhlIGxv
Y2FsIHNsYXZlIHNlcnZl
ci4KPiAgICAtQW4gaW5j
cmVhc2UgaW4gdGhl
IHJlc3BvbnNlIHRpbWU6
IFRoZSBzeXN0ZW0gaXMg
d2ViLWJhc2VkCj4gKGlt
cGxlbWVudGVkIGlu
IFBIUCkuIEZvciB0aGUg
cmVtb3RlIHVzZXJzLCBp
biB0aGUgb3RoZXIgZW5k
IG9mIHRoZQo+IGNv
dW50cnksIHRoZSByZXNw
b25zZSB0aW1lIGlzIG11
Y2ggbW9yZSBzbG93ZXIg
dGhhbiBmb3IgdGhl
IHVzZXJzIG5lYXIKPiB0
aGUgbWFzdGVyIHNlcnZl
ciwgZHVlIHRvIHRoZSBJ
bnRlcm5ldCBjb25u
ZWN0aW9uLiBBbmQgdGhl
IG1ham9yaXR5IG9mCj4g
dGhlIGRhdGFiYXNlIGFj
Y2VzcyBhcmUgcmVh
ZHMgbm90IHdyaXRlcy4g
Cj4gCj4gQXMgSSBzYWlk
LCBSZWFkcyBhcmUgc29s
dmVkIGxvY2FsbHkg
YnkgZXZlcnkgbG9jYWwg
c2xhdmUgc2VydmVyLCBh
bmQKPiB3cml0ZXMgYXJl
IHJvdXRlZCBkaXJl
Y3RseSB0byB0aGUgbWFz
dGVyIHNlcnZlciwgd2hp
Y2ggdGhlbiByZXBsaWNh
dGVzCj4gdGhlIGNo
YW5nZXMgbWFkZS4gCj4g
SGVyZSBpcyB3aGVyZSBt
eSBwcm9ibGVtIGFyaXNl
LiBJZiBhIHVzZXIg
aXMgY29ubmVjdGVkIHRv
IGEgc2xhdmUKPiBzZXJ2
ZXIgYW5kIG5lZWRzIHRv
IG1ha2UgY2hhbmdl
cyB0byBkZSBkYXRhYmFz
ZSwgdGhlIHdyaXRlcyB3
aWxsIGJlCj4gcGFzc2Vk
IHRvIHRoZSBtYXN0
ZXIgc2VydmVyLiBUaGUg
bWFzdGVyIHdpbGwgbWFr
ZSB0aGUgY29ycmVzcG9u
ZGluZwo+IGNoYW5n
ZXMgYW5kIFNsb255LUkg
d2lsbCByZXBsaWNhdGUg
dGhpcyBjaGFuZ2VzIGJh
Y2sgdG8gdGhlIHNs
YXZlIHdobwo+IHBhc3Nl
ZCB0aGUgd3JpdGVzIChh
bmQgYWxsIG90aGVyIHNs
YXZlcykuIEFsbCB0
aGlzIHN0ZXBzIGFkZHMg
YSBkZWxheQo+IHVudGls
IHRoZSByZW1vdGUgdXNl
ciBjYW4gc2VlIHRo
ZSBjaGFuZ2VzIGhlL3No
ZSBtYWRlIHRvIHRoZSBk
YXRhYmFzZQo+IHJlZmxl
Y3RlZCBpbiBoaXMg
c2NyZWVuLiBGb3IgZXhh
bXBsZSBpZiB0aGUgdXNl
ciBhZGRzIGEgbmV3IHBy
b2R1Y3QgdG8KPiB0
aGUgZGF0YWJhc2UsIGhl
IHdvbid0IHNlZSBpdCBp
biB0aGUgcHJvZHVjdHMg
bGlzdCB1bnRpbCAg
dGhlCj4gcmVwbGljYXRp
b24gdGFrZXMgcGxhY2Uu
IAo+IFRoaXMga2luZCBv
ZiBzaXR1YXRpb25z
IGNhbiBtYWtlIHRoZSBz
eXN0ZW0gc2hvdyB3cm9u
ZyBpbmZvcm1hdGlvbiwK
PiB3aGljaCBpcyBk
YW5nZXJvdXMgaW4gc29t
ZSBzY2VuYXJpb3Mgb2Yg
bXkgc3lzdGVtLgo+IAo+
IFNvIEkgYXNrOiBB
cmUgdGhlcmUgYW55IHNv
bHV0aW9ucyB0aGF0IG1h
eSBoZWxwIG1lIHJlZHVj
ZS9hdm9pZCB0aGlz
Cj4gZGVsYXkgb3IgYXQg
bGVhc3QgcHJldmVudCB0
aGUgc2xhdmUgc2VydmVy
IHRvIHJlc3BvbmQg
dG8gdGhlIHVzZXIKPiB1
bnRpbCB0aGUgcmVwbGlj
YXRpb24gaGF2ZSBwcm9w
YWdhdGVkIHRvIGFs
bCB0aGUgc2xhdmVzIChv
ciBhdCBsZWFzdAo+IHRo
ZSBvbmUgd2hpY2ggb3Jp
Z2luYXRlcyB0aGUg
d3JpdGVzKT8gTWF5YmUg
c29tZSBraW5kIG9mIGNv
bWJpbmF0aW9uCj4gYmV0
d2VlbiBhc3luY2hy
b25vdXMgcmVwbGljYXRp
b24gKHByb3ZpZGVkIGJ5
IFNsb255LUkpIGFuZCBz
eW5jaHJvbm91cwo+
IHJlcGxpY2F0aW9uPyAK
CkkgdGhpbmsgdGhhdCB3
aGF0IHlvdSBuZWVkLCBp
cyBub3Qgc2xvbnks
IGJ1dCBzb21lIG90aGVy
IHN5c3RlbSwgc2ltaWxh
cgp0byBzbG9ueSwgbWF5
YmUgZXZlbiB1c2lu
ZyBwYXJ0cyBvZiBzbG9u
eS4KClRoZSBwcm9ibGVt
IGlzIHJlbGx5IG5vdCAi
SWYvd2hlbiB0aGUg
bWFzdGVyIGdldHMgb2Zm
bGluZSIgYnV0ICJJZi93
aGVuCnRoZSBtYXN0ZXIg
YmVjb21lcyBpbmFj
Y2Vzc2libGUgZnJvbSBz
bGF2ZShzKSIsIHVzdWFs
bHkgZHVlIHRvIG5ldHdv
cmsKcHJvYmxlbXMu
CgpZb3Ugd2lsbCBtb3N0
IGxpa2VseSBuZWVkIGFu
IHN5bmNocm9ub3VzIHNv
bWUgcXVldWVpbmcg
c3lzdGVtIHdoaWNoIGNh
bgpkZXRlY3QgdGhpcyBj
b25kaXRpb24sIGFuZCBz
YXZlIHVwZGF0ZXMg
aW4gYSBsb2NhbCBxdWV1
ZSBpZiBtYXN0ZXIgaXMK
aW5hY2Nlc3NpYmxlLiBZ
b3Ugc3RpbGwgbmVl
ZCB0byBjb25zaWRlciBh
bGwgcG9zc2libGUKaW5h
Y2Nlc3NpYmlsaXR5L2Rv
d25uZXNzIGNhc2Vz
IGFuZCBkZXZpc2UgYXBw
cm9wcmlhdGUgc29sdXRp
b25zIChvciB0ZWxsCnlv
dXIgY3VzdG9tZXJz
L2J1c2luZXNzcGVvcGxl
IGFib3V0IHRoZSBwb3Nz
aWJsZSAgZmFpbHVyZSBt
b2RlcyBhbmQgbGV0
CnRoZW0gYXBwcm92ZSB0
aGVzZSAob3Igd29ycnkg
YWJvdXQgYnV5aW5nIHJl
ZHVuZGFudCBuZXR3
b3JrCmNvbm5lY3Rpb25z
L3NlcnZlciBoYXJkd2Fy
ZSBpZiB0aGUgZGF0YSBh
dmFpbGFiaWxpdHkg
aXMgcmVhbGx5CmNyaXRp
Y2FsKSkKCi0tLS0tLS0t
LS0tLQpIYW5udQoKCl9f
X19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fClNsb255MS1nZW5l
cmFsIG1haWxpbmcg
bGlzdApTbG9ueTEtZ2Vu
ZXJhbEBnYm9yZy5wb3N0
Z3Jlc3FsLm9yZwpodHRw
Oi8vZ2JvcmcucG9z
dGdyZXNxbC5vcmcvbWFp
bG1hbi9saXN0aW5mby9z
bG9ueTEtZ2VuZXJhbAo=

Miguel

2006-03-30, 8:28 pm

Marco Canderle wrote:

>Hi, I'm new here so hello everyone. I've been experimenting with Slony-I to
>deploy a replication system for the database at my work.
>
>My objective is to have a master server in headquarter office, replicating
>to every slave server at the satellite offices. The idea is that users
>located in headquarters read from and write to master server. The users
>located in satellite offices read from the local slave server and write
>directly to the master server (which then replicates to all slaves
>databases, including the one who originate the write).
>This has to be done this way mainly for two reasons:
> -If the master gets offline, the remote users can continue accessing
>information because they read from the local slave server.
> -An increase in the response time: The system is web-based (implemented
>in PHP). For the remote users, in the other end of the country, the response
>time is much more slower than for the users near the master server, due to
>the Internet connection. And the majority of the database access are reads
>not writes.
>
>As I said, Reads are solved locally by every local slave server, and writes
>are routed directly to the master server, which then replicates the changes
>made.
>Here is where my problem arise. If a user is connected to a slave server and
>needs to make changes to de database, the writes will be passed to the
>master server. The master will make the corresponding changes and Slony-I
>will replicate this changes back to the slave who passed the writes (and all
>other slaves). All this steps adds a delay until the remote user can see the
>changes he/she made to the database reflected in his screen. For example if
>the user adds a new product to the database, he won't see it in the products
>list until the replication takes place.
>This kind of situations can make the system show wrong information, which is
>dangerous in some scenarios of my system.
>
>So I ask: Are there any solutions that may help me reduce/avoid this delay
>
>

use fiber channels between masters and slaves and robust high
performance hardware?

cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org

2006-03-31, 3:32 am

> Marco Canderle wrote:
>
>
> use fiber channels between masters and slaves and robust high
> performance hardware?

In principle, that could work; fibrechannel can, in theory, operate over
distances up to 50km...

That therefore could potentially work, if we assume all satellite offices
are within 50km of HQ.

I think the point was to allow remote network connections...

Jens-Wolfhard Schicke

2006-03-31, 3:32 am

--On Donnerstag, M=E4rz 30, 2006 19:10:06 -0300 Marco Canderle =

<marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org> wrote:
> As I said, Reads are solved locally by every local slave server, and
> writes are routed directly to the master server, which then replicates
> the changes made.
> Here is where my problem arise. If a user is connected to a slave server
> and needs to make changes to de database, the writes will be passed to
> the master server. The master will make the corresponding changes and
> Slony-I will replicate this changes back to the slave who passed the
> writes (and all other slaves). All this steps adds a delay until the
> remote user can see the changes he/she made to the database reflected in
> his screen. For example if the user adds a new product to the database,
> he won't see it in the products list until the replication takes place.
> This kind of situations can make the system show wrong information, which
> is dangerous in some scenarios of my system.
>
> So I ask: Are there any solutions that may help me reduce/avoid this
> delay or at least prevent the slave server to respond to the user until
> the replication have propagated to all the slaves (or at least the one
> which originates the writes)? Maybe some kind of combination between
> asynchronous replication (provided by Slony-I) and synchronous
> replication?

For once, you can reduce the interval slony uses to synchronize the updates =

around, no idea how the options are named, only twiddled with them a bit a =

time ago. This could reduce the delay, but would not remove it.

I am not aware of a solution within slony, yet I can easily think of an =

architecture which might do what you want:
Before every write, make the writing process first insert the row into the =

master server, insert a "object is dirty" row into a special table _at =

every satelite location_ and commit at the satelites, then commit the =

master. This way, the information that a row is dirty is known at every =

satelite server.

You could then either connect a slightly modified slony-replication to your =

satelite server which instead of syncing does remove the marker rows (we =

did something similar at our company for other purposes). Or you could =

(which I'd clearly prefer, include the transaction id within your marker =

rows. If your client then requests a row from the local database it would =

need to have a look into the special table and could then say something =

along the lines of "object is currently being edited"...

If the connection goes down, well writing gets impossible, but that's the =

case anyhow... And of course your local data gets non-up-to-date, well no =

way to change that. At least it should make conflicting updates impossible.

Mit freundlichem Gru=DF
Jens Schicke
-- =

Jens Schicke j.schicke-Lm9TY+Exa7c@public.gmane.org
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400

Rod Taylor

2006-03-31, 9:33 am

On Thu, 2006-03-30 at 19:10 -0300, Marco Canderle wrote:
> Hi, I'm new here so hello everyone. I've been experimenting with
> Slony-I to deploy a replication system for the database at my work.
>
> My objective is to have a master server in headquarter office,
> replicating to every slave server at the satellite offices. The idea
> is that users located in headquarters read from and write to master
> server. The users located in satellite offices read from the local
> slave server and write directly to the master server (which then
> replicates to all slaves databases, including the one who originate
> the write).
> This has to be done this way mainly for two reasons:
> -If the master gets offline, the remote users can continue
> accessing information because they read from the local slave server.
> -An increase in the response time: The system is web-based
> (implemented in PHP). For the remote users, in the other end of the
> country, the response time is much more slower than for the users near
> the master server, due to the Internet connection. And the majority of
> the database access are reads not writes.

I have a few places that I needed to synchronize as well. One of the
nicer bits about Slony is that it is possible to determine the current
replication status. That is, which parts have been replicated and which
parts are still pending replication.

1) Check out the sl_status query for some hints about how to find out
the status of all nodes (not just the current one) as known by this
machine.

2) Record a local transaction log that you use to 'munge' the results.
If you write an INSERT to the master at time X, then you need to show
the additional tuple on the screen to the end user (say in a
non-modifiable state to indicate the operation is pending completion)
until the slave status reaches time X.

When everything is working, you will find the transaction logs are quite
short. When things are not functioning very well then at least each
individual user has a fairly representative session of the last state
(as their user knew it -- different users may see a different state)

I have had success doing something very similar (I actually allow full
editing of the transaction logged entries by the same user who did the
first modification) with well partitioned data (limited number of users
can access any individual datablock).

I assume you have already solved write conflicts that occur when 2 or
more employees edit the same record.

--

Dirk Jagdmann

2006-04-01, 7:27 am

Hello Marco,

> So I ask: Are there any solutions that may help me reduce/avoid this delay
> or at least prevent the slave server to respond to the user until the
> replication have propagated to all the slaves (or at least the one which
> originates the writes)? Maybe some kind of combination between asynchronous
> replication (provided by Slony-I) and synchronous replication?

I think you should not try to solve this issue in the database, but
rather your application. I would suggest you setup your application,
so that read requests to the database are initially done on the slave
servers (for remote sites) and once you have to do any write queries
you switch your database connection to the master site and from that
point on use the master db for the rest of the application session. Of
course you could be a bit more fancier, so that you add a timeout
value (for example 1min, but you should see how fast your replication
works) and if there have not been any write queries in the last
"timeout" minutes (or seconds or whatever) you can switch your
database connection back to a slave.

--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org

Marco Canderle

2006-04-04, 8:29 pm

Hi again. I've read all the possible solutions you've proposed. Of course I
have to thank you all for this. Thank you very much for your time.

As Christopher Browne, Hannu Krosing, Jens Schicke and Dirk Jagdmann said,
Slony can't solve this problem on its own. However I will use Slony anyway,
for backup purposes, and to take advantage of the fail-over Slony provides.
To solve the synchronization problem, what Dirk proposed, was what I had in
mind beforehand, so that's the primary solution I will try for that issue.
Thanks Dirk!

While I implement and test this solution, one of my partners will try with a
VPN between Headquarters and the other offices. So all users (remote or
local from master server point of view) will read and write to master,
hoping that VPN will improve greatly the response time for my application. I
think that will work for now...The slaves will be available as read-only
access for the users in remote offices, should the master fall down.

Many Thanks to all of you for taking the time to answer me. I hope this
doubt helps someone else, and I hope to help you back sometime.

I will write again here if I make any advance with all this. thanks again.

Marco.

On 4/1/06, Dirk Jagdmann <jagdmann- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org > wrote:
>
> Hello Marco,
>
> delay
> asynchronous
>
> I think you should not try to solve this issue in the database, but
> rather your application. I would suggest you setup your application,
> so that read requests to the database are initially done on the slave
> servers (for remote sites) and once you have to do any write queries
> you switch your database connection to the master site and from that
> point on use the master db for the rest of the application session. Of
> course you could be a bit more fancier, so that you add a timeout
> value (for example 1min, but you should see how fast your replication
> works) and if there have not been any write queries in the last
> "timeout" minutes (or seconds or whatever) you can switch your
> database connection back to a slave.
>
> --
> ---> Dirk Jagdmann
> ----> http://cubic.org/~doj <http://cubic.org/%7Edoj>
> -----> http://llg.cubic.org
>

--
********************
************
Marco A. Canderle
marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org
********************
************

Jan Wieck

2006-04-04, 8:29 pm

On 4/4/2006 2:36 PM, Marco Canderle wrote:

> Hi again. I've read all the possible solutions you've proposed. Of course I
> have to thank you all for this. Thank you very much for your time.
>
> As Christopher Browne, Hannu Krosing, Jens Schicke and Dirk Jagdmann said,
> Slony can't solve this problem on its own. However I will use Slony anyway,
> for backup purposes, and to take advantage of the fail-over Slony provides.
> To solve the synchronization problem, what Dirk proposed, was what I had in
> mind beforehand, so that's the primary solution I will try for that issue.
> Thanks Dirk!
>
> While I implement and test this solution, one of my partners will try with a
> VPN between Headquarters and the other offices. So all users (remote or
> local from master server point of view) will read and write to master,
> hoping that VPN will improve greatly the response time for my application. I
> think that will work for now...The slaves will be available as read-only
> access for the users in remote offices, should the master fall down.

In case your VPN software supports compression, during my WAN tests
using ssh tunnels to run slony replication I found that activating
compression on the tunnels improved the replication throughput
significantly.

Jan

>
> Many Thanks to all of you for taking the time to answer me. I hope this
> doubt helps someone else, and I hope to help you back sometime.
>
> I will write again here if I make any advance with all this. thanks again.
>
> Marco.
>
> On 4/1/06, Dirk Jagdmann <jagdmann- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org > wrote:
>
>
>
> --
> ********************
************
> Marco A. Canderle
> marcocanderle- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org
> ********************
************
>
>
>
> ------------------------------------------------------------------------
>
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Sponsored Links