Thread: Conditional row grained replication with DBMirror
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
Sure, send the patches to the patches list and we will get them applied. I am not sure if I can get them into 7.4. It depends if the dbmirror author can review them. --------------------------------------------------------------------------- Achilleus Mantzios wrote: > > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi, any news on applying the patches to dbmirror?? Thanx. On Sun, 10 Aug 2003, Bruce Momjian wrote: > > Sure, send the patches to the patches list and we will get them applied. > I am not sure if I can get them into 7.4. It depends if the dbmirror > author can review them. > > --------------------------------------------------------------------------- > > Achilleus Mantzios wrote: > > > > > > 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 > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > -- ================================================================== 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
This has been saved for the 7.5 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 --------------------------------------------------------------------------- Achilleus Mantzios wrote: > > Hi, > > any news on applying the patches to dbmirror?? > > Thanx. > > > On Sun, 10 Aug 2003, Bruce Momjian wrote: > > > > > Sure, send the patches to the patches list and we will get them applied. > > I am not sure if I can get them into 7.4. It depends if the dbmirror > > author can review them. > > > > --------------------------------------------------------------------------- > > > > Achilleus Mantzios wrote: > > > > > > > > > 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 > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that your > > > message can get through to the mailing list cleanly > > > > > > > > > -- > ================================================================== > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073