Application connection management v5

Managing application connections is an important part of high availability.

Especially with asynchronous replication, having a consistent write lead node is important to avoid conflicts and guarantee availability for the application.

EDB Postgres Distributed provides a proxy layer called PGD Proxy, which is normally installed in highly available configuration (at least two instances per region).

The PGD Proxy connects to one of the EDB Postgres Distributed nodes and monitors routing configuration changes as decided by the EDB Postgres Distributed cluster. It ensures that the connections are routed to the correct nodes consistently.

Configuration

Configuring the routing is done through either SQL interfaces or through PGD-CLI.

You can enable routing decisions by calling the bdr.alter_node_group_option() function. For example:

SELECT bdr.alter_node_group_option('region1-group', 'enable_proxy_routing', 'true')

You can disable it by setting the same option to false.

Additional group-level options affect the routing decisions:

  • route_writer_max_lag Maximum lag in bytes of the new write candidate to be selected as write leader. If no candidate passes this, no writer is selected automatically.
  • route_reader_max_lag Maximum lag in bytes for a node to be considered a viable read-only node. Currently reserved for future use.

Per-node configuration of routing is set using bdr.alter_node_option(). The available options that affect routing are the following:

  • route_dsn The dsn used by proxy to connect to this node.
  • route_priority Relative routing priority of the node against other nodes in the same node group.
  • route_fence Whether the node is fenced from routing, that is, it can't receive connections from PGD Proxy.
  • route_writes Whether writes can be routed to this node, that is, whether the node can become write leader.
  • route_reads Whether read-only connections can be routed to this node. Currently reserved for future use.

You can also configure the proxies using SQL interfaces. You can add proxy configuration using bdr.create_proxy. For example, SELECT bdr.create_proxy('region1-proxy1', 'region1-group'); adds the default configuration for a proxy named region1-proxy1 that's a member of PGD group region1-group. The name of the proxy given here must be same as the name given in the proxy configuration file. You can remove a proxy configuration using SELECT bdr.drop_proxy('region1-proxy1'). The proxy is deactivated as a result.

You can configure options for each proxy using the bdr.alter_proxy_option() function. The available options are:

  • listen_address Address for the proxy to listen on.
  • listen_port Port for the proxy to listen on.
  • max_client_conn Maximum number of connections for the proxy to accept.
  • max_server_conn Maximum number of connections the proxy can make to the Postgres node.
  • server_conn_timeout Connection timeout for server connections.
  • server_conn_keepalive Keepalive interval for server connections.
  • consensus_grace_period Duration for which proxy continues to route even upon loss of a Raft leader. If set to 0s, proxy stops routing immediately.

The current configuration of every group is visible in the bdr.node_group_routing_config_summary view. Similarly, the bdr.node_routing_config_summary view shows current per-node routing configuration. bdr.proxy_config_summary shows per-proxy configuration.

You can also do a switchover operation to explicitly change the node that's the write leader. To do so, use the bdr.routing_leadership_transfer() function.