Mailing List Archive

On Mon, 2016-09-12 at 22:57 -0400, Joseph W wrote:
> Hi all,
>
> I've used Davical successfully in my company for several years
> (version 1.1.1; haven't had a need to upgrade), but now I've been
> tasked with making sure all of our services are fully redundant with
> presences in both office locations.
>
> I have a bunch of services using my postgres backend, so I figured a
> master-master replication solution would be perfect as it would allow
> both sites to use the local services and still have changes
> replicated to the other system.  After some research, I've chosen
> Bucardo as the postgres master-master replication solution.
>
> Unfortunately, Bucardo seems to require that all tables that are
> replicated have unique primary keys, and there are tables in Davical
> which do not.   I've considered adding primary key columns
> (auto-incrementing sequences) to each table, but I'm afraid of what
> that would break in the current software as well as with upgrades.

Do you mean "must have a unique primary key which is a single column
and of some numeric type"?  From memory I think all tables should have
a unique primary key - just that in some cases it is more than one
column, and in some cases it is non-numeric.

However there should be no particular problem adding some kind of a
"bucardo_key" columns based on sequences where they are needed.


> Now that the background is out of the way, onto my questions:
>
> 1.  Has anyone actually created a redundant hot pair of Davical
> systems in any way, and if so how?

I've tended towards WAL shipping approaches, which seems to be the core
replication model in PostgreSQL, but at this point it only supports
read queries against a warm slave.

> 2.  Would my idea with creating the additional column actually be
> benign and work fine?

I would expect this to be benign. The column may end up appearing in
some data if there's a "SELECT * FROM ..." on one of those tables, but
all database access uses column names to refer to fields in the result
set, so that shouldn't affect any functionality.

> 3.  Does anyone have any suggestions outside of my questions in 1 and
> 2?

Given the low rate of writes vs. reads for calendar data, I'd think it
could also be reasonable to add a synchronous commit option into
DAViCal.  It's pretty clear where DAViCal is writing to the database,
and I think the functionality could be wrapped inside the database
library layer relatively easily.

Cheers,
Andrew.

>
> Thank you in advance,
> Joseph
>
>
> -------------------------------------------------------------------
> -----------
> _______________________________________________
> Davical-general mailing list
> Davical-general@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/davical-general

------------------------------------------------------------------------------
_______________________________________________
Davical-general mailing list
Davical-general@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/davical-general

On Tue, Sep 13, 2016 at 05:53:59PM +0100, Andrew McMillan wrote:
> On Mon, 2016-09-12 at 22:57 -0400, Joseph W wrote:
> > Hi all,
> >
> > I've used Davical successfully in my company for several years
> > (version 1.1.1; haven't had a need to upgrade), but now I've been
> > tasked with making sure all of our services are fully redundant with
> > presences in both office locations.
> >
> > I have a bunch of services using my postgres backend, so I figured a
> > master-master replication solution would be perfect as it would allow
> > both sites to use the local services and still have changes
> > replicated to the other system.????After some research, I've chosen
> > Bucardo as the postgres master-master replication solution.
> >
> > Unfortunately, Bucardo seems to require that all tables that are
> > replicated have unique primary keys, and there are tables in Davical
> > which do not.??????I've considered adding primary key columns
> > (auto-incrementing sequences) to each table, but I'm afraid of what
> > that would break in the current software as well as with upgrades.
>
> Do you mean "must have a unique primary key which is a single column
> and of some numeric type"? ??From memory I think all tables should have
> a unique primary key - just that in some cases it is more than one
> column, and in some cases it is non-numeric.

I don't believe it has to be numeric, but I do think it has to be a "unique constraint" which a primary key fulfills. I think any column, so long as it's always unique would count (though postgres would have to know it as a unique constraint). I think, though I haven't verified this, that bucardo could even allow multi-column unique constraints to work, though for these davical tables, I don't think I can just add that as there is nothing that requires entries in addressbook_address_adr, for instance, to be unique.

>
> However there should be no particular problem adding some kind of a
> "bucardo_key" columns based on sequences where they are needed.
>
>
> > Now that the background is out of the way, onto my questions:
> >
> > 1.????Has anyone actually created a redundant hot pair of Davical
> > systems in any way, and if so how?
>
> I've tended towards WAL shipping approaches, which seems to be the core
> replication model in PostgreSQL, but at this point it only supports
> read queries against a warm slave.
>
> > 2.????Would my idea with creating the additional column actually be
> > benign and work fine?
>
> I would expect this to be benign. The column may end up appearing in
> some data if there's a "SELECT * FROM ..." on one of those tables, but
> all database access uses column names to refer to fields in the result
> set, so that shouldn't affect any functionality.
>

It's great that it is probably benign for the running of Davical; I'll of course test it a bit to make sure things don't explode somehow.

But do you have any idea if it would cause problems with upgrades and the sql update scripts? So far, I haven't had to upgrade (though since i'm rolling out a new solution I'll be moving everything to the latest versions, just in case) but if I need to upgrade in the future, or if I get hit by a bus and a replacement has to maintain this, I don't want to be too far outside of the standard path.


> > 3.????Does anyone have any suggestions outside of my questions in 1 and
> > 2?
>
> Given the low rate of writes vs. reads for calendar data, I'd think it
> could also be reasonable to add a synchronous commit option into
> DAViCal. ??It's pretty clear where DAViCal is writing to the database,
> and I think the functionality could be wrapped inside the database
> library layer relatively easily.

That sounds like it would make sense, but I'm so far from being a developer that feels quite dangerous to me. If it were a feature in the official platform it sounds great, but making those alterations and living with a forked version isn't something I've ever done or feel comfortable with. Thank you for the suggestion though; if I were more skilled it might work.


Thanks again,
Joseph

>
> Cheers,
> Andrew.
>
> >
> > Thank you in advance,
> > Joseph
> >
> >
> > -------------------------------------------------------------------
> > -----------
> > _______________________________________________
> > Davical-general mailing list
> > Davical-general@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/davical-general

------------------------------------------------------------------------------
_______________________________________________
Davical-general mailing list
Davical-general@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/davical-general