What is the recommended python module for SQL database access?

[Chris Angelico]

On Tue, Feb 11, 2014 at 2:02 PM, Asaf Las <roegltd at gmail.com> wrote:
> On Tuesday, February 11, 2014 4:57:30 AM UTC+2, Walter Hurry wrote:
>> Chris Angelico wrote:
>> >
>> > And definitely don't go for a non-free option (MS-SQL, DB2, etc)
>> > unless you've looked into it really closely and you are absolutely
>> > thoroughly *sure* that you need that system (which probably means you
>> > need your app to integrate with someone else's, and that other app
>> > demands one particular database).
>> >
>>
>> I agree 100% with this. And speaking as an ex Oracle and DB2 DBA -
>> not to mention MS-SQL (spit), with which I occasionally had to dabble,
>> avoid them like the plague unless circumstances dictate.
>
> What is about clustering? Do we have such option for free alternatives?
>
> Thanks

PostgreSQL has replication in-built now, which will do most forms of
clustering. With some third-party software like Slony (also free), you
can do even more (including replicating between different PostgreSQL
versions, so you can upgrade progressively without any downtime; PG's
internal replication has tight restrictions on that). I've used PG's
streaming replication to fairly good effect. You do need some kind of
system to decide when to promote a slave to master, though - my boss
had this weird idea that each node had to be a perfect peer with no
external authority [1], which led to unsolvable problems, but if you
have an external system that declares which of several slaves should
be promoted, it's pretty easy to do. I could whip you up a
proof-of-concept in an hour, probably; just needs a heartbeat script
and some way of signalling them to fail over to the new master.

Clustering for performance, as opposed to reliability, is a bit
trickier. You can do read-only queries on slaves (so if you have a
many-readers-few-writers model, this can work nicely), but otherwise,
you probably need some third-party middleware. I haven't looked into
that side of things. Ultimately your biggest bottleneck is going to be
locking, which fundamentally has to be done in one place... or else
you have to deal with merge conflicts (the bane of true multi-master
replication).

So, it all depends on what you need to accomplish, and how much work
you're willing to do. Postgres offers a particular set of primitives
(including replication, promotion of a slave to master, etc), and lets
you trigger things from scripts (execute "pg_ctl promote" to make this
node become master). Advanced logic can be done by writing a Python
script that edits config files, runs programs, sends Unix signals,
whatever. There are pay-for Postgres support companies, too, if you
need that sort of thing.

tl;dr: Yeah, you can do that too. :)

ChrisA

[1] He had a weird issue with the concept of authority, actually. I
think his dislike of any form of government polluted his thinking so
he wouldn't accept even the IT sense of the word "authority". Never
mind that that's the best way to solve a lot of problems. But I
digress.