[sqlalchemy] SQLA in a DB cluster
Hi all. I'm interested in learning proper application design patterns and best practices when using a SQLA powered web application (Pyramid based) in a DB cluster (PostgreSQL 9.1, psycopg2 driver). The primary requirement for the cluster is realtime backup, so the idea is to have two or more DB backends in master-slave (WAL replicated probably) setup. The second requirement is failover. If the master fails, the system should promote a slave into master, forget about former master and notify the admin. Similarly if a slave fails, it should be taken out of the equation, admin notified. Last but not least is loadbalancing of selects and/or parallel queries to take the advantage of more than one DB nodes being available. Each application request is basically treated like a single transaction in Pyramid and there are mixed cases. Some requests are read only, some are read-write, and the application treats every request as single transaction. There is no scenario where single request can result with multiple read queries that are isolated. Instead of reinventing the wheel I'm looking at using pgpool-II which can achieve all this but I'm not sure to what degree with SQLA. My primary concern is pgpool's inability to loadbalance queries within single explicit transaction block which is the case here (esp. with Pyramid and psycopg2 driver). I am also not sure whether to use pgpool's replication because it is statement level, ie. what happens if a node fails in transaction; or WAL replication. Any advices, pointers, tutorials, your own experience? Many thanks. -- .oO V Oo. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] SQLA in a DB cluster (repost)
Hi all. I'm posting this again because it seems my original post never reached the list? I'm interested in learning proper application design patterns and best practices when using a SQLA powered web application (Pyramid based) in a DB cluster (PostgreSQL 9.1, psycopg2 driver). The primary requirement for the cluster is realtime backup, so the idea is to have two or more DB backends in master-slave (WAL replicated probably) setup. The second requirement is failover. If the master fails, the system should promote a slave into master, forget about former master and notify the admin. Similarly if a slave fails, it should be taken out of the equation, admin notified. Last but not least is loadbalancing of selects and/or parallel queries to take the advantage of more than one DB nodes being available. Each application request is basically treated like a single transaction in Pyramid and there are mixed cases. Some requests are read only, some are read-write, and the application treats every request as single transaction. There is no scenario where single request can result with multiple read queries that are isolated. Instead of reinventing the wheel I'm looking at using pgpool-II which can achieve all this but I'm not sure to what degree with SQLA. My primary concern is pgpool's inability to loadbalance queries within single explicit transaction block which is the case here (esp. with Pyramid and psycopg2 driver). I am also not sure whether to use pgpool's replication because it is statement level, ie. what happens if a node fails in transaction; or WAL replication. Any advices, pointers, tutorials, your own experience? Many thanks. -- .oO V Oo. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
