We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as datastore, not database;). I know there are multiple possibilities how to achieve redundancy in pgsql but I think none of them is reliable as mysql cluster. So
1) Is there any good conceptual design that could offer reliable pgsql cluster – reliability, data corruption prevention, high availability, no SPOF, load balancing and access to DB through one (virtual?) IP address?
2) Do you know some companies that are able to design and deploy the cluster?
Best Answer
This is pretty broad. Let's answer (2) first:
http://www.postgresql.org/support/professional_support/
Now, on to (1):
The question is not "if" but "which".
You are describing a heartbeat/failover cluster. There aren't really fully canned solutions for this, you'll need your own heartbeat and STONITH using appropriate non-PostgreSQL-specific tools.
You can use PostgreSQL 9.1 streaming replication and log shipping for the replication. The replication manager
repmgrcan help automate much of the management.Other replication options are described on the PostgreSQL wiki. All will require added heartbeat and STONITH for a fully transparent cluster with virtual IP, etc.
You might also want to look at
pgbouncer. It's a useful tool not only for connection pooling, but also for failover and load balancing.See the PostgreSQL manual on high availability, load balancing, and replication for more information.