pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase Synchronization
17+ messages / 5 participants
[nested] [flat]
* Database Synchronization
@ 2007-07-18 07:14 Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jyoti Seth @ 2007-07-18 07:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-18 07:28 ` Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2007-07-18 07:28 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
@ 2007-07-18 14:57 ` Chris Browne <cbbrowne@acm.org>
2007-07-18 15:43 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-23 09:25 ` Re: Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Chris Browne @ 2007-07-18 14:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
@ 2007-07-18 15:43 ` Richard Huxton <dev@archonet.com>
2007-07-19 04:31 ` Re: Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2007-07-18 15:43 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
2007-07-18 15:43 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
@ 2007-07-19 04:31 ` Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jyoti Seth @ 2007-07-19 04:31 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
@ 2007-07-23 09:25 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-23 18:13 ` Re: Database Synchronization Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 17+ messages in thread
From: Jyoti Seth @ 2007-07-23 09:25 UTC (permalink / raw)
To: 'Chris Browne' <cbbrowne@acm.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
2007-07-23 09:25 ` Re: Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-23 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-24 07:45 ` Re: Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Andrew Sullivan @ 2007-07-23 18:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database Synchronization
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Re: Database Synchronization Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Re: Database Synchronization Chris Browne <cbbrowne@acm.org>
2007-07-23 09:25 ` Re: Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-23 18:13 ` Re: Database Synchronization Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-07-24 07:45 ` Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jyoti Seth @ 2007-07-24 07:45 UTC (permalink / raw)
To: 'Andrew Sullivan' <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Database synchronization
@ 2007-07-25 10:12 Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-08-08 13:15 ` Re: Database synchronization Enrico Weigelt <weigelt@metux.de>
0 siblings, 2 replies; 17+ messages in thread
From: Jyoti Seth @ 2007-07-25 10:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-25 10:24 ` Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2007-07-25 10:24 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
@ 2007-07-25 10:40 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jyoti Seth @ 2007-07-25 10:40 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-25 12:39 ` Richard Huxton <dev@archonet.com>
2007-07-26 04:50 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2007-07-25 12:39 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
@ 2007-07-26 04:50 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:02 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jyoti Seth @ 2007-07-26 04:50 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-26 04:50 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-31 08:02 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:22 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jyoti Seth @ 2007-07-31 08:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-26 04:50 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:02 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-07-31 08:22 ` Richard Huxton <dev@archonet.com>
2007-07-31 13:36 ` Re: Database synchronization Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2007-07-31 08:22 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-26 04:50 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:02 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:22 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
@ 2007-07-31 13:36 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 17+ messages in thread
From: Andrew Sullivan @ 2007-07-31 13:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Database synchronization
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
@ 2007-08-08 13:15 ` Enrico Weigelt <weigelt@metux.de>
1 sibling, 0 replies; 17+ messages in thread
From: Enrico Weigelt @ 2007-08-08 13:15 UTC (permalink / raw)
To: pgsql-sql
* 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/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2007-08-08 13:15 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-18 07:14 Database Synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-18 07:28 ` Richard Huxton <dev@archonet.com>
2007-07-18 14:57 ` Chris Browne <cbbrowne@acm.org>
2007-07-18 15:43 ` Richard Huxton <dev@archonet.com>
2007-07-19 04:31 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-23 09:25 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-23 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-24 07:45 ` Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:12 Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 10:24 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-25 10:40 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-25 12:39 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-26 04:50 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:02 ` Re: Database synchronization Jyoti Seth <jyotiseth2001@gmail.com>
2007-07-31 08:22 ` Re: Database synchronization Richard Huxton <dev@archonet.com>
2007-07-31 13:36 ` Re: Database synchronization Andrew Sullivan <ajs@crankycanuck.ca>
2007-08-08 13:15 ` Re: Database synchronization Enrico Weigelt <weigelt@metux.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox