Thread: Transferring Data between databases
Hi, I want to do the following: When data gets added to a table in one database a subset of that data must be sent to another database, perhaps on another computer. The primary database will not always be connected to the secondary one and must perhaps (not that urgent now) send the data (old and new) to the secondary database as soon as they are connected. Can I write triggers or something that will do this inside the database? I know Sloni-I and other software does replication but that is not really what I need (I think). I read up on some of these software but can't seem to find what I am looking for. If someone can point me in the right direction it will be appreciated. Thanks -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
> I want to do the following: When data gets added to a table in one > database a subset of that data must be sent to another database, > perhaps on another computer. The primary database will not always be > connected to the secondary one and must perhaps (not that urgent now) > send the data (old and new) to the secondary database as soon as they > are connected. > Can I write triggers or something that will do this inside the database? If connectivity between the two servers is poor, the triggers may fail and your actual transactions may be rolled back, right? Are you looking to replicate a large number of tables or just a few tables? You could have a trigger populate the data to files (that way the original transactions do not get affected) and then schedule a job to process the files and move data to the other server. If the connectivity is good and only a few tables need to be replicated, you could try triggers with a database link. I am a PostgreSQL novice. So my suggestions may not be the best. Regards, Jayadevan From: Carel Combrink <s25291930@tuks.co.za> To: pgsql-novice@postgresql.org Date: 21/05/2010 13:20 Subject: [NOVICE] Transferring Data between databases Sent by: pgsql-novice-owner@postgresql.org Hi, I want to do the following: When data gets added to a table in one database a subset of that data must be sent to another database, perhaps on another computer. The primary database will not always be connected to the secondary one and must perhaps (not that urgent now) send the data (old and new) to the secondary database as soon as they are connected. Can I write triggers or something that will do this inside the database? I know Sloni-I and other software does replication but that is not really what I need (I think). I read up on some of these software but can't seem to find what I am looking for. If someone can point me in the right direction it will be appreciated. Thanks -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar. -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
hi carel, you could use a trigger to copy the data needed into separate tables. this tables could be used to sync the data. regards andreas Jayadevan M wrote: >> I want to do the following: When data gets added to a table in one >> database a subset of that data must be sent to another database, >> perhaps on another computer. The primary database will not always be >> connected to the secondary one and must perhaps (not that urgent now) >> send the data (old and new) to the secondary database as soon as they >> are connected. >> > > >> Can I write triggers or something that will do this inside the database? >> > > If connectivity between the two servers is poor, the triggers may fail and > your actual transactions may be rolled back, right? Are you looking to > replicate a large number of tables or just a few tables? You could have a > trigger populate the data to files (that way the original transactions do > not get affected) and then schedule a job to process the files and move > data to the other server. If the connectivity is good and only a few > tables need to be replicated, you could try triggers with a database link. > > I am a PostgreSQL novice. So my suggestions may not be the best. > Regards, > Jayadevan > > > > From: Carel Combrink <s25291930@tuks.co.za> > To: pgsql-novice@postgresql.org > Date: 21/05/2010 13:20 > Subject: [NOVICE] Transferring Data between databases > Sent by: pgsql-novice-owner@postgresql.org > > > > Hi, > > I want to do the following: When data gets added to a table in one > database a subset of that data must be sent to another database, > perhaps on another computer. The primary database will not always be > connected to the secondary one and must perhaps (not that urgent now) > send the data (old and new) to the secondary database as soon as they > are connected. > > Can I write triggers or something that will do this inside the database? > > I know Sloni-I and other software does replication but that is not > really what I need (I think). I read up on some of these software but > can't seem to find what I am looking for. > > If someone can point me in the right direction it will be appreciated. > > Thanks >
On 2010-05-21, Carel Combrink <s25291930@tuks.co.za> wrote: > Hi, > > I want to do the following: When data gets added to a table in one > database a subset of that data must be sent to another database, > perhaps on another computer. The primary database will not always be > connected to the secondary one and must perhaps (not that urgent now) > send the data (old and new) to the secondary database as soon as they > are connected. > > Can I write triggers or something that will do this inside the database? > > I know Sloni-I and other software does replication but that is not > really what I need (I think). I read up on some of these software but > can't seem to find what I am looking for. > > If someone can point me in the right direction it will be appreciated. Have a look at slony1, I'm not saying use it unless you find it suitable but, it works on a similar principle and will atleast give you some ideas.
On Sat, May 22, 2010 at 4:00 AM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2010-05-21, Carel Combrink <s25291930@tuks.co.za> wrote: >> Hi, >> >> I want to do the following: When data gets added to a table in one >> database a subset of that data must be sent to another database, >> perhaps on another computer. The primary database will not always be >> connected to the secondary one and must perhaps (not that urgent now) >> send the data (old and new) to the secondary database as soon as they >> are connected. >> >> Can I write triggers or something that will do this inside the database? >> >> I know Sloni-I and other software does replication but that is not >> really what I need (I think). I read up on some of these software but >> can't seem to find what I am looking for. >> >> If someone can point me in the right direction it will be appreciated. > > Have a look at slony1, I'm not saying use it unless you find it > suitable but, it works on a similar principle and will atleast give > you some ideas. When you say you want to copy a subset of your data to another database, I wonder what you mean exactly. Slony, as well as alternative trigger-based replication options such as Bucardo and Londiste, should all allow you to copy data from only some of the database tables. If you're interested in transforming the data during the copy process, or replicating only certain rows of the database, I suspect Bucardo would work well. I don't know of a way Slony could do that, and don't know about Londiste. Bucardo is designed to support flaky networks. -- Joshua Tolley / eggyknap End Point Corporation
Carel Combrink wrote:
> Hi,
>
> I want to do the following: When data gets added to a table in one
> database a subset of that data must be sent to another database,
> perhaps on another computer. The primary database will not always be
> connected to the secondary one and must perhaps (not that urgent now)
> send the data (old and new) to the secondary database as soon as they
> are connected.
>
> Can I write triggers or something that will do this inside the database?
>
> I know Sloni-I and other software does replication but that is not
> really what I need (I think). I read up on some of these software but
> can't seem to find what I am looking for.
>
> If someone can point me in the right direction it will be appreciated.
>
> Thanks
>
As others have mentioned before, Slony and Bucardo replication packages
are designed to do just that, but there is an even more appealing
option. Postgres 9.0 will have what is in Oracle world known as "logical
standby". In other words, it will be possible to transport WAL archives
over the network, apply them to a replica DB, possibly on another
machine, and query the replica. The overhead will be very tiny compared
to the trigger-based packages like Slony and Bucardo because the primary
database will only ship the WAL archive to the replica ("logical
standby") which will do the rest of the magic. Of course, it all depend
on when do you need your DB, will the pg_migrate package be ready in
time, how big is your DB and many other variables.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com