PostgreSQL High Availability options

PostgreSQL is widely accepted as the most scalable and stable Open Source database in the industry. It is also known to hold its own against any of the proprietary databases as well. There are a plethora of High Availability options available for every workload and business requirement. Below is a brief listing of the common High Availability options for PostgreSQL. This is by no means an exhaustive list but it does provide some starting points. (and before anyone yelps, 9.0 isn't out yet) Log Shipping: Also known as streaming replication and Point in Time Recovery, log shipping is a great mechanism for creating a Highly Available requirement. Log shipping is the least administrative overhead solution for a number of business requirements including:
  • DDL Replication
  • Zero load backups
  • Failover
  • Geographically disparate servers
  • You have an existing application that can not be modified
  • No read-only slave requirement
    Solutions include: PITRTools, Walmgr
    Async Replication: Asynchronous Replication provides a Master->Slave (also known as Origin->Subscriber) model of replication. Asynchronous Replication for PostgreSQL is an excellent option if your business requirements include:
  • Few DDL changes (or Managed DDL changes)
  • Read from Slaves
  • Geographically disparate servers
  • Failover or Switchover capability
  • Zero load backups
    Solutions include: Replicator, Londiste, Slony
    Block Replication: Block level replication can be Synchronous or Asynchronous. It provides the lowest level of replication between two (or more) systems. In short it replicates disk blocks as they are modified, over the network the the receiving system. This type of replication is particularly useful for a true HA zero data loss scenario. Consider this option if your business requirements include:
  • Zero data loss
  • You do not need to read from the slave
  • You do not want to offload backups
  • Your slave/standby is in the same data center (synchronous)
    Solutions include: DRBD (Linux)
    Also note that any of the solutions may be used in conjunction with another solution. Thus you may use Block replication, but also have log shipping for a warm standby. If you have further questions, please do not hesitate to contact us.