Application connection management v5

Suggest edits

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.

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.

Next →

EDB Postgres Distributed Proxy