On 2007-08-20, Kai-Martin Knaak <kmk (AT) lilalaser (DOT) de> wrote:
I do not know, it depends on what your db/tables look like. The process
could go something like this:

for table X in both dbs do:
1: download some uniq id from each table, that is common to both
2: build a list of things in table X1 that are not in table X2
3: build a list of things in table X2 that are not in table X1

now you have a list of what you need to move around

4: move stuff needed from X1 to X2
5: move stuff needed from X2 to X1

6: write in scripting language of your choice.

marc

--
ms4720 (AT) sdf (DOT) lonestar.org
SDF Public Access UNIX System - http://sdf.lonestar.org

marc spitzer wrote:

Given the OPs requirements (non concurrent access, slow sync)
I'd use the following:

1. Have a cron job (at 01:00) do a data only sql table dump on each box.
2. have a cron job (at 01:30) get the dum from the other box (using wget)
3. have a cron job (at 02:00) run the foreign sql dump through psql

If you switch frequently you might have made changes on both boxes.
To faciliate merging don't clean the table before inserting.
Just make sure there is a globally unique key (e.g. part id) and ignore
the 'duplicate key' errors.

A more sohisticated approach would be to use triggers to create the
'dump' files which then would only include the changes.

happy hacking,
Uwe

Kai-Martin Knaak wrote:
I'm kind of a newb concerning PG myself, but not concerning databases in
general. As far as I understand, both Master-Slave replication and
Master-Master replication are not exactly what you're looking for (since
both database have objects that alter independantly of eachother.

Afaik know, this rules out both Slony and Pgpool (since one is a
master-slave replicator and the other is a shared-nothing cluster).

(Although I might be wrong about slony if it can provide replication on
a per object base, in that case, it might just do the trick).

The way I would do it, is have a nightly cron job standing by to export
all needed objects (data only) from the master database, transfer them
to the slave and import them. Ofcourse whether or not this is the
solution you're searching for depends entirely on the size of your
objects. If direct connectivity is possible, you can do it from 1 script
from your slave altogether.

- pg_dump -a -t<obj> -h<master> -f<file> <db>
(- psql -d<db> -h<slave> -c "delete from ...")
- psql -d<db> -h<slave> -f<file>

Not the prettiest of ways, not the fastest of ways, but I do believe
it's the easiest.

I hope this is of any use to you.

Regards,

-R-