Thread: Sync 2 tables in 2 databases

Hi,
I have 2 postgres databases with similar structure. I want to keep some tables in sync in these 2 databases(They can be
syncedjust once a day). Is there a way to archive this using function ? 
Something like....

  Select syncTable('foo')

where syncTable is a function that compares table 'foo' in db1 with table 'foo' in db2 and make
changes(update/insert/delete)to 'foo' in db1 

Thanks
Sharmila

On Wed, Mar 11, 2009 at 1:20 PM, SHARMILA JOTHIRAJAH
<sharmi_jo@yahoo.com> wrote:
>
> Hi,
> I have 2 postgres databases with similar structure. I want to keep some tables in sync in these 2 databases(They can
besynced just once a day). Is there a way to archive this using function ? 
> Something like....
>
>  Select syncTable('foo')
>
> where syncTable is a function that compares table 'foo' in db1 with table 'foo' in db2 and make
changes(update/insert/delete)to 'foo' in db1 

You can either truncate it on the destination db every so often, then
dump / restore the data back into it, drop it and restore it, write a
simple replication script that looks for missing  / updated rows, or
my suggestion, set up replication with slony and be done with it.  Of
course, you don't mention if you need one or two way synchronization,
which makes a big difference in how you choose to do things.

--- On Wed, 3/11/09, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> >
> > Hi,
> > I have 2 postgres databases with similar structure. I
> want to keep some tables in sync in these 2 databases(They
> can be synced just once a day). Is there a way to archive
> this using function ?
> > Something like....
> >
> >  Select syncTable('foo')
> >
> > where syncTable is a function that compares table
> 'foo' in db1 with table 'foo' in db2 and make
> changes(update/insert/delete) to 'foo' in db1
>
> You can either truncate it on the destination db every so
> often, then
> dump / restore the data back into it, drop it and restore
> it, write a
> simple replication script that looks for missing  /
> updated rows, or
> my suggestion, set up replication with slony and be done
> with it.  Of
> course, you don't mention if you need one or two way
> synchronization,
> which makes a big difference in how you choose to do
> things.
>

It is just 1 way synchronization... replication with slony sounds pretty good... ill try that out
Thanks

> > Hi,
> > I have 2 postgres databases with similar structure. I
> want to keep some tables in sync in these 2 databases(They
> can be synced just once a day). Is there a way to archive
> this using function ?
> > Something like....
> >
> >  Select syncTable('foo')
> >
> > where syncTable is a function that compares table
> 'foo' in db1 with table 'foo' in db2 and make
> changes(update/insert/delete) to 'foo' in db1
>
> You can either truncate it on the destination db every so
> often, then
> dump / restore the data back into it, drop it and restore
> it, write a
> simple replication script that looks for missing  /
> updated rows, or
> my suggestion, set up replication with slony and be done
> with it.  Of
> course, you don't mention if you need one or two way
> synchronization,
> which makes a big difference in how you choose to do
> things.
>

Also, Is there a way to archive this using dbi_link? In Oracle I do this using db_link..is it possible to get this done
usingpostgres' dbi_link? 

On Wed, Mar 11, 2009 at 1:29 PM, SHARMILA JOTHIRAJAH
<sharmi_jo@yahoo.com> wrote:
>
>
>> > Hi,
>> > I have 2 postgres databases with similar structure. I
>> want to keep some tables in sync in these 2 databases(They
>> can be synced just once a day). Is there a way to archive
>> this using function ?
>> > Something like....
>> >
>> >  Select syncTable('foo')
>> >
>> > where syncTable is a function that compares table
>> 'foo' in db1 with table 'foo' in db2 and make
>> changes(update/insert/delete) to 'foo' in db1
>>
>> You can either truncate it on the destination db every so
>> often, then
>> dump / restore the data back into it, drop it and restore
>> it, write a
>> simple replication script that looks for missing  /
>> updated rows, or
>> my suggestion, set up replication with slony and be done
>> with it.  Of
>> course, you don't mention if you need one or two way
>> synchronization,
>> which makes a big difference in how you choose to do
>> things.
>>
>
> Also, Is there a way to archive this using dbi_link? In Oracle I do this using db_link..is it possible to get this
doneusing postgres' dbi_link? 

there's a package called dblink for linux.  Not sure it could automate
things, but if you're using dbi_link in oracle, dblink in pgsql can
accomplish the same basic thing.

On Wed, Mar 11, 2009 at 12:26:35PM -0700, SHARMILA JOTHIRAJAH wrote:
> It is just 1 way synchronization... replication with slony sounds pretty good... ill try that out
> Thanks

There are options other than Slony, each with their pros and cons. Some
that come to mind include Bucardo[1], Londiste[2], and Golconde[3].

- Josh

[1] http://bucardo.org/
[2] http://pgfoundry.org/projects/skytools/
[3] http://code.google.com/p/golconde/