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.
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.
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
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
PGDATAenvironment 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
Add the primary and secondary nodes to the formation.
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 |