Thread: Some thoughts on replication
Hi folks,
Maybe this belongs to the developers mailing list, but I'm not subscribed to
that list, so I post it here.
I've read through todo list on the website, especially the planned
replication feature. It seems to me that implementing replication into
postgresql is a rather hard and longterm task.
I want to show two possible alternatives, used in a real world application (a
billing system based on Informix) I was involved in a few years ago.
In our case we didn't need (or want) to replicate the whole database, but
only a few tables. The first thing was to use synonym tables. In Infomix one
can just type something like
create synonym mysynonym for table mytable:somedb@somehost;
where the somedb and somehost values are optional. It was then possible to
work on the synonym as if it where a normal table.
Later, we also did table replication based on triggers, a daemon and a
configuration table. The tables which had to be replicated where entered in
the config table, together with their destination. Then a little script was
used to setup the apprpriate insert, update and delete triggers for those
tables. When one of the triggers fired, the daemon contacted its friend on
the other host and did the same action on the remote database. I think there
was also a timestamp in the config table which had to be updated. I don't
know more details anymore, but that was roughly the concept.
Maybe one of those two (or both) is easier and faster to realise than
database replication.
Any comments?
Bye...
Dirk
Have you looked at the replication code that is in contrib?
On Thu, 25 Jan 2001, Dirk Heinrichs wrote:
> Hi folks,
>
> Maybe this belongs to the developers mailing list, but I'm not subscribed to
> that list, so I post it here.
>
> I've read through todo list on the website, especially the planned
> replication feature. It seems to me that implementing replication into
> postgresql is a rather hard and longterm task.
>
> I want to show two possible alternatives, used in a real world application (a
> billing system based on Informix) I was involved in a few years ago.
>
> In our case we didn't need (or want) to replicate the whole database, but
> only a few tables. The first thing was to use synonym tables. In Infomix one
> can just type something like
>
> create synonym mysynonym for table mytable:somedb@somehost;
>
> where the somedb and somehost values are optional. It was then possible to
> work on the synonym as if it where a normal table.
>
> Later, we also did table replication based on triggers, a daemon and a
> configuration table. The tables which had to be replicated where entered in
> the config table, together with their destination. Then a little script was
> used to setup the apprpriate insert, update and delete triggers for those
> tables. When one of the triggers fired, the daemon contacted its friend on
> the other host and did the same action on the remote database. I think there
> was also a timestamp in the config table which had to be updated. I don't
> know more details anymore, but that was roughly the concept.
>
> Maybe one of those two (or both) is easier and faster to realise than
> database replication.
>
> Any comments?
>
> Bye...
>
> Dirk
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> > Have you looked at the replication code that is in contrib? > Yes, your description sounds just like it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Am Donnerstag, 25. Januar 2001 19:07 schrieb The Hermit Hacker:
> Have you looked at the replication code that is in contrib?
Oops, not yet. In 7.0.3 or current?
Bye...
Dirk
Am Samstag, 27. Januar 2001 00:20 schrieb Bruce Momjian:
> > Have you looked at the replication code that is in contrib?
>
> Yes, your description sounds just like it.
Hmm, which of the two? The trigger mechanism?
In the meantime, a third solution came to my mind:
In case somebody has to take a database with him on a laptop, it is hard to
do replication and avoid conflicts. I once saw an insurance application,
based on FoxPro, where the insurance agents could select the data of the
customers he wanted to visit and and create a so called child database, which
had exactly he same tables as the "mother" and exactly those rows with data
related to the selected customers. Those rows where the marked as readonly in
the mother database, so that only the data in the child db could be changed.
Later, the changes in th child db was reintegrated into the mother db and the
child was deleted.
But I don't know if this was a feature of the application or of the database
system itself.
Bye...
Dirk
> Am Samstag, 27. Januar 2001 00:20 schrieb Bruce Momjian: > > > Have you looked at the replication code that is in contrib? > > > > Yes, your description sounds just like it. > > Hmm, which of the two? The trigger mechanism? The triggers. > > In the meantime, a third solution came to my mind: > > In case somebody has to take a database with him on a laptop, it is hard to > do replication and avoid conflicts. I once saw an insurance application, > based on FoxPro, where the insurance agents could select the data of the > customers he wanted to visit and and create a so called child database, which > had exactly he same tables as the "mother" and exactly those rows with data > related to the selected customers. Those rows where the marked as readonly in > the mother database, so that only the data in the child db could be changed. > Later, the changes in th child db was reintegrated into the mother db and the > child was deleted. Yes, that is a data partitioning feature of replication. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026