Skip site navigation (1) Skip section navigation (2)

Re: Replication

From: Markus Schiltknecht <markus(at)bluegap(dot)ch>
To: anorganic anorganic <anorganicphc(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Replication
Date: 2007-01-05 10:42:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

[ 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.



In response to

pgsql-admin by date

Next:From: Andy Shellam (Mailing Lists)Date: 2007-01-05 11:13:32
Subject: Recovering a deleted database problem
Previous:From: ArnauDate: 2007-01-05 10:33:42
Subject: Re: I want to import a database from a customer

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group