Postgresql – Creating High Availability Cluster with PostgreSQL

clusteringhigh-availabilitypostgresqlreplication

I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven't found any good way to do this on PostgreSQL.

Where should I start and what are the steps involved in creating a high-availability cluster?

Best Answer

There are many ways to set up an HA cluster for PostgreSQL. Some ways are big and tough.

The easiest method is the following:

sync your db to a standby server
Install heartbeat and do a asymmetric clustering

This method is the easiest method to build a HA cluster with graceful degradation .

Related Solutions

Postgresql – High Availability for postgresql

1a. Warm standby is a "live", incremental backup fed with complete blocks of changes (wal segments) 16 mb each, that are send to the standby node once they're filled. You cannot query a warm standby node. 16 mb of changes (by default) can mean a lot of transactions, should the master fail, they'll be lost.

1b. Hot Standby. (also a "live" incremental backup) .small changes are send to the slave (wal records, which are tiny parts of a wal segment). You can query (read only) the hot standby node. The window for lost transactions should the master fail is very tiny. There are synchronous and asynchronous hot standby nodes, a synchronous node will force the master to wait for it to confirms the application of changes and then the master will commit the transaction.In asynchronous replication the master sends the wal records and does not wait for confirmation. The former requires a very reliable and fast link between the master and the slave,also adds overhead to the master but guarantees no data loss.

Regarding incremental backups: 1. You take a base copy of your whole database installation. 2. Ship it to the slave. 3. Configure it to catch up for changes.

Streaming Replication (hot standby) is the winner here. I personally prefer asynchronous replication as it does not impose a considerable burden on the master and the replication lag is very small (a couple of seconds in many cases)

One complement to this setup is pg-pool. It acts as a proxy between the application and the servers participating in a replication configuration like the one described above, it has load balancing and parallel query capabilities. It's able also to provide automatic failover. http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting/index.html

Postgresql – High availability in postgresql

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.

Best Answer

Related Solutions

Postgresql – High Availability for postgresql

Postgresql – High availability in postgresql

Related Question