VMware Postgres High Availabitity with pg_auto_failover

VMware Postgres includes pg_auto_failover, an open source extension for PostgreSQL, that monitors replication between Postgres instances and manages automatic failover for a group of Postgres nodes. pg_auto_failover is optimised for simplicity and correctness, and orchestrates business continuity in a clustered Postgres environment.

The pg_auto_failover architecture requires three key components as a minimum:

  • a Postgres primary node
  • a Postgres secondary node, using a synchronous hot standby setup
  • a pg_auto_failover monitor node that acts both as a witness and an orchestrator

This architecture guarantees availability of the Postgres service to users and applications, while automating maintenance operations. By default, pg_auto_failover uses synchronous replication between primary and standby nodes.

Review the key architecture concepts of pg_auto_failover in Architecture Basics.

Prerequisites

The pg_auto_failover architecture requires a three server nodes setup, to host the monitor, the primary, and the secondary Postgres instances.

Install the VMware Postgres software on each node as described in Installing the Postgres Server RPM, up to point 3. You do not need to initialize the Postgres instances in advance.

Installing pg_auto_failover

pg_auto_failover is included with the VMware Postgres release, and installed during the rpm package installation. For details see Installing the Postgres Server RPM.

pg_auto_failover uses the pg_autoctl utility with subcommands to initialize and manage the replicated environment. For reference see Main pg_autoctl commands.

Confirm the pg_auto_failover installation by using:

$ pg_autoctl --version

Creating a High Availability cluster

pg_auto_failover relies on the creation of a monitor service that manages one or several formations. A formation can contain a group of Postgres nodes in a highly available configuration. The node group may contain two or more nodes.

For detailed steps on how to configure and use pg_auto_failover, see the documentation pages at the pg_auto_failover site.

Prior to creating the HA architecture, determine key configuration details by referring to the important decision points discussed in Configuring pg_auto_failover.

In summary, the key steps include:

  • Create the monitor service, similar to:

    $ pg_autoctl create monitor --auth trust --no-ssl 
    

    This example creates a monitor in a test environment with no security restrictions, which does not reflect a production setup. The example assumes the PGDATA environment variable has been set to reflect the data location in your setup.

    This command initializes the monitor and then shuts down the service. To run the monitor service, use:

    $ pg_autoctl run
    

    For further details, refer to the example in the pg_auto_failover Run a Monitor tutorial or the pg_auto_failover Monitor page.

  • Add the primary and secondary nodes to the formation.

    For details, refer to the pg_auto_failover Bring up the nodes example or the command pg_autoctl create postgres reference.

  • Confirm the primary and secondary node architecture.

    For details, refer to pg_autoctl show state.

  • Verify the default formation and the postgres uri used to connect to the pg_auto_failover nodes.

    For details see pg_autoctl show uri.

     $ pg_autoctl show uri
    
     Type    |  Name   | Connection String
     -----------+---------+-------------------------------
     monitor | monitor | postgres://autoctl_node@serverA-rhel7-postgres.c.dataproject.internal:5432/pg_auto_failover?sslmode=prefer
     formation | default |