Conditional row grained replication with DBMirror

From: Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: ssinger(at)navtechinc(dot)com
Cc: pgsql-admin(at)postgresql(dot)org, <pgsql-general(at)postgresql(dot)org>
Subject: Conditional row grained replication with DBMirror
Date: 2003-08-08 11:53:51
Message-ID: Pine.LNX.4.44.0308080942420.11089-100000@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

I made a modification on DBMirror.pl,
an addition in the slavedatabase.conf file,
and added another replication table "specialtables".

The goal was to have a way of controlling
when a row of table (if the table is present in specialtables),
will be mirrored to the remote slave.

Lets assume, we have a bank's central IT DEPT master DB
and some branches abroad.

Rows in some tables (e.g. personel, branches, statistics on transactions,
etc...)
have no reason to be replicated to all sites, but only
those sites that these rows refer to:

E.G.
we have
test=# \d personel
Table "public.personel"
Column | Type | Modifiers
---------------+------------------------+-----------
id | integer | not null
name | character varying(20) |
branchid | integer |
test=#

or
test=# \d branch
Table "public.personel"
Column | Type | Modifiers
---------------+------------------------+-----------
id | integer | not null
country | character varying(20) |
address | text |
test=#

And we require that insert, updates on table branch with id = 1
go only to remote site (the ip of) branch 1,
and that inserts,updates on personel with branchid = 2,
in the same fashion, go only to (the ip of) branch 2.

I retain the table specialtables
test=# \d specialtables
Table "public.specialtables"
Column | Type | Modifiers
---------------+------------------------+-----------
tblname | character varying(100) | not null
siteidkeyname | character varying(20) |
Indexes: specialtables_pkey primary key btree (tblname)

test=#

in which for each conditional table, the corresponding
column name (the value of siteidkeyname) is given
which indicates against which column in the PendingData.Data
column is to be examined for equality against the site id.

The site id for each slave is defined in an additional field
in slaveInfo struct in slavedatabase.conf.

Please tell me if i should send the patches
to DBMirror.pl, MirrorSetup.sql, and slavedatabase.conf.

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Randolph Jones 2003-08-08 12:19:27 Re: readline missing in linux
Previous Message Matt Clark 2003-08-08 11:47:20 Cost estimates consistently too high - does it matter?

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Browne 2003-08-08 12:25:20 Re: postmaster(s) have high load average
Previous Message Pavel Stehule 2003-08-08 11:00:58 Re: problem with timeofday() function in cvs PostgreSQL