PostgreSQL Application Connection Failover Using HAProxy with xinetd

PostgreSQLRecently we published a blog about a very simple application failover using libpq features which could be the simplest of all automatic application connection routing.  In this blog post, we are discussing how a proxy server using HAProxy can be used for connection routing which is a well-known technique with very wide deployment. There are multiple ways HAProxy can be configured with PostgreSQL which we shall cover in upcoming blogs, but configuring a xinetd service to respond to Http requests on individual nodes of a database cluster is one of the most traditional approaches.

On HAProxy

HAProxy could be the most popular connection routing and load balancing software available. Along with PostgreSQL, it is used across different types of High Availability Clusters. HAProxy, as the name indicates, works as a proxy for TCP (Layer 4) and HTTP (Layer 7), but it has additional features of load balancing also. The TCP proxying feature allows us to use it for database connections of PostgreSQL. There are three objectives of connection routing of a PostgreSQL cluster:

  1. Read-Write load to Master
  2. Read-Only load to Slave
  3. Load balancing of multiple slaves is achievable by HAProxy.

HAProxy maintains an internal routing table. In this blog, we are going to take a look at the most traditional approach to configure HAProxy with PostgreSQL. This approach is independent of underlying clustering software and can be used even with the traditional PostgreSQL built-in replication feature without any clustering or automation solutions.

In this generic configuration, we won’t use any special software or capabilities offered by clustering frameworks. This requires us to have 3 components:

  1. A simple shell script to check the status of the PostgreSQL instance running on the local machine.
  2. A xinetd service daemonizer.
  3. HAProxy: Which maintains the routing mechanism.

Concept:

HAProxy has a built-in check for PostgreSQL with option pgsql-check.  (Documentation is available here) This is good enough for basic Primary failover. But the lack of features to detect and differentiate the Primary and Hot-Standby nodes makes it less useful.

Meanwhile, HAProxy with xinetd would give us the luxury to see what is the Master and what is a hot standby to redirect connections appropriately. We will be writing about the built-in check pgsql-check in upcoming blog posts and explain how to make use of it effectively.

Xinetd (Extended Internet Service Daemon) is a Super-server daemon. It can listen to requests on custom ports and respond to requests by executing custom logic. In this case, we have custom scripts to check the status of the database. In the script we use writes HTTP header with status code. Different status code represents the status of the database instance. Status code 200 if PostgreSQL instance is Primary, 206 if PostgreSQL is Hot Standby, and 503 if status cannot be verified.

Every database server needs to have a xinetd service running on a port for status checks of PostgreSQL instances running in them. Generally, port: 23267 is used for this purpose, but we can use any port of our choice. This service uses a custom-developed script (shell script) to understand the 3 different statuses of PostgreSQL instances.

  1. Primary database
  2. Standby database
  3. Unable to connect to PostgreSQL – Indication of PostgreSQL down

Since the status check is available through a port exposed by xinetd, HAProxy can send a request to that port and understand the status from the response.

Installation and Configuration

First, we need to have a script that can check the status of a PostgreSQL instance. It is quite simple, the shell script invokes psql utility and executes pg_is_in_recovery() function of postgres. Based on the result, it can understand whether it is a master or slave or whether it failed to connect.

A sample script is here:

Instead of password-based authentication, any password-less authentication methods can be used.

It is a good practice to keep the script in /opt folder, but make sure that it has got execute permission:

Now we can install xinetd on the server. Optionally, we can install a telnet client so that we can test the functionality.

Now let us create a xinetd definition/configuration.

Add a configuration specification to the same file as below:

Add the pgsqlchk service to /etc/services.

Now xinetd service can be started.

Configuring HAProxy to use xinetd

We need to have HAProxy installed on the server:

Create or modify the HAProxy configuration. Open /etc/haproxy/haproxy.cfg using a text editor.

A sample HAProxy configuration file is given below: