Обсуждение: Replication

Hello,

i need make with postgre something as this:

i have two servers S1 and  S2, connection is quit good, but sometimes once or twice per month there is short disconnection, longer discoonection 2x per year (1-2+ hours)
on both are running updates
i need have same data in time, but i never update same collumns on S1 an S2, S1 change another atributes than S2 =  there are no conflicts, on MS SQL i use transactional replication and i like find somethin same for postgre.

Can u please send me some info where can i found what i need, which postgre tool is right way for me..

Thnx a lot.

Did you try Slony with PostgreSQL?

--------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Hi,

did you check the manual about 'High Availability and Load Balancing"?
http://www.postgresql.org/docs/8.2/static/high-availability.html

Regards

Markus

Hi,

anorganic anorganic wrote:
 >> did you check the manual about 'High Availability and Load
 >> Balancing"?
 >> http://www.postgresql.org/docs/8.2/static/high-availability.html
 >
> yes but this was really short i think.

Interesting... where exactly is it to short for you? What else would you
expect?

> I need that: s1 and s2 must be multimaster replication asyn. but make
> replication in real time. Changes are immediately send to second server.

Slony can probably be used for such a setup, although it's primarily
designed for single master, async replication.

> OR: s1 is master s2 slave, s1 change some columns on s2, and s2 is
> master s1
> slave and s2 change another columns in one time.

I don't quite follow what you mean here. I assume you mean rows, not
columns. Then probably you mean something like what we call data
partitioning, where each server is a master for a certain subset of the
data.

> I read about slony but, it looks that's make changes not immediatly but in
> packages = not send one transaction but wait and send package. This
> could be a problem.

Hm. If that really is a problem, think again about sync vs. async. In
async replication, there is a delay by definition. But again, I'm not
sure what Slony can do to minimize that delay.

> I hope slony online system is right for me. And these question
> about slony i have more than one i think it could be rigth way. Somebody
> who run it could be great person for me :).

Probably ask on the slony mailing list. I'm not exactly a slony expert
either.

Regards

Markus

Hi,

[ please keep CCing to the list (reply all), as this certainly isn't a
personal discussion and could help others. ]

anorganic anorganic wrote:
> Hello,
>
> "I don't quite follow what you mean here. I assume you mean rows, not
> columns. Then probably you mean something like what we call data
> partitioning, where each server is a master for a certain subset of the
> data."
>
> I think granurality, some system allow replicate only all db, all table,
> some allow to choose only 4 colums from one table 2 from another and
> replicated only these parts of db. I can add example.
> it's not  partitioning.

Uhm.. I'd still call it partitioning. Although, I'm not exactly sure why
you want to replicate only a partial row. I suspect you will have to
split into two tables and have one table replicated while not
replicating the other.

> "Interesting... where exactly is it to short for you? What else would you
> expect?"
>
> ok may be i'm false but i think this all is not common features of postgre
> and it's necessary use system as slony.
> i want know what i must use, how i can make and detail description of these
> solution. What's happen when connection crash, when server shutdown etc.

Right, but as we only have external solutions, I think it makes sense to
have them document how exactly they work and behave.

> "Hm. If that really is a problem, think again about sync vs. async. In
> async replication, there is a delay by definition. But again, I'm not
> sure what Slony can do to minimize that delay."
>
> yeah may be i am on bad adress but may be somebody here will say me here i
> used this program fort his solution. Or this problem  have no solution on
> postgre.

I fear replication is such a wide field with that many options, you
probably can't get away with a general approach. I especially think so
because I consider your wishes to be quite exotic for two reasons:

  * realtime, as fast as possible, but certainly not sync
    (You are aware that async, no matter how fast, can lead to conflicts,
     aren't you?)
  * replicating only parts of a row

> Async, sync i dont need sync, but i need send change fast as possible. i
> don't want sync. But when i change col on s1 i wnat send change right now,
> no waiting for another 5 changes. It must be quite realtime we have low
> traffic between servers there is not necessary make some optimilaztion as
> sending in packages.

I'd be surprised if Slony couldn't be configured to send changes
immediately (i.e. not waiting for more transactions before sending a
package). But again, the Slony guys may know better.

Regards

Markus

Hi,

[ please keep CCing to the list (reply all), as this certainly isn't a
personal discussion and could help others. ]

anorganic anorganic wrote:
> my opinion:
> partitioning is vertical and horizontal, dived table into two or more
> parts.
 >
> replicated only part of some object,here table = replicated two columns
> ins't partitioning, because table is same ;) and have one part = self table
> ;)

The main point is, you divide data *somehow* into partitions and grant
write rights to only *one* server for each partition, thus allowing you
to use a simpler master-slave replication.

> i want have
> s1 and s2
> tableA
> col1
> col2
> col3
>
> tableA is on s1 and s2 same design
> s1 change only col1, s2 change only col3
> is possible set replication on s1 only for col1 of tableA
> is possible set replication on s2 only for col3 of tableA
>
> this is not partitioning i think

How else would you call it? In what way is it different from what's
commonly known as vertical partitioning?

Couldn't you do what you want with the help of ordinary vertical
partitioning and using a VIEW viewA with all the columns col1, col2, col3?

> ok, sync is not saying something about when is transaction log send...

Uhm.. yes it does. It has to be sent before commit confirmation is sent
to the client (Postgres-R does some optimization here, but basically the
above statement still holds true). It is the async approach, which
doesn't say anything about when changes are sent to other servers.

> sync said: i coimmit transaction when all slaves send me commit :)
> asynch: i make change i commit this change and i send it to my slave. and i
> want do it fast as possible ;)

No matter *how* fast you do that, conflicts can arise if you don't
prevent them somehow.

The requirement to send changes as fast as possible in async replication
rings my alarm bell, because it sounds like your application cannot cope
with conflicts and wants to prevent them by minimizing the propagation
delays. That's prone to race conditions and probably won't work.

However, if you partition your data vertically and do per-table
master-slave replication, you of course don't have to worry about
conflicts, as there is only exactly one master.

Regards

Markus