In PostgreSQL you can switch a database from the primary server to the standby role, as well as from the standby server (slave) to the primary. This is known as a database switchover or failover. In the case of a disaster, a controlled failover can always be made manually. However, many customers prefer automated failover over manual failover as it reduces the amount of downtime and minimizes or even eliminates data loss.

Building PostgreSQL High-Availability Clusters with true experts

Cybertec has close to 20 years of experience in the field of PostgreSQL database clustering. We have built clusters using a variety of technologies including, but not limited to:

  • Patroni and vipmanager (“Virtual IP Manager”)
  • Linux HA (heartbeat, pacemaker, corosync, etc.)
  • repmgr
  • Solaris Cluster

Benefit from our experience.

Clustering PostgreSQL with Patroni and vipmanager

While there are many tools out there for clustering and replicating PostgreSQL, not all of them are equally good.

Cybertec recommends “Patroni”, which is based on the most modern technologies currently available in the High-Availability world. Patroni has been widely adopted by the industry and provides full PostgreSQL automation. Based on modern consensus algorithms, Patroni will automatically promote and demote PostgreSQL servers and provide superior availability using minimal complexity.

The advantages of Patroni are:

  • Proven, cutting-edge technology
  • Full cluster automation
  • Low complexity and maximum reliability
  • Fully Open Source
  • Integrates into cloud solutions (Kubernetes, OpenShift, etc.)
  • Infinite cluster size

If you are looking for a professional setup, you are most likely looking for a cluster promising automatic IP failover. The Cybertec “vipmanager” extension for Patroni offers exactly that. If something goes wrong inside your cluster, Patroni and vipmanager will make sure there is no need to reconfigure your application or do any kind of manual work. Patroni and vipmanager will manage IP addresses and automatically bind the IP seen by your software to the desired machine.

Patroni and vipmanager automatically manage IP addresses.

Patroni is very easy to use. Experience has shown that complexity is the main enemy of any good high-availability solution. Our aim, therefore, is to reduce the degree of complexity to an absolute minimum. Low complexity is one of the key advantages of Patroni over traditional solutions such as Linux HA (pacemaker, corosync, heartbeat, STONITH, etc.).

Minimal complexity ensures High-Availability.

Find out more about Patroni and vipmanager contact us today.

PostgreSQL Replication Clustering Failover

Other solutions to PostgreSQL clustering and replication

Patroni’s simplicity gives it a real edge over more traditional solutions. However, many customers out there are still using traditional solutions, which are of course fully supported by Cybertec.

Linux HA (pacemaker, corosync, etc.)

Linux HA is a general purpose solution to High-Availability as it can be used to cluster pretty much any kind of software. This kind of flexibility is also its greatest weakness because it introduces a lot of complexity, which is not necessarily desirable.

In addition to this, Patroni has been made specifically for PostgreSQL so it can use onboard functionality a lot better than Linux HA can.

However, Linux HA is still a good solution and offers a rich set of features including:

  • Detects and recovers machine and application failures
  • Supports redundancy configurations
  • Supports both quorate and resource-driven clusters
  • Provides configurable strategies to deal with quorum loss (= too many machines were lost)
  • Supports application startup/shutdown ordering, regardless of which machine(s) the applications are on
  • Supports applications that must/must not run on the same machine
  • Supports applications that need to be active on multiple machines
  • Supports applications with multiple modes (e.g. master/slave)

Linux HA allows PostgreSQL to integrate with other components that are required to be available around the clock.

pgpool-II and pgbouncer

When we talk about separate pooling servers in the Postgre context, two products stand out – PgBouncer and pgpool-II. Both tools are often used in the context of PostgreSQL clustering and connection pooling.
Our database experts have written about pgpool-II and pgbouncer. For more information check out the following blog post.

In general, we recommend Patroni over pgpool-II because it is less invasive.

Legacy: Slony and Skytools (londiste)

Slony was developed around the release date of PostgreSQL 7.4. It supports trigger-based replication. Before the introduction of transaction-log-based replications, triggers were pretty much the only way to replicate data to a set of remote hosts.

Skytools (namely a tool called londiste) faces issues that are similar to those of Slony and is therefore considered outdated. If you are interested in replicating single tables, consider checking out “CREATE PUBLICATION” and “CREATE SUBSCRIPTION”, which were introduced in PostgreSQL 10.0.

If you are using Slony and are eager to migrate or are just interested in learning about new technologies, contact us today.

Professional help

Contact us today to receive your personal offer from Cybertec. We offer timely delivery, professional handling, and over 17 years of PostgreSQL experience.

Contact us