Thread: Synchronize tables question....

Hi,

I have a table with four text fields on two different machines
that contain information about mail list passwords/software keys
and similar information. Neither table has any keys or constraints.

I do have dbi installed on each machine...

Is there an elegant way I can merge/update the two tables so that
they will contain the same information ( with no duplicates or
omissions)?

Thanks,

Jerry

Don't forget to CC: the list

Jerry LeVan wrote:
>
>>> Is there an elegant way I can merge/update the two tables so that
>>> they will contain the same information ( with no duplicates or
>>> omissions)?
>>
>> It sounds like you'll want some form of replication, but whether
>> single-master or multi-master no-one can say without more information.

> I meant to say I had dblink installed :)
>
> The following sequence of steps seems to work ok, the tables are very
> small...

> insert into tmpRegistrations select *
>    from dblink('select * from registrations ')
>    as (software text,
>        id text,
>        value text,
>        location text
>       )
>
> select dblink_disconnect()
>
> create temp table newregistrations as
>    (select * from registrations union select * from tmpregistrations )
>
> truncate table registrations
>
> insert  into registrations  select * from newregistrations

A couple of points:
1. What if a registration is updated?
2. What happens if you get a new registration between CREATE TEMP TABLE
and TRUNCATE?

--
   Richard Huxton
   Archonet Ltd

On Feb 19, 2007, at 9:05 AM, Richard Huxton wrote:

> Don't forget to CC: the list
>
> Jerry LeVan wrote:
>>>> Is there an elegant way I can merge/update the two tables so that
>>>> they will contain the same information ( with no duplicates or
>>>> omissions)?
>>>
>>> It sounds like you'll want some form of replication, but whether
>>> single-master or multi-master no-one can say without more
>>> information.
>
>> I meant to say I had dblink installed :)
>> The following sequence of steps seems to work ok, the tables are
>> very small...
>
>> insert into tmpRegistrations select *
>>    from dblink('select * from registrations ')
>>    as (software text,
>>        id text,
>>        value text,
>>        location text
>>       )
>> select dblink_disconnect()
>> create temp table newregistrations as
>>    (select * from registrations union select * from
>> tmpregistrations )
>> truncate table registrations
>> insert  into registrations  select * from newregistrations
>
> A couple of points:
> 1. What if a registration is updated?
> 2. What happens if you get a new registration between CREATE TEMP
> TABLE and TRUNCATE?
>
> --
>   Richard Huxton
>   Archonet Ltd

While a "generic" solution would be nice, I am the only one using the
DB :)
(so point 2 is unlikely).

Point 1 will require a bit of thought..., currently I would have to do
a manual scan of the (small) table after the merge.

Thanks

Jerry

Jerry LeVan wrote:
>
>
> Is there an elegant way I can merge/update the two tables so that
> they will contain the same information ( with no duplicates or
> omissions)?
>

Well comparing algorithm is quite complex and depend on data.
You can use third patry software like
http://www.sqlmanager.net/en/products/postgresql/datacomparer
--
View this message in context: http://www.nabble.com/Synchronize-tables-question....-tf3242977.html#a9055565
Sent from the PostgreSQL - general mailing list archive at Nabble.com.