From jyotiseth2001@gmail.com Wed Jul  18 07:17:40 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 38A699FA326
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 04:17:40 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 76185-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 04:17:36 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.177])
	by postgresql.org (Postfix) with ESMTP id D63D49FA1D6
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 04:17:34 -0300 (ADT)
Received: by wa-out-1112.google.com with SMTP id k40so135990wah
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 00:17:33 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:content-language;
	b=F22w114i0N0aGjZPp63OwQqv0Le8Wv9pp7QuhV5o4fF7Q8xRhkm79fc1nP/f+GX5kbdkMnwgXiyS/g90qbGKdpvcn757bEDAMytFZQ1tDVlS5IAPslfOb6pTjH2hspzdIzG8EBiVqMztBCXHPJl1+nNmVhNPLeGtikmNmEm+3FM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:content-language;
	b=eLptJsp4PNdn6oiwvMRKYc7YoZIIJ4LIi8GvnqKM31/YHyjW1LbbVwS67112i5mNqUl7kvCJDEmZYIY7IF13U4o3Tot1pln9ZPfnG87Fl9KJ6sMKCTLDfLTh/eV7HolFlcXvACkCoVj73AdDTh1q/sP1Q5hyVDcXCMp2vUmF68k=
Received: by 10.114.169.2 with SMTP id r2mr1219393wae.1184743053208;
	Wed, 18 Jul 2007 00:17:33 -0700 (PDT)
Received: from bitdldt04 ( [122.162.170.43])
	by mx.google.com with ESMTPS id l30sm569264waf.2007.07.18.00.17.30
	(version=SSLv3 cipher=RC4-MD5); Wed, 18 Jul 2007 00:17:32 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: <pgsql-sql@postgresql.org>
Subject: Database Synchronization
Date: Wed, 18 Jul 2007 12:44:25 +0530
Message-ID: <002d01c7c90b$48870560$d9951020$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002E_01C7C939.623F4160"
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfJC0UQporanyFdRNmhFmuRfEEVtw==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/110
X-Sequence-Number: 28845

This is a multipart message in MIME format.

------=_NextPart_000_002E_01C7C939.623F4160
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello ,

 

I have two postgres databases on different linux servers. Postgres database
on one server has some tables that needs to be synchronized from the other
postgres database . What should be the best method for this.

 

Please let me know if someone has idea.

 

Thanks,

Jyoti Seth


------=_NextPart_000_002E_01C7C939.623F4160
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hello ,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I have two postgres databases on different linux =
servers. Postgres
database on one server has some tables that needs to be synchronized =
from the
other postgres database . What should be the best method for =
this.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Please let me know if someone has =
idea.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks,<o:p></o:p></p>

<p class=3DMsoNormal>Jyoti Seth<o:p></o:p></p>

</div>

</body>

</html>

------=_NextPart_000_002E_01C7C939.623F4160--




From dev@archonet.com Wed Jul  18 07:29:31 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 05E199FB5A5
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 04:29:31 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 27901-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 04:29:27 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id BA2059FB32F
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 04:29:27 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 2E5E774BF0;
	Wed, 18 Jul 2007 08:28:37 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 6742D274059;
	Wed, 18 Jul 2007 08:28:38 +0100 (BST)
Message-ID: <469DC124.8000704@archonet.com>
Date: Wed, 18 Jul 2007 08:28:36 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jyoti Seth <jyotiseth2001@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database Synchronization
References: <002d01c7c90b$48870560$d9951020$@com>
In-Reply-To: <002d01c7c90b$48870560$d9951020$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/111
X-Sequence-Number: 28846

Jyoti Seth wrote:
> Hello ,
> 
>  
> 
> I have two postgres databases on different linux servers. Postgres database
> on one server has some tables that needs to be synchronized from the other
> postgres database . What should be the best method for this.

The slony replication package lets you choose what tables you replicate. 
On the destination server the tables will be read-only.

http://www.slony.info/

Take a little time to read through the documentation and experiment with 
a test system first. It works fine, but it's got a lot of complicated 
options.

-- 
   Richard Huxton
   Archonet Ltd



From cbbrowne@acm.org Wed Jul  18 15:04:09 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 6AF839FB4D9
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 12:04:09 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 34106-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 12:03:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id E0A2D9FB51C
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 12:04:04 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 2B4DE3143B; Wed, 18 Jul 2007 17:04:04 +0200 (MET DST)
From: Chris Browne <cbbrowne@acm.org>
X-Newsgroups: pgsql.sql
Subject: Re: Database Synchronization
Date: Wed, 18 Jul 2007 10:57:40 -0400
Organization: cbbrowne Computing Inc
Lines: 30
Message-ID: <60fy3ln4ij.fsf@dba2.int.libertyrms.com>
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@news.hub.org
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.20 (linux)
Cancel-Lock: sha1:HwUzef0A+QU+pScPo8kG7BziW3U=
To: pgsql-sql@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/115
X-Sequence-Number: 28850

dev@archonet.com (Richard Huxton) writes:
> Jyoti Seth wrote:
>> Hello ,
>>  I have two postgres databases on different linux servers. Postgres
>> database
>> on one server has some tables that needs to be synchronized from the other
>> postgres database . What should be the best method for this.
>
> The slony replication package lets you choose what tables you
> replicate. On the destination server the tables will be read-only.
>
> http://www.slony.info/
>
> Take a little time to read through the documentation and experiment
> with a test system first. It works fine, but it's got a lot of
> complicated options.

But it is worth noting one thing about the synchronization...

Slony-I's strategy is pretty simple: One node is considered the
"master," and the other node is forcibly made to conform to what is on
the master.

If you want to synchronize back and forth (e.g. - multimaster
replication of some sort), Slony-I is not suitable...
-- 
"cbbrowne","@","linuxdatabases.info"
http://www3.sympatico.ca/cbbrowne/unix.html
Culus thinks  we should go to trade  shows and see how  many people we
can kill by throwing debian cds at them



From dev@archonet.com Wed Jul  18 15:43:28 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A42989FB4F6
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 12:43:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 05478-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 18 Jul 2007 12:43:24 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 0AB0C9FB4EE
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 12:43:24 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 0B193143F6F;
	Wed, 18 Jul 2007 16:43:17 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 91A7D274059;
	Wed, 18 Jul 2007 16:43:21 +0100 (BST)
Message-ID: <469E3515.4070006@archonet.com>
Date: Wed, 18 Jul 2007 16:43:17 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database Synchronization
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
	<60fy3ln4ij.fsf@dba2.int.libertyrms.com>
In-Reply-To: <60fy3ln4ij.fsf@dba2.int.libertyrms.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/116
X-Sequence-Number: 28851

Chris Browne wrote:
> dev@archonet.com (Richard Huxton) writes:
>>
>> http://www.slony.info/

> But it is worth noting one thing about the synchronization...
> 
> Slony-I's strategy is pretty simple: One node is considered the
> "master," and the other node is forcibly made to conform to what is on
> the master.
> 
> If you want to synchronize back and forth (e.g. - multimaster
> replication of some sort), Slony-I is not suitable...

Except in the case when you can have separate tables for the data. If 
you have a london_sales table only updated in London and a paris_sales 
table only updated in Paris then you have something inching towards 
multi-master.

-- 
   Richard Huxton
   Archonet Ltd



From jyotiseth2001@gmail.com Thu Jul  19 04:33:02 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id A2F2E9FBB6C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 19 Jul 2007 01:33:02 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 60234-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 19 Jul 2007 01:32:50 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.177])
	by postgresql.org (Postfix) with ESMTP id 6BABA9FB8C3
	for <pgsql-sql@postgresql.org>; Thu, 19 Jul 2007 01:32:51 -0300 (ADT)
Received: by wa-out-1112.google.com with SMTP id k40so556946wah
	for <pgsql-sql@postgresql.org>; Wed, 18 Jul 2007 21:32:48 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=gn8tblCUyD8SXc6Mp1TMag9SosUp0S3t9isWe3JTfF1pQTk51MRJxo000S3Q6k53ZV7L3V3oAsf2RzOR6FSTK4Ww7b+Y32UOPLTaamfJ+kFKIUwUbt+yQk5jrfFRTdKEMH550GhCyZvSLRUAdR1ICZpua4vNlpFTz6addfKJRik=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=SkDcHXqPLmcBLbj4HSlzJ0KwbEHnBI/demYqyoJj6cM8jf/CZJeJSSPjlWHgqnXbp7CS5M0zFK4+sg4MYOk9nfEYC5L7ZM9LCUH7CRRkD1JGc3qz0kLbgMJhymhraUdK0zk/7qnBi5Y2hSn4ifjD+xVw+23OlSPFDE1zuszguUQ=
Received: by 10.114.12.9 with SMTP id 9mr2196104wal.1184819567623;
	Wed, 18 Jul 2007 21:32:47 -0700 (PDT)
Received: from bitdldt04 ( [122.162.160.164])
	by mx.google.com with ESMTPS id g25sm1621800wag.2007.07.18.21.32.44
	(version=SSLv3 cipher=RC4-MD5); Wed, 18 Jul 2007 21:32:46 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
	<60fy3ln4ij.fsf@dba2.int.libertyrms.com>
	<469E3515.4070006@archonet.com>
In-Reply-To: <469E3515.4070006@archonet.com>
Subject: Re: Database Synchronization
Date: Thu, 19 Jul 2007 10:01:14 +0530
Message-ID: <004f01c7c9bd$a657c3c0$f3074b40$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcfJUuYz7IkR3a9KTQ6HckXtAchIwgAaovsg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/119
X-Sequence-Number: 28854

Thanks a lot for your suggestions. I have ony single master.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Richard Huxton
Sent: Wednesday, July 18, 2007 9:13 PM
To: Chris Browne
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database Synchronization

Chris Browne wrote:
> dev@archonet.com (Richard Huxton) writes:
>>
>> http://www.slony.info/

> But it is worth noting one thing about the synchronization...
> 
> Slony-I's strategy is pretty simple: One node is considered the
> "master," and the other node is forcibly made to conform to what is on
> the master.
> 
> If you want to synchronize back and forth (e.g. - multimaster
> replication of some sort), Slony-I is not suitable...

Except in the case when you can have separate tables for the data. If 
you have a london_sales table only updated in London and a paris_sales 
table only updated in Paris then you have something inching towards 
multi-master.

-- 
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend




From jyotiseth2001@gmail.com Mon Jul  23 09:28:37 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 9993B9FB3BF
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 06:28:37 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 66418-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 06:28:34 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.178])
	by postgresql.org (Postfix) with ESMTP id 0427E9FB532
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 06:28:33 -0300 (ADT)
Received: by wa-out-1112.google.com with SMTP id k40so2241577wah
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 02:28:32 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=kTSZKmyCYvmnl7Pv5osaYqQy/xDUtZU7PkRYtJdXfN0DKxhY3GfOkHw15DLTrk4Pj8F3u0g2jo+lcyxPdzGoi8bwoNVOAOkZZFt0iJhbTBiUQ9iST3mmHfLml84qE72sdhekWcSERJYQbh1WOg44Vq1nqwKLZHVLjYuk+XrVFVc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=XsAqRfGIjcyjhScxSs2EUu0WpmArjkFEeTPyMFkTbYMkUlfLBnORDa2EUA52ynyhEwjjaS6hZK7glQn29I9tjzcT2USwTcsPLSxr6IISLDCuEpPrpiTmROSdaGJFdvOUuWxAgeADzEJQ0UCdwyN7PgzG3TLuvbiyy9BY7W7iGXc=
Received: by 10.115.92.2 with SMTP id u2mr2784665wal.1185182911331;
	Mon, 23 Jul 2007 02:28:31 -0700 (PDT)
Received: from bitdldt04 ( [122.162.169.7])
	by mx.google.com with ESMTPS id n38sm9548129wag.2007.07.23.02.28.27
	(version=SSLv3 cipher=RC4-MD5); Mon, 23 Jul 2007 02:28:29 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "'Chris Browne'" <cbbrowne@acm.org>
Cc: <pgsql-sql@postgresql.org>
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
	<60fy3ln4ij.fsf@dba2.int.libertyrms.com>
In-Reply-To: <60fy3ln4ij.fsf@dba2.int.libertyrms.com>
Subject: Re: Database Synchronization
Date: Mon, 23 Jul 2007 14:55:21 +0530
Message-ID: <004801c7cd0b$677a4870$366ed950$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfJTY0Vn9kN8hmkSt+XXNc//vg4MgDq+hEw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/132
X-Sequence-Number: 28867

Hello,

When I am installing slony-I from rpm's available at the following
url:http://main.slony.info/downloads/1.2/rpm/ it is giving an error: There
are no installable providers of postgresql-slony1-engine.

In our system postgresql has been installed through YAST. So when we try to
install and configure slony-I through source, it gives the message please
make sure tp build and install postgresql from the sources first.

I am new to both linux and postgres. Please help me in installing and
configuring slony-I on my suse linux m/c.

Thanks,
Jyoti Seth 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Chris Browne
Sent: Wednesday, July 18, 2007 8:28 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database Synchronization

dev@archonet.com (Richard Huxton) writes:
> Jyoti Seth wrote:
>> Hello ,
>>  I have two postgres databases on different linux servers. Postgres
>> database
>> on one server has some tables that needs to be synchronized from the
other
>> postgres database . What should be the best method for this.
>
> The slony replication package lets you choose what tables you
> replicate. On the destination server the tables will be read-only.
>
> http://www.slony.info/
>
> Take a little time to read through the documentation and experiment
> with a test system first. It works fine, but it's got a lot of
> complicated options.

But it is worth noting one thing about the synchronization...

Slony-I's strategy is pretty simple: One node is considered the
"master," and the other node is forcibly made to conform to what is on
the master.

If you want to synchronize back and forth (e.g. - multimaster
replication of some sort), Slony-I is not suitable...
-- 
"cbbrowne","@","linuxdatabases.info"
http://www3.sympatico.ca/cbbrowne/unix.html
Culus thinks  we should go to trade  shows and see how  many people we
can kill by throwing debian cds at them

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate




From ajs@crankycanuck.ca Mon Jul  23 18:13:30 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 1F5489FA1C0
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 15:13:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 99933-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 15:13:25 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id C3EA09FA13B
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 15:13:26 -0300 (ADT)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1ID2PN-0001g3-Rh
	for pgsql-sql@postgresql.org; Mon, 23 Jul 2007 14:13:25 -0400
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id 3D5514050; Mon, 23 Jul 2007 14:13:20 -0400 (EDT)
Date: Mon, 23 Jul 2007 14:13:20 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Database Synchronization
Message-ID: <20070723181320.GA9196@phlogiston.dyndns.org>
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
	<60fy3ln4ij.fsf@dba2.int.libertyrms.com>
	<004801c7cd0b$677a4870$366ed950$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <004801c7cd0b$677a4870$366ed950$@com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/136
X-Sequence-Number: 28871

On Mon, Jul 23, 2007 at 02:55:21PM +0530, Jyoti Seth wrote:
> In our system postgresql has been installed through YAST. So when we try to
> install and configure slony-I through source, it gives the message please
> make sure tp build and install postgresql from the sources first.
> 

You _might_ be able to get this to work by installing whatever extra
bits the YAST installation tool offers (probably something with -dev-
or -src- in it).

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."
		--Damien Katz



From jyotiseth2001@gmail.com Tue Jul  24 07:49:10 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 95D069FA2EF
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 24 Jul 2007 04:49:10 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 40624-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 24 Jul 2007 04:49:07 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.232])
	by postgresql.org (Postfix) with ESMTP id 556409FA2C1
	for <pgsql-sql@postgresql.org>; Tue, 24 Jul 2007 04:49:07 -0300 (ADT)
Received: by qb-out-0506.google.com with SMTP id o12so9878120qba
	for <pgsql-sql@postgresql.org>; Tue, 24 Jul 2007 00:49:06 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=W5Epm7ASxk9bNyHY4nH+i5mTPFk/m3CP5WQoarSz8IHGds7lWzrsyXVbF/fm1QYNISgIRjKnt3yrPqH2tGJwCq+tZHowuq0tq26UDzodnuWbgEBOtVMq2zr/2PRjeP2Vi8ONw9vZirNayaE1faqeGcwJgPGGVH4A4YCr3GvIy+I=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=UkkbzWKdr5HIBHtbgEc6Ee9y5esfbN+jFxbBrL9i44fvHbCJSeeatq6XG8eOCGZ2mZb0wiT+XCL3KRshSgPUJGd8Ww4UMpWZoSxBjE5FOqEdZoL/G3ZDlAuS42R6p6Oev4Ks4ZhaJz2a1T6JdNhkleXWmWONq0DNtyP65JnEhpc=
Received: by 10.141.209.7 with SMTP id l7mr1211316rvq.1185263345682;
	Tue, 24 Jul 2007 00:49:05 -0700 (PDT)
Received: from bitdldt04 ( [122.162.171.152])
	by mx.google.com with ESMTPS id c20sm13250854rvf.2007.07.24.00.49.01
	(version=SSLv3 cipher=RC4-MD5); Tue, 24 Jul 2007 00:49:03 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "'Andrew Sullivan'" <ajs@crankycanuck.ca>
Cc: <pgsql-sql@postgresql.org>
References: <002d01c7c90b$48870560$d9951020$@com>
	<469DC124.8000704@archonet.com>
	<60fy3ln4ij.fsf@dba2.int.libertyrms.com>
	<004801c7cd0b$677a4870$366ed950$@com>
	<20070723181320.GA9196@phlogiston.dyndns.org>
In-Reply-To: <20070723181320.GA9196@phlogiston.dyndns.org>
Subject: Re: Database Synchronization
Date: Tue, 24 Jul 2007 13:15:56 +0530
Message-ID: <000601c7cdc6$ae37f530$0aa7df90$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfNVagh7+Mdq0rXTUuygVe2fJzH8gAb9NTg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/137
X-Sequence-Number: 28872

Hello,

Thanks Andrew for the solution. I have successfully installed following rpm
<< postgresql-slony1-engine-1.1.5-1_PG8.1.4.i686.rpm >> available at
http://main.slony.info/downloads/1.1/rpm/.

But when I am trying to set a cluster its throwing a following error:
<stdin>:4: PGRES_FATAL_ERROR load '$libdir/xxid';  - ERROR:  could not
access file "$libdir/xxid": No such file or directory
<stdin>:4: Error: the extension for the xxid data type cannot be loaded in
database 'dbname=contactdb host=localhost user=postgres'
<stdin>:4: ERROR: no admin conninfo for node 134590736

I have also tried changing the value of libdir to the directory where
xxid.so file is there but couldn't resolve the problem. 

Thanks,
Jyoti Seth

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andrew Sullivan
Sent: Monday, July 23, 2007 11:43 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database Synchronization

On Mon, Jul 23, 2007 at 02:55:21PM +0530, Jyoti Seth wrote:
> In our system postgresql has been installed through YAST. So when we try
to
> install and configure slony-I through source, it gives the message please
> make sure tp build and install postgresql from the sources first.
> 

You _might_ be able to get this to work by installing whatever extra
bits the YAST installation tool offers (probably something with -dev-
or -src- in it).

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."
		--Damien Katz

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings




From jyotiseth2001@gmail.com Wed Jul  25 10:16:03 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 4476B9FB631
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:16:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 98966-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:15:45 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.236])
	by postgresql.org (Postfix) with ESMTP id DB0829FB5FB
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 07:15:54 -0300 (ADT)
Received: by qb-out-0506.google.com with SMTP id o12so12888121qba
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 03:15:53 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=eSS8QH57h91brZTr+bQe/qVa20dIZIFCCE9y7CLERVckX7CPXaTAM6e4a3mcvnsFwtgqFTxaDetNnoPFn6QJOd9LppEJRN1fKCDv6UafvvLdKIS9hCTP40ijjQ/0ZnfIYBqXe2qfujLnC2JO6P4/CAdDy2pXYyUub0BM1zGxzX8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=EZyCkrPx72jMdaa041SUCQ3w3d41gswLcw7tDXxEmlS+DDWtijhNrSi0NaHaA0aWCxY4+jwoDg5cGIa9iLuGarUDPg9VJt3eR6P+dHXcGFbqyWxydr/HEus4867SSi7l/hRVnryH9vCwd25eoH6Hc03E9e61+HGRas6iJ77wcc8=
Received: by 10.114.135.1 with SMTP id i1mr487869wad.1185358552644;
	Wed, 25 Jul 2007 03:15:52 -0700 (PDT)
Received: from bitdldt04 ( [122.162.169.176])
	by mx.google.com with ESMTPS id v35sm838175wah.2007.07.25.03.15.50
	(version=SSLv3 cipher=RC4-MD5); Wed, 25 Jul 2007 03:15:51 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: <pgsql-sql@postgresql.org>
References: <20070724143737.GA25948@apartia.fr>
In-Reply-To: <20070724143737.GA25948@apartia.fr>
Subject: Database synchronization
Date: Wed, 25 Jul 2007 15:42:41 +0530
Message-ID: <004e01c7cea4$58046570$080d3050$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfOALQVjwm19UlTSNiQ79CHHbqCugAoWOQw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/143
X-Sequence-Number: 28878

Hi,

I have two postgres databases "Db1" and "Db2" on different linux servers.
"DB1" is my master server and its data updates very frequently.

My application access DB2 database, which in turn requires data from Db1
database. 

The same can be achieved in either of the following ways:
1. I can have the same tables from Db1 in the Db2 database and use some
replication process to update these tables say using Slony-I. And then our
application accesses the data from the single database.

2. Or I can use dblink to fetch the data from the master database only.

My problem is my master database changes frequently and I always require the
latest information.

Please suggest which is the right approach in this situation.

Thanks,
Jyoti Seth




From dev@archonet.com Wed Jul  25 10:24:19 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 250729FB7FD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:24:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 34549-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:24:14 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 53F6A9FB79C
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 07:24:14 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id EDD9E772A4;
	Wed, 25 Jul 2007 11:24:11 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id D48AB274059;
	Wed, 25 Jul 2007 11:24:11 +0100 (BST)
Message-ID: <46A724CB.3030405@archonet.com>
Date: Wed, 25 Jul 2007 11:24:11 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jyoti Seth <jyotiseth2001@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database synchronization
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
In-Reply-To: <004e01c7cea4$58046570$080d3050$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/144
X-Sequence-Number: 28879

Try not to start a new thread (question) by replying to an old one - it 
can mean some people don't see your question.

Jyoti Seth wrote:
> Hi,
> 
> I have two postgres databases "Db1" and "Db2" on different linux servers.
> "DB1" is my master server and its data updates very frequently.
> 
> My application access DB2 database, which in turn requires data from Db1
> database. 

OK

> The same can be achieved in either of the following ways:
> 1. I can have the same tables from Db1 in the Db2 database and use some
> replication process to update these tables say using Slony-I. And then our
> application accesses the data from the single database.

Yes

> 2. Or I can use dblink to fetch the data from the master database only.

Yes

also:

3. Write a layer in your application / between the application & 
database which routes queries to the correct database.

> My problem is my master database changes frequently and I always require the
> latest information.

Well, if by "latest" you mean you can't have any delay at all you'll 
have to use #2 or #3.

However, that could be slow if you need to join a lot of data from DB1 
to DB2. Can you provide more details of what each contains?

-- 
   Richard Huxton
   Archonet Ltd



From jyotiseth2001@gmail.com Wed Jul  25 10:43:44 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 2EDA49FB64A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:43:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 31283-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 07:43:34 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from nz-out-0506.google.com (nz-out-0506.google.com
 [64.233.162.238])
	by postgresql.org (Postfix) with ESMTP id 9D99D9FB616
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 07:43:40 -0300 (ADT)
Received: by nz-out-0506.google.com with SMTP id l8so120738nzf
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 03:43:39 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=Z3CPZhGsqY3c1nH8PRIEtnxWDw5CghOdrrSjwnevXYbTvX2GHxrp+bYpYn9B8SN5H4RaseRS7ljL5u7gfCqL2GhujPQArqXKLoCVdGW2mEA5Y9hlTla42igto4VCSbNMZ2OnXgWZZ84hp5nKZPtkHfpK+OR7U0XDL4xqZAsSEks=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=qKVsM3MomkMw3F0F5MkLFw0XbWUWHph6+pN1uKzv/85wGqNGdpPnLZHeHU4slJ2eEwUCEHcWZ0M4uLuHllUZe18Kj39kRQpMpptPx+zZ2nbCmaPWJa/f/LUBGfz6cuY49XSd+IHqShU1cJCTxAAXWF07gzbzzDgIg27JKav+QQw=
Received: by 10.141.197.18 with SMTP id z18mr150853rvp.1185360218813;
	Wed, 25 Jul 2007 03:43:38 -0700 (PDT)
Received: from bitdldt04 ( [122.162.169.176])
	by mx.google.com with ESMTPS id l38sm874315rvb.2007.07.25.03.43.35
	(version=SSLv3 cipher=RC4-MD5); Wed, 25 Jul 2007 03:43:37 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
	<46A724CB.3030405@archonet.com>
In-Reply-To: <46A724CB.3030405@archonet.com>
Subject: Re: Database synchronization
Date: Wed, 25 Jul 2007 16:10:27 +0530
Message-ID: <000001c7cea8$39775d20$ac661760$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfOpfIZ1Px6iZaGRzeHppFRZ/5KsgAAA6Ww
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/145
X-Sequence-Number: 28880

My master database contains prices of some products that vary after few
hours and many other details that are also reqd in Db2 which doesn't change
that frequently. My slave database contains the details of queries sent by
the customers.
These queries are to find the price of the product and other details. (Some
part of the info reqd from Db1 varies frequently where some information is a
kind of master data that doesn't vary frequently).

So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
for other details or there is some other better option

Thanks,
Jyoti

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com] 
Sent: Wednesday, July 25, 2007 3:54 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database synchronization

Try not to start a new thread (question) by replying to an old one - it 
can mean some people don't see your question.

I will take care in future.

Jyoti Seth wrote:
> Hi,
> 
> I have two postgres databases "Db1" and "Db2" on different linux servers.
> "DB1" is my master server and its data updates very frequently.
> 
> My application access DB2 database, which in turn requires data from Db1
> database. 

OK

> The same can be achieved in either of the following ways:
> 1. I can have the same tables from Db1 in the Db2 database and use some
> replication process to update these tables say using Slony-I. And then our
> application accesses the data from the single database.

Yes

> 2. Or I can use dblink to fetch the data from the master database only.

Yes

also:

3. Write a layer in your application / between the application & 
database which routes queries to the correct database.

> My problem is my master database changes frequently and I always require
the
> latest information.

Well, if by "latest" you mean you can't have any delay at all you'll 
have to use #2 or #3.

However, that could be slow if you need to join a lot of data from DB1 
to DB2. Can you provide more details of what each contains?

-- 
   Richard Huxton
   Archonet Ltd




From dev@archonet.com Wed Jul  25 12:42:43 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 6CB5B9FB9F8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 09:42:43 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 17923-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 25 Jul 2007 09:42:31 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 322C79FBB55
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 09:42:35 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 508D174E88;
	Wed, 25 Jul 2007 13:39:54 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 37EA127406F;
	Wed, 25 Jul 2007 13:39:54 +0100 (BST)
Message-ID: <46A7449A.4080500@archonet.com>
Date: Wed, 25 Jul 2007 13:39:54 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jyoti Seth <jyotiseth2001@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database synchronization
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
	<46A724CB.3030405@archonet.com>
	<000001c7cea8$39775d20$ac661760$@com>
In-Reply-To: <000001c7cea8$39775d20$ac661760$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/146
X-Sequence-Number: 28881

Jyoti Seth wrote:
> My master database contains prices of some products that vary after few
> hours and many other details that are also reqd in Db2 which doesn't change
> that frequently. My slave database contains the details of queries sent by
> the customers.
> These queries are to find the price of the product and other details. (Some
> part of the info reqd from Db1 varies frequently where some information is a
> kind of master data that doesn't vary frequently).
> 
> So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
> for other details or there is some other better option

If you can live with a time-lag measured in seconds, then slony will 
work just fine. If you can't then you'll want dblink.

One other thing you might consider - if you attach "valid_from" time to 
each price then you can put them in place in advance, and make sure they 
are replicated before needed.

-- 
   Richard Huxton
   Archonet Ltd



From jyotiseth2001@gmail.com Thu Jul  26 05:13:38 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 12A2C9FBAB6
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 26 Jul 2007 02:13:38 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 61687-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 26 Jul 2007 02:12:28 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from py-out-1112.google.com (py-out-1112.google.com
 [64.233.166.181])
	by postgresql.org (Postfix) with ESMTP id 27BC49FB95E
	for <pgsql-sql@postgresql.org>; Thu, 26 Jul 2007 02:00:47 -0300 (ADT)
Received: by py-out-1112.google.com with SMTP id d32so976192pye
	for <pgsql-sql@postgresql.org>; Wed, 25 Jul 2007 22:00:46 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=aJGspnAOKPYvaip5DNZGaRf+LRnFMoI04zyKWvVVYqfCL1oWqtppQPw2ZZhbjL7gvI9WID0y6uvCNm3reAr4zJblBGyF6GYLLw4kUfN2dIJRWuG01zn9Ao7dZ+U6xBnJPThbOzfRgn7PapJ4xsMuN3LMhDpVzXvBG7bLzT7Ru4Q=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:cc:references:in-reply-to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language;
	b=Bdg3XY79cHWYlTXX3r+C6CVeWj6OwubG3Bo0T9TUvYHGph8zW5pROVFNi5eHZTI4zaTwy8taiyGCxtRISw65SGgXU+enJsgCCDfNYVDGK+Kfa5TQMM1yDb3QuQLB3UqMFS9/danANyxNmTGpzKhGgqCE8m1+HD2izS5UajXO2ew=
Received: by 10.140.186.18 with SMTP id j18mr441124rvf.1185425621169;
	Wed, 25 Jul 2007 21:53:41 -0700 (PDT)
Received: from bitdldt04 ( [122.162.167.88])
	by mx.google.com with ESMTPS id g31sm2407918rvb.2007.07.25.21.53.38
	(version=SSLv3 cipher=RC4-MD5); Wed, 25 Jul 2007 21:53:40 -0700 (PDT)
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
	<46A724CB.3030405@archonet.com>
	<000001c7cea8$39775d20$ac661760$@com>
	<46A7449A.4080500@archonet.com>
In-Reply-To: <46A7449A.4080500@archonet.com>
Subject: Re: Database synchronization
Date: Thu, 26 Jul 2007 10:20:39 +0530
Message-ID: <001e01c7cf40$85e7b540$91b71fc0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcfOuUZ5jMjSi1wuS5m/QUgrMTDbHAAhw3zg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/150
X-Sequence-Number: 28885

Thanks a lot for your suggestion. It will help me in taking the decision.

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com] 
Sent: Wednesday, July 25, 2007 6:10 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database synchronization

Jyoti Seth wrote:
> My master database contains prices of some products that vary after few
> hours and many other details that are also reqd in Db2 which doesn't
change
> that frequently. My slave database contains the details of queries sent by
> the customers.
> These queries are to find the price of the product and other details.
(Some
> part of the info reqd from Db1 varies frequently where some information is
a
> kind of master data that doesn't vary frequently).
> 
> So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
> for other details or there is some other better option

If you can live with a time-lag measured in seconds, then slony will 
work just fine. If you can't then you'll want dblink.

One other thing you might consider - if you attach "valid_from" time to 
each price then you can put them in place in advance, and make sure they 
are replicated before needed.

-- 
   Richard Huxton
   Archonet Ltd




From jyotiseth2001@gmail.com Tue Jul  31 08:02:57 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 7532C9FB91C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 05:02:57 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 54194-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 05:02:50 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wa-out-1112.google.com (wa-out-1112.google.com
 [209.85.146.177])
	by postgresql.org (Postfix) with ESMTP id AFB649FB61D
	for <pgsql-sql@postgresql.org>; Tue, 31 Jul 2007 05:02:53 -0300 (ADT)
Received: by wa-out-1112.google.com with SMTP id k40so2348043wah
	for <pgsql-sql@postgresql.org>; Tue, 31 Jul 2007 01:02:52 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=X6o48dC7o7iiYS5GFnU+TdH0U3o/CRdNwoaguNzop6X+KjgFxQnsGleZ0mGdC20bDGdcRDi/+NC3kyCpFyL+yk3sBUgiuXVxNpLTDuf/Wn7YZ2gE3+q4z/LqB3HVO7/EiovILNqnGaJcCat1Gx0vhzfoCdP68Ym41pF0TZyMRWM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=LkDupgU+kLVKaM0e7brLar5y6BQqA5DwBVlbn569TY7D+0TmcnGR2edYb0pmW+w2chuev99dZ/gC6AyiTfUZwonTGOFzPMQuziFnre2GOOpwun7sQP8m2N/f+Up3i70v2XBORBqk98D9JWsrQuwH88lEaZl5ymWldepO39nz3fQ=
Received: by 10.114.171.1 with SMTP id t1mr6425075wae.1185868972305;
	Tue, 31 Jul 2007 01:02:52 -0700 (PDT)
Received: by 10.114.234.2 with HTTP; Tue, 31 Jul 2007 01:02:52 -0700 (PDT)
Message-ID: <6c3fb5eb0707310102s73bf7516w8dd157c550e2cf7d@mail.gmail.com>
Date: Tue, 31 Jul 2007 13:32:52 +0530
From: "Jyoti Seth" <jyotiseth2001@gmail.com>
To: "Richard Huxton" <dev@archonet.com>
Subject: Re: Database synchronization
Cc: pgsql-sql@postgresql.org
In-Reply-To: <001e01c7cf40$85e7b540$91b71fc0$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_42518_15748223.1185868972271"
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com> <46A724CB.3030405@archonet.com>
	<000001c7cea8$39775d20$ac661760$@com> <46A7449A.4080500@archonet.com>
	<001e01c7cf40$85e7b540$91b71fc0$@com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/194
X-Sequence-Number: 28929

------=_Part_42518_15748223.1185868972271
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello,

I have one query related to slony-I. Can I replicate table data in different
schemas. Say if have table 'country' in database 'stock' and schema
'stocksch'. And I want to replicate the data in table 'country' of database
'smsdb' and schema 'smssch'. Here, the country table is present in two
different schemas. Can I replicate the country table data from schema
stocksch to schema smssch or I have to create a schema with the same name
for replication.

Thanks,
Jyoti Seth


On 7/26/07, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
>
> Thanks a lot for your suggestion. It will help me in taking the decision.
>
> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Wednesday, July 25, 2007 6:10 PM
> To: Jyoti Seth
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Database synchronization
>
> Jyoti Seth wrote:
> > My master database contains prices of some products that vary after few
> > hours and many other details that are also reqd in Db2 which doesn't
> change
> > that frequently. My slave database contains the details of queries sent
> by
> > the customers.
> > These queries are to find the price of the product and other details.
> (Some
> > part of the info reqd from Db1 varies frequently where some information
> is
> a
> > kind of master data that doesn't vary frequently).
> >
> > So should I use all the ways i.e #2 or #3 to get price info and #1
> (slony)
> > for other details or there is some other better option
>
> If you can live with a time-lag measured in seconds, then slony will
> work just fine. If you can't then you'll want dblink.
>
> One other thing you might consider - if you attach "valid_from" time to
> each price then you can put them in place in advance, and make sure they
> are replicated before needed.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
>

------=_Part_42518_15748223.1185868972271
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>Hello,</div>
<div>&nbsp;</div>
<div>I have one query related to slony-I. Can I replicate table data in different schemas. Say if have table &#39;country&#39; in database &#39;stock&#39; and schema &#39;stocksch&#39;. And&nbsp;I want to replicate the data in table &#39;country&#39; of database &#39;smsdb&#39; and schema &#39;smssch&#39;. Here, the country table is present in two different schemas. Can I replicate the country table data from schema stocksch to schema smssch or I have to create a schema with the same name for replication.
</div>
<div>&nbsp;</div>
<div>Thanks,</div>
<div>Jyoti Seth<br><br>&nbsp;</div>
<div><span class="gmail_quote">On 7/26/07, <b class="gmail_sendername">Jyoti Seth</b> &lt;<a href="mailto:jyotiseth2001@gmail.com">jyotiseth2001@gmail.com</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Thanks a lot for your suggestion. It will help me in taking the decision.<br><br>-----Original Message-----
<br>From: Richard Huxton [mailto:<a href="mailto:dev@archonet.com">dev@archonet.com</a>]<br>Sent: Wednesday, July 25, 2007 6:10 PM<br>To: Jyoti Seth<br>Cc: <a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org
</a><br>Subject: Re: [SQL] Database synchronization<br><br>Jyoti Seth wrote:<br>&gt; My master database contains prices of some products that vary after few<br>&gt; hours and many other details that are also reqd in Db2 which doesn&#39;t
<br>change<br>&gt; that frequently. My slave database contains the details of queries sent by<br>&gt; the customers.<br>&gt; These queries are to find the price of the product and other details.<br>(Some<br>&gt; part of the info reqd from Db1 varies frequently where some information is
<br>a<br>&gt; kind of master data that doesn&#39;t vary frequently).<br>&gt;<br>&gt; So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)<br>&gt; for other details or there is some other better option
<br><br>If you can live with a time-lag measured in seconds, then slony will<br>work just fine. If you can&#39;t then you&#39;ll want dblink.<br><br>One other thing you might consider - if you attach &quot;valid_from&quot; time to
<br>each price then you can put them in place in advance, and make sure they<br>are replicated before needed.<br><br>--<br>&nbsp;&nbsp;Richard Huxton<br>&nbsp;&nbsp;Archonet Ltd<br><br></blockquote></div><br>

------=_Part_42518_15748223.1185868972271--



From dev@archonet.com Tue Jul  31 08:22:15 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 555689FBA91
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 05:22:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 56225-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 05:22:08 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 5FDBA9FB5B7
	for <pgsql-sql@postgresql.org>; Tue, 31 Jul 2007 05:22:12 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 6C341144651;
	Tue, 31 Jul 2007 09:22:06 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 472D8274068;
	Tue, 31 Jul 2007 09:22:04 +0100 (BST)
Message-ID: <46AEF12C.2090107@archonet.com>
Date: Tue, 31 Jul 2007 09:22:04 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jyoti Seth <jyotiseth2001@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database synchronization
References: <20070724143737.GA25948@apartia.fr>	
	<004e01c7cea4$58046570$080d3050$@com>
	<46A724CB.3030405@archonet.com>	
	<000001c7cea8$39775d20$ac661760$@com>
	<46A7449A.4080500@archonet.com>	
	<001e01c7cf40$85e7b540$91b71fc0$@com>
	<6c3fb5eb0707310102s73bf7516w8dd157c550e2cf7d@mail.gmail.com>
In-Reply-To: <6c3fb5eb0707310102s73bf7516w8dd157c550e2cf7d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/195
X-Sequence-Number: 28930

Jyoti Seth wrote:
> Hello,
> 
> I have one query related to slony-I. Can I replicate table data in different
> schemas. Say if have table 'country' in database 'stock' and schema
> 'stocksch'. And I want to replicate the data in table 'country' of database
> 'smsdb' and schema 'smssch'. Here, the country table is present in two
> different schemas. Can I replicate the country table data from schema
> stocksch to schema smssch or I have to create a schema with the same name
> for replication.

I don't think slony lets you rename tables/schemas during transfer. I'm 
not sure many people want it, and it would be a complicated thing to do 
properly in the general case (you'll need to consider dependant objects 
- FKeys, views, functions etc).


-- 
   Richard Huxton
   Archonet Ltd



From ajs@crankycanuck.ca Tue Jul  31 13:36:30 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 2F0539FB660
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 10:36:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 05230-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Jul 2007 10:36:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id 707B49FB638
	for <pgsql-sql@postgresql.org>; Tue, 31 Jul 2007 10:36:26 -0300 (ADT)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1IFrth-0000Dt-75
	for pgsql-sql@postgresql.org; Tue, 31 Jul 2007 09:36:25 -0400
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id D99DC3FAF; Tue, 31 Jul 2007 09:36:19 -0400 (EDT)
Date: Tue, 31 Jul 2007 09:36:19 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Database synchronization
Message-ID: <20070731133619.GB8583@phlogiston.dyndns.org>
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
	<46A724CB.3030405@archonet.com>
	<000001c7cea8$39775d20$ac661760$@com>
	<46A7449A.4080500@archonet.com>
	<001e01c7cf40$85e7b540$91b71fc0$@com>
	<6c3fb5eb0707310102s73bf7516w8dd157c550e2cf7d@mail.gmail.com>
	<46AEF12C.2090107@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <46AEF12C.2090107@archonet.com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/197
X-Sequence-Number: 28932

On Tue, Jul 31, 2007 at 09:22:04AM +0100, Richard Huxton wrote:
> I don't think slony lets you rename tables/schemas during transfer. I'm 
> not sure many people want it, and it would be a complicated thing to do 
> properly in the general case (you'll need to consider dependant objects 
> - FKeys, views, functions etc).

It doesn't allow this, but you could cause it to happen anyway on the
replica using a trigger.  You have to use STORE TRIGGER to make this
work.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
		--George Orwell



From weigelt@metux.de Wed Aug  8 13:17:10 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id C10589FC059
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  8 Aug 2007 10:17:10 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 31340-02-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  8 Aug 2007 10:16:54 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from s15216962.onlinehome-server.info
	(s15216962.onlinehome-server.info [217.160.22.205])
	by postgresql.org (Postfix) with ESMTP id BDBE99FC052
	for <pgsql-sql@postgresql.org>; Wed,  8 Aug 2007 10:17:01 -0300 (ADT)
Received: (from uucp@localhost)
	by s15216962.onlinehome-server.info (8.13.3/8.13.3) with UUCP id
	l78DH82o020020
	for pgsql-sql@postgresql.org; Wed, 8 Aug 2007 15:17:08 +0200
Received: (from weigelt@localhost)
	by nibiru.metux.de (8.12.10/8.12.10) id l78DFqv4012718
	for pgsql-sql@postgresql.org; Wed, 8 Aug 2007 15:15:52 +0200
Date: Wed, 8 Aug 2007 15:15:52 +0200
From: Enrico Weigelt <weigelt@metux.de>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Database synchronization
Message-ID: <20070808131552.GD27936@nibiru.local>
Reply-To: weigelt@metux.de
References: <20070724143737.GA25948@apartia.fr>
	<004e01c7cea4$58046570$080d3050$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <004e01c7cea4$58046570$080d3050$@com>
User-Agent: Mutt/1.4.1i
X-Terror: bin laden, kill bush, Briefbombe, Massenvernichtung, KZ, 
X-Nazi: Weisse Rasse, Hitlers Wiederauferstehung, 42, 
X-Antichrist: weg mit schaeuble, ausrotten, heiliger krieg, al quaida, 
X-Killer: 23, endloesung, Weltuntergang, 
X-Doof: wer das liest ist doof
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/51
X-Sequence-Number: 28989

* Jyoti Seth <jyotiseth2001@gmail.com> wrote:

Hi,

if you can live with some seconds lag, you can use an separate
transfer process which looks at the source of your prices and 
and updates the second DB if some price changes. 

Depending on how often prices change, there're two good options:

a) add an timestamp field to the original price table, which is 
   maintained by an after-trigger. The transfer regularily 
   (ie. every few secs) fetches all those records which have 
   been changed since last poll (via the new timestamp field).

b) create an new table for an journal of the price changes.
   this journal is filled by rules on the original table and 
   contains exactly what's needed to reproduce the price changes
   in the other DB. The transfer process regularily fetches
   the journal and rolls it out in the second DB.
   
I've implemented the method b) in an realtime stock (FOREX) 
trading applications where trading automatically robots execute 
transactions on the customer's broker accounts on signals 
provided by an team of profession traders. (the client can 
subscribe to several trading signal channels and define in 
which quantities transactions should be performed from which
channels should be executed on their broker accounts and the
broking platform does all the time critical work). The robots
work entirely on separate databases (on separate hosts),
several transfer processes feed in robot commands and fetch
back results to the primary database. So time critical things
are separated to own servers. It works quite fine :)


cu
-- 
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service - http://www.metux.de/
---------------------------------------------------------------------
 Please visit the OpenSource QM Taskforce:
 	http://wiki.metux.de/public/OpenSource_QM_Taskforce
 Patches / Fixes for a lot dozens of packages in dozens of versions:
	http://patches.metux.de/
---------------------------------------------------------------------