472,371 Members | 1,668 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
2 1793

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


--
Bruce Momjian | http://candle.pha.pa.us
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2

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 ma*******@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Achilleus Mantzios | last post by:
Hi, first off, i broadcast my case to as many people as possible, maybe off topic for many, but maybe as well of interest for some. Also i speak sort of DBMirror terminology ( a great and simple...
9
by: Jan Wieck | last post by:
Dear community, for some reason the post I sent yesterday night still did not show up on the mailing lists. I have set up some links on the developers side under...
10
by: Anthony Best | last post by:
I'm working on an idea that uses sequences. I'm going to create a table like this: id serial, sequence int, keyword varchar(32), text text for every keyword there will be a uniq sequence...
1
by: Andreas | last post by:
Hello list, did someone try successfully to use the replication extension that was released to the community this summer ? Maybe someone could share his/her impressions and produce a tiny...
3
by: Steven job | last post by:
What is the best replication option out there today for postgresql. Have been trying to get rserv working and that hasn't been the easiest. Would like to run 7.4.1. Any suggestions? -Steve
2
by: Vidyasagara Guntaka | last post by:
Hi, I'm new to this list. I'm Sagar. Our company is seriously considering using Postgresql for our storage virtualization solution. To be able to use the database, replication is must for us....
2
by: David Parker | last post by:
Hi. I'm new to postgresql in general, and have been tasked with coming up with a replication solution for our postgresql based application. Assuming we don't bite off trying to roll our own...
1
by: Jon Brisbin | last post by:
We're trying to figure out how we can implement a reasonably simple cluster of postgres servers on a private network at our store locations. The idea is to have a group of 2-6 PCs each be able to...
0
by: ntech | last post by:
Hello everyone, I am really in a great problem. I have been using dbmirror as the replication method for postgresql. But now the requirement is it has to be both way (i.e. from the primary server to...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.