From: Radu-Adrian Popescu <radu.popescu@aldratech.com>
To: twinsen <twins3n@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Reliable database communication - Slightly OT
Date: Mon, 24 May 2004 22:55:09 +0300
Message-ID: <40B2531D.6050704@aldratech.com> (raw)
In-Reply-To: <20040524190703.47097.qmail@web41405.mail.yahoo.com>
References: <20040524190703.47097.qmail@web41405.mail.yahoo.com>
twinsen wrote:
> Hi all
>
> I have an architecture 'problem' which I would like to
> bounce off you. We have five remote Interactive Voice
> Response sites interconnected via WAN links. I have
> two Postgresql databases replicated at one of the
> sites. I need to capture data (mostly stats) from the
> IVR's and store the data in one of the databases - or
> both in which case I could drop replication.
>
> What messaging methods could one use to ensure data is
> never lost if a link should go down? I need something
> that would detect failure and buffer or something like
> that. A simple connection to the database wont cut it
> - then again, maybe one of you has found a way.
>
Hmm, this is a good one. I think it all depends on hi HA you want to get.
If you're in a producer-receiver scenario, where your data comes in, a program
grabs it and sends it to the database, then you have to make a connection -
either to another program or to the database.
While not completely fail-safe but good enough I find the following, assuming
you can change the code the sends data to the database: continue to do so, while
providing for a safe failover.
I think this is quite a sensible solution and provides a fair amount of
insurance. You send the data to one database server where it gets replicated to
the second. Should the first server (the one you're using) go down, either
switch to the second (not sure it can be done if you're in a master/slave repl.
scheme), or even better start logging to the local media (hdd). If that fails
you're toast anyhow; if it gets filled up then you're not paying attention to
the daemon anyhow and you deserve it :). When the failover happens, send an
alert to the operator/administrator. You might want to use something like SQLite
instead of binary/text files for data storage, to ease the import into the
database; SQLite has no network connections, no server/client model, it's
basically SQL over binary files.
I personally think this is a good solution because:
- you don't have to invest much time in new APIs and technologies altogether, as
you simply have to detect failure and properly recover from it i.e. write data
to a file/embedded database and programming SQLite involves dealing with a C API
consisting of roughly 3-5 functions;
- the HDD you're on is much more reliable than any distributed whatever, since
it has exactly the same reliability rate as your program; besides HDD space is
cheap and RAID is nothing new any more;
- you de-compose the problem into smaller issues that are technically very
solvable in very sensible ways, instead of building / relying on frameworks
whose purpose is beyond (imho) effective failover.
> I am reading through the spread www.spread.org docs
> already, but maybe one of you has had experience with
> a situation like this?
>
> Kind Regards
> Craig
>
Love to hear your thoughts on the above,
Cheers,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: radu.popescu@aldratech.com, twins3n@yahoo.com
Subject: Re: Reliable database communication - Slightly OT
In-Reply-To: <40B2531D.6050704@aldratech.com>
References: <20040524190703.47097.qmail@web41405.mail.yahoo.com> <40B2531D.6050704@aldratech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox