Virtual IP and Redundancy

Overview

This document describes the virtual router multiplexing protocol provided by the OpenProxy module in OpenSQL v3.0. (VRRP)-based virtual IP configuration and management features, including how to set up and configure them.

Virtual IP management features

OpenProxy uses the Virtual Router Multiplexing Protocol to perform Virtual IP management.
OpenProxy handles virtual router events through a separate asynchronous runtime to avoid impacting PostgreSQL connection pooling and load balancing features.
It can also work in a unicast manner by sending advertisement packets to the multicast address of a specified network interface or by setting the IPv4 addresses of other peer nodes (other nodes with OpenProxy configured).
- Sending/receiving advertisement packets is accomplished by opening a Linux Raw (L3; IP) socket and binding to the network's Multicast address 224.0.0.18 or to the IPv4 address of the node you are running on (if the Unicast option is enabled).
- Virtual IP acquisition/release is done by opening a Linux Netlink socket and sending RTM_NEWADDR or RTM_DELADDR messages directly to the kernel.
To enable the virtual IP feature, the OpenProxy process requires the Linux system privilege You need to set cap_net_admin and cap_net_raw.

Patroni integration

For PostgreSQL clusters configured with Patroni, you can specify that OpenProxy should perform topology discovery via Patroni's REST API without having to define the roles of the PostgreSQL servers to connect to.
- For each Shard in each Pool, you can enable it by setting the use_patroni (Boolean) key to
  true; if it is not defined, the feature is disabled.
- For each shard, you can additionally set the patroni_port variable as needed to set the default
  Port 8008 You can also integrate with Patroni servers that are listening on ports other than the default.
- When importing a cluster topology in conjunction with a Patroni server, each server in the shard
  must have a port number to connect to PostgreSQL and a PostgreSQL node role (Primary or Replica) is ignored.
A Pool set up to work with Patroni sends a request to Patroni's REST API server every set renew_interval (in milliseconds, default: 5000) to get the connection information and Primary/Replica Role information of the PostgreSQL server.
HTTP requests to the Patroni server have a timeout of 1 second by default. After sending a request to a server in the shard, if it does not receive a response within the timeout or fails to parse the response, it will send the request to the next server.
- If all servers are queried and the response is not processed properly, the Role of the server is not updated. This can cause Query Parsing and Read-Write Splitting functions in transaction pooling mode to not work properly.

Configuration

Virtual routers

You can enable the virtual router feature by setting the [general.virtual_router] entry in the OpenProxy configuration file, openproxy.toml.

[general.virtual_router]
interface = "eno1"
router_id = 50
priority = 150
advert_int = 3
vip_addresses = [ "192.168.0.200/24" ]
pre_promote_script = "/home/opensql/startup.sh"
pre_demote_script = "/home/opensql/cleanup.sh"
unicast_peers = [ "192.168.0.7", "192.168.0.8" ]

Once defined, the virtual router state machine is activated by a separate runtime at the start of the OpenProxy process to handle virtual IP-related events.

The interface entry specifies the name of this node's network interface to communicate with other OpenProxy nodes.
The router_id entry is an identifier to distinguish the virtual router cluster on this network, with a value between 1 and 255. It must have the same value as the other OpenProxy nodes that will participate in the virtual IP MASTER election.
The priority entry is the priority value that this node will have in electing the MASTER virtual router, with a value between 1 and 255. A node with a priority value of 255 will attempt to occupy the virtual IP in the MASTER state rather than the BACKUP state at startup. Otherwise, the node with the highest priority value is recognized through advertisement packets and becomes the MASTER. It is recommended that you set this differently for each node.
The advert_int entry is the frequency at which the MASTER node will forward advertisement packets that propagate its status and priority values within the network, measured in seconds, and has a value between 1~ 255. All nodes in the cluster must have the same value, and if the advertisement packet is not received for three consecutive times, the other BACKUP nodes begin to elect the MASTER.
vip_addresses is a list of virtual IPs to be occupied by the MASTER node and should be given as a comma- separated list of IPv4 addresses and the bitmask length of the network.
pre_promote_script (Optional) is an entry that specifies a command to run on this node when the BACKUP→ MASTER promotion occurs. In certain cloud vendor environments, the node's network interface is virtualized, so registering a virtual IP might require additional work beyond registering the node's Secondary IP.
pre_demote_script (Optional) is an entry that specifies the command to run on this node if a MASTER→ BACKUP demote occurs.
unicast_peers (Optional) is an option to forward advertisement packets to other OpenProxy nodes in a unicast manner instead of a multicast manner. Specify the IPv4 addresses of other OpenProxy nodes that will participate in the virtual IP MASTER election as a comma-separated array.

Patroni REST API integration

In the OpenProxy configuration file, openproxy.toml, enable the topology discovery feature by setting the use_patroni key in the Shard definition in the section where the Pool you want to work with the Patroni REST API is defined.

You can set the renew_interval value in the [general] section to adjust how often to update the
PostgreSQL server's Role by reading the openproxy.toml file or querying the Patroni server.
The Port number and Role values are not actually referenced when creating an OpenProxy Connection, but should be entered for backward compatibility.

[general]
renew_interval = 5000  ## You can update the Config by reading the openproxy.toml file or query the Patroni server 
                       ## to set the period to update the Role of the PostgreSQL server.
                       ## The unIt IS milliseconds, wIth a default value of 5000.

[pools.my_pool]

[pools.my_pool.shards.0]
servers = [
  [
    "192.168.0.8",    ## Enter the IPv4 addreSS of the hoStS where the Patroni REST API Server IS runnIng.

    5432,             ## Port number to connect to PoStgreSQL, Ignored when connectIng Patroni.
    "Auto",           ## Whether the PostgreSQL InStance haS a Primary / Replica Role IS Ignored when IntegratIng Patroni.
  ],
  [
    "192.168.0.9",
    5432,
    "Auto",
  ],
  [
    "192.168.0.10",
    5432,
    "Auto",
  ]
]
database = "postgres"
use_patroni = true
patroni_port = 8765    ## Specifies the port number of the Patroni REST API server. If left blank, the default value 8008 is used.

Execution

To enable the virtual router feature, the OpenProxy process must run as root user or be granted cap_net_raw and cap_net_admin privileges.

cap_net_raw is required to open a RAW type network socket.
cap_net_admin is required to add/delete Secondary IPs on a network interface.

Authorize the executable binary openproxy using Linux setcap as shown below.

$ sudo setcap 'cap_net_raw=eip cap_net_admin=eip' openproxy

If you are executing it as a systemd service, add the options below to grant permissions.

[Service]
AmbientCapabilities=CAP_NET_RAW CAP_NET_ADMIN

PreviousLoad balancing NextTmax O2 Extensions

Last updated 5 days ago