Step 7 - Configure proxies v5
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
andenable_proxy_routing
node group options totrue
for the subgroup. Usebdr.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
bdr.create_proxy
and passing the new proxy name and the subgroup to attach it to. Thebdr.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 thelisten_address
to0.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
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
andlisten_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:
You can use the bdr.node_group_summary
view to check the status of options previously set with bdr.alter_node_group_option()
:
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:
You can use the bdr.proxy_config_summary
view to check that the proxies were created:
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.
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.
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
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.
Configure the systemd service on each host
Switch the service file from using root to using the pgdproxy user.
Reload the systemd daemon.
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:
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.
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:
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:
Restart the service
On each host where the proxy is being installed, restart the pgd-proxy service.
Confirm it's running correctly:
When running, it shows Active: (running)
in the opening details.
Test the proxy
At this point, connecting to the PGD Proxy port on any host in the cluster results in the connection being routed to the current write lead node.
For example, and assuming you've installed the proxy on all three hosts, then connecting to the proxy on host-three results in the connection being routed to node-one.
This example passes connection details, using the -d
flag of psql with the hostname for the proxy you just configured and the proxy port number:
Once connected to the proxy, you can query the server to find out which node the proxy connected you to:
You should have connected to the current write leader of the subgroup. You can confirm that by querying which node is the write leader for the subgroup you're connected to:
And the write_lead is node-one, too, so you confirm you're being proxy-connected to the write leader.
- On this page
- Configure proxies
- Worked example
- Preparing for proxies
- Create a pgdproxy user on the database
- Configure proxy routes to each node
- Create a pgdproxy user on each host
- Configure the systemd service on each host
- Create a proxy config file for each host
- Install a PGD proxy configuration on each host
- Restart the service
- Test the proxy