Hi.

Is there a way to synchronize specific tables across different databases?

My specific problem:
I use an accounting application at two sites (lx-office, a german fork of
sql-ledger). Although the accounting is different at both sites, the
master data for products and vendors should contain the same items. This
kind of database grows slowly as news products are entered via the
accounting application. Now I'd hate to enter every bit of master
information twice. So I am looking for some tool that can keep these
specific tables in unison.
Any hint?

---<(kaimartin)>---
--
Kai-Martin Knaak tel: +49-511-762-2895
Universität Hannover, Inst. für Quantenoptik fax: +49-511-762-2211
Welfengarten 1, 30167 Hannover http://www.iqo.uni-hannover.de
GPG key: http://pgp.mit.edu:11371/pks/lookup?...aak+kmk&op=get

On 2007-08-17, Kai-Martin Knaak <kmk (AT) lilalaser (DOT) de> wrote:
Its low tech but how about a script and a cronjob?

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

Kai-Martin Knaak schrieb:
Not sure what you mean by: specific tables across different but similar
databases

But pgpool might be what you're looking for
http://pgfoundry.org/projects/pgpool/

Gruß Stefan

Kai-Martin Knaak <kmk (AT) lilalaser (DOT) de> wrote:
There is no out-of-the-box way for this.

Are the tables modified in one database and should be "replicated" to the
other database? If yes, what delay is acceptable?

Or are the data generated somewhere else and then imported into the
databases?

Apart from the tools already suggested, there is also dblink in the
contribs with which you can access one database from the other.

You can consider triggers that use dblink to propagate the information.

Yours,
Laurenz Albe

On Sat, 18 Aug 2007 13:27:32 +0200, Stefan Braumeister wrote:

The databases are on different computers which both run the same
application (lx-office, an open source enterprise resource planning
tool). So they are similar in that they contain identically defined
tables. But the data contained in the tables is different. Now, I'd like
to synchronize some tables (vendors, products, prices) but leave the rest
alone (purchases, orders, invoices ...). I hope, this clarified my
question.


Thanks. I'll look into that.

---<(kaimartin)>---
--
Kai-Martin Knaak tel: +49-511-762-2895
Universität Hannover, Inst. für Quantenoptik fax: +49-511-762-2211
Welfengarten 1, 30167 Hannover http://www.iqo.uni-hannover.de
GPG key: http://pgp.mit.edu:11371/pks/lookup?...aak+kmk&op=get

On Mon, 20 Aug 2007 10:26:10 +0000, Laurenz Albe wrote:

Whatever. Currently, I am the only user on both systems. So there is no
danger of race conditions. Having the tables synchronized once a week
would be fine for me.


There is a firewall between the systems. But I might ask the network
admin to poke hole into the wall...


Being a newbie at postgres administration my next question would ask for
information how to "propagate the information"...

At the moment, I consider a more hands on route: Dump the tables on both
databases do a diff, decide manually how to deal with conflicting lines
and rebuild the tables on the databases.

---<(kaimartin)>---
--
Kai-Martin Knaak tel: +49-511-762-2895
Universität Hannover, Inst. für Quantenoptik fax: +49-511-762-2211
Welfengarten 1, 30167 Hannover http://www.iqo.uni-hannover.de
GPG key: http://pgp.mit.edu:11371/pks/lookup?...aak+kmk&op=get

On Fri, 17 Aug 2007 22:07:45 +0000, marc spitzer wrote:

Err, yes, but how would such a script look like?
Until now, I simply used databaes as blackboxes. The most I daring task I
did, was to edit entries in tables with pgadmin3.

---<(kaimartin)>---
--
Kai-Martin Knaak
http://lilalaser.de/blog

Kai-Martin Knaak <kmk (AT) lilalaser (DOT) de> wrote:
That's good, as it will allow simpler solutions.

My suggestion is that you regularly export the table from the master system,
transfer the data to the slave system and import it there.

To export table "schema.table", you can use pg_dump similar to this:

pg_dump --data-only --file=exportfile --table=schema.table -U dbuser masterdb

This will generate a text file "exportfile" that contains SQL statements to
fill the table.

Transfer this to the slave, empty the table there (TRUNCATE schema.table)
and load the export file by feeding it to psql similar to this:

psql -U dbuser -d slavedb -f exportfile

You can use operating system tools to automate this process if you want
(cron, rsh, ...).

Since you don't need that strict synchronization, this is probably too
complicated.

But I'll explain a litte what I meant to satisfy your curiosity.

You could create a trigger, that is a user written function that runs
on the database server whenever certain changes occur in a table.
So, for example, whenever a record in the table on the master database
is updated, the trigger could connect to the second database and update
the corresponding record there.
This would keep the databases synchronized.

That seems too much work unless these changes are very infrequent.

Yours,
Laurenz Albe

On Tue, 21 Aug 2007 07:17:00 +0000, Laurenz Albe wrote:


Ok. I'll try that route. Thanks for helping me out.

--<(kaimartin)>---
--
Kai-Martin Knaak tel: +49-511-762-2895
Universität Hannover, Inst. für Quantenoptik fax: +49-511-762-2211
Welfengarten 1, 30167 Hannover http://www.iqo.uni-hannover.de
GPG key: http://pgp.mit.edu:11371/pks/lookup?...aak+kmk&op=get

On Tue, 21 Aug 2007 07:17:00 +0000, Laurenz Albe wrote:


For the archive:
I had to split schema and table to two options.
pg_dump --data-only --file=exportfile --schema=schema --table=table -U [...]

---<(kaimartin)>---
--
Kai-Martin Knaak tel: +49-511-762-2895
Universität Hannover, Inst. für Quantenoptik fax: +49-511-762-2211
Welfengarten 1, 30167 Hannover http://www.iqo.uni-hannover.de
GPG key: http://pgp.mit.edu:11371/pks/lookup?...aak+kmk&op=get