X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9B957D1B196 for ; Mon, 24 May 2004 16:54:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 81277-08 for ; Mon, 24 May 2004 16:54:06 -0300 (ADT) Received: from mxc.pcnet.ro (unknown [82.137.14.5]) by svr1.postgresql.org (Postfix) with ESMTP id B70FED1B193 for ; Mon, 24 May 2004 16:54:02 -0300 (ADT) Received: from aldratech.com (aldratech.com.pcnet.ro [213.154.158.58] (may be forged)) by mxc.pcnet.ro (8.12.10/8.12.10) with ESMTP id i4OJliN5030254 for ; Mon, 24 May 2004 22:47:44 +0300 Received: from aldratech.com ([192.168.0.6]) by aldratech.com for with eXtremail; Mon, 24 May 2004 22:54:02 +3GMT Message-ID: <40B2531D.6050704@aldratech.com> Date: Mon, 24 May 2004 22:55:09 +0300 From: Radu-Adrian Popescu Reply-To: radu.popescu@aldratech.com Organization: Aldratech Ltd. User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.6) Gecko/20040510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: twinsen Cc: pgsql-admin@postgresql.org Subject: Re: Reliable database communication - Slightly OT References: <20040524190703.47097.qmail@web41405.mail.yahoo.com> In-Reply-To: <20040524190703.47097.qmail@web41405.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/252 X-Sequence-Number: 13568 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