Step 7 - Configure proxies ========================== Configure proxies ----------------- PGD can use proxies to direct traffic to one of the cluster’s nodes, selected automatically by the cluster. There are performance and availability reasons for using a proxy: - Performance: By directing all traffic (in particular, write traffic) to one node, the node can resolve write conflicts locally and more efficiently. - Availability: When a node is taken down for maintenance or goes offline for other reasons, the proxy can direct new traffic to a new write leader that it selects. It’s best practice to configure PGD Proxy for clusters to enable this behavior. Configure the cluster for proxies ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ To set up a proxy, you need to first prepare the cluster and subgroup the proxies will be working with by: - Logging in and setting the ``enable_raft`` and ``enable_proxy_routing`` node group options to ``true`` for the subgroup. Use :ref:`bdr.alter_node_group_option ` , passing the subgroup name, option name, and new value as parameters. - Create as many uniquely named proxies as you plan to deploy using :ref:`bdr.create_proxy ` and passing the new proxy name and the subgroup to attach it to. The :ref:`bdr.create_proxy ` does not create a proxy, but creates a space for a proxy to register itself with the cluster. The space contains configuration values which can be modified later. Initially it is configured with default proxy options such as setting the ``listen_address`` to ``0.0.0.0`` . - Configure proxy routes to each node by setting route_dsn for each node in the subgroup. The route_dsn is the connection string that the proxy should use to connect to that node. Use :ref:`bdr.alter_node_option ` to set the route_dsn for each node in the subgroup. - Create a pgdproxy user on the cluster with a password or other authentication. Configure each host as a proxy ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Once the cluster is ready, you need to configure each host to run pgd-proxy: - Create a pgdproxy local user. - Create a ``.pgpass`` file for that user that allows the user to log into the cluster as pgdproxy. - Modify the systemd service file for pgdproxy to use the pgdproxy user. - Create a proxy config file for the host that lists the connection strings for all the nodes in the subgroup, specifies the name for the proxy to use when fetching proxy options like ``listen_address`` and ``listen_port`` . - Install that file as ``/etc/edb/pgd-proxy/pgd-proxy-config.yml`` . - Restart the systemd service and check its status. - Log in to the proxy and verify its operation. Further detail on all these steps is included in the worked example. Worked example -------------- Preparing for proxies --------------------- For proxies to function, the ``dc1`` subgroup must enable Raft and routing. Log in to any node in the cluster, using psql to connect to the ``bdrdb`` database as the enterprisedb user. Execute: .. code:: sql SELECT bdr.alter_node_group_option(dc1, enable_raft, true); SELECT bdr.alter_node_group_option(dc1, enable_proxy_routing, true); You can use the :ref:`bdr.node_group_summary ` view to check the status of options previously set with ``bdr.alter_node_group_option()`` : .. code:: sql SELECT node_group_name, enable_proxy_routing, enable_raft FROM bdr.node_group_summary WHERE parent_group_name IS NOT NULL; __OUTPUT__ node_group_name | enable_proxy_routing | enable_raft - ----------------+----------------------+------------- dc1 | t | t (1 row) bdrdb=# Next, create a PGD proxy within the cluster using the ``bdr.create_proxy`` function. This function takes two parameters: the proxy’s unique name and the group you want it to be a proxy for. In this example, you want a proxy on each host in the ``dc1`` subgroup: :: SELECT bdr.create_proxy(pgd-proxy-one,dc1); SELECT bdr.create_proxy(pgd-proxy-two,dc1); SELECT bdr.create_proxy(pgd-proxy-three,dc1); You can use the :ref:`bdr.proxy_config_summary ` view to check that the proxies were created: .. code:: sql SELECT proxy_name, node_group_name FROM bdr.proxy_config_summary; __OUTPUT__ proxy_name | node_group_name - ----------------+----------------- pgd-proxy-one | dc1 pgd-proxy-two | dc1 pgd-proxy-three | dc1 bdrdb=# Create a pgdproxy user on the database -------------------------------------- Create a user named pgdproxy and give it a password. This example uses ``proxysecret`` . On any node, log into the ``bdrdb`` database as enterprisedb/postgres. :: CREATE USER pgdproxy PASSWORD proxysecret; GRANT bdr_superuser TO pgdproxy; Configure proxy routes to each node ----------------------------------- Once a proxy has connected, it gets its dsn values (connection strings) from the cluster. The cluster needs to know the connection details that a proxy should use for each node in the subgroup. This is done by setting the ``route_dsn`` option for each node to a connection string that the proxy can use to connect to that node. Please note that when a proxy starts, it gets the initial dsn from the proxy’s config file. The route_dsn value set in this step and in config file should match. On any node, log into the bdrdb database as enterprisedb/postgres. .. code:: sql SELECT bdr.alter_node_option(node-one, route_dsn, host=host-one dbname=bdrdb port=5444 user=pgdproxy); SELECT bdr.alter_node_option(node-two, route_dsn, host=host-two dbname=bdrdb port=5444 user=pgdproxy); SELECT bdr.alter_node_option(node-three, route_dsn, host=host-three dbname=bdrdb port=5444 user=pgdproxy); Note that the endpoints in this example specify ``port=5444`` . This is necessary for EDB Postgres Advanced Server instances. For EDB Postgres Extended and community PostgreSQL, you can omit this. Create a pgdproxy user on each host ----------------------------------- .. code:: shell sudo adduser pgdproxy This user needs credentials to connect to the server. Create a ``.pgpass`` file with the ``proxysecret`` password in it. Then lock down the ``.pgpass`` file so it’s accessible only by its owner. .. code:: shell echo -e "*:*:*:pgdproxy:proxysecret" | sudo tee /home/pgdproxy/.pgpass sudo chown pgdproxy /home/pgdproxy/.pgpass sudo chmod 0600 /home/pgdproxy/.pgpass Configure the systemd service on each host ------------------------------------------ Switch the service file from using root to using the pgdproxy user. .. code:: shell sudo sed -i s/root/pgdproxy/ /usr/lib/systemd/system/pgd-proxy.service Reload the systemd daemon. .. code:: shell sudo systemctl daemon-reload Create a proxy config file for each host ---------------------------------------- The proxy configuration file is slightly different for each host. It’s a YAML file that contains a cluster object. The cluster object has three properties: - The name of the PGD cluster’s top-level group (as ``name`` ) - An array of endpoints of databases (as ``endpoints`` ) - The proxy definition object with a name and endpoint (as ``proxy`` ) The first two properties are the same for all hosts: :: cluster: name: pgd endpoints: - "host=host-one dbname=bdrdb port=5444 user=pgdproxy" - "host=host-two dbname=bdrdb port=5444 user=pgdproxy" - "host=host-three dbname=bdrdb port=5444 user=pgdproxy" Remember that host-one, host-two, and host-three are the systems on which the cluster nodes (node-one, node-two, node-three) are running. You use the name of the host, not the node, for the endpoint connection. Again, note that the endpoints in this example specify ``port=5444`` . This is necessary for EDB Postgres Advanced Server instances. For EDB Postgres Extended and community PostgreSQL, you can set this to ``port=5432`` . The third property, ``proxy`` , has a ``name`` property. The ``name`` property is a name created with ``bdr.create_proxy`` earlier, and it’s different on each host. A proxy can’t be on the same port as the Postgres server and, ideally, should be on a commonly used port different from direct connections, even when no Postgres server is running on the host. Typically, you use port 6432 for PGD proxies. :: proxy: name: pgd-proxy-one In this case, using ``localhost`` in the endpoint specifies that this proxy will listen on the host where the proxy is running. Install a PGD proxy configuration on each host ---------------------------------------------- For each host, create the ``/etc/edb/pgd-proxy`` directory: :: sudo mkdir -p /etc/edb/pgd-proxy Then, on each host, write the appropriate configuration to the ``pgd-proxy-config.yml`` file in the ``/etc/edb/pgd-proxy`` directory. For this example, you can run this on host-one to create the file: :: cat <