Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
So you're looking for a synchronous solution for postgresql? – HTTP500 Oct 24 '12 at 13:29
I do not know in fact.. All I know is that I want to have some solution accessible through 1 IP address and the ways how it will be done are not interesting for me. – John Oct 24 '12 at 13:42
1  
Are you looking for a multi-master solution? Or is read-only access with 1 ip address good enough? – Olav Grønås Gjerde Nov 1 '12 at 23:51
Just a personal note - I've used postgres and Oracle RAC quite a bit....choose Postgres unless there is an extremely specific requirement for RAC. – rfusca Nov 3 '12 at 16:36
@John The way it's done should be interesting to you. If you're doing replication and failover you need to understand and care how it works, or hire someone who does. It isn't the sort of thing you can just plug in and go with. Consider hiring help: postgresql.org/support/professional_support – Craig Ringer Nov 4 '12 at 3:26

migrated from serverfault.com Nov 3 '12 at 13:29

1 Answer

up vote 1 down vote

This is pretty broad. Let's answer (2) first:

http://www.postgresql.org/support/professional_support/

Now, on to (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?

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 repmgr can 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.