PolarProxy allows you to customize a routing rule for requests that contain a specified function or a specified table. By default, read requests that contain functions in your PolarDB cluster are routed to the read-only nodes and read requests that contain tables in your PolarDB cluster are routed to the primary node. You can customize a routing rule to route read requests that contain a specified function in your PolarDB cluster to the read-only nodes. You can also customize a routing rule to route read requests that contain a specified table to the primary node.
Prerequisites
A privileged account is used to connect to the primary endpoint of your PolarDB cluster.
Create the polar_proxy_utils extension
Execute the following statement as the polar_superuser
user to create the polar_proxy_utils
extension:
create extension polar_proxy_utils;
Customize a routing rule
To customize a routing rule, execute the following statement:
polar_add_proxy_routing_strategy(_name, _type, rw_mode);
Parameters in the statement:
_name: the name of the table or function for which you want to customize a routing rule.
_type: specifies whether the object that is specified by the _name parameter is a table or a function. The value t specifies a table. The value f specifies a function.
rw_mode: specifies whether to route requests to the primary node or the read-only nodes. The value w specifies the primary node. The value r specifies the read-only nodes.
For example, execute the polar_add_proxy_routing_strategy('lol', 't', 'w');
statement.
Before this statement is executed,
select * from lol
operations are routed to the read-only nodes.After this statement is executed,
select * from lol
operations are routed to the primary node.
Query routing rules
To query routing rules, execute the following statement:
select polar_list_proxy_routing_strategy();
Delete a routing rule
To delete a routing rule, execute the following statement:
select polar_delete_proxy_routing_strategy(_name, _type);
Parameters in the statement:
_name: the name of the table or function that is associated with the routing rule to be deleted.
_type: specifies whether the object specified by the _name parameter is a table or a function. The value t specifies a table. The value f specifies a function.
For example, run the select polar_delete_proxy_routing_strategy('lol', 't');
statement.
Before this statement is executed,
select * from lol
operations are routed to the primary node.After this statement is executed,
select * from lol
operations are routed to the read-only nodes.
Delete all routing rules
To delete all routing rules, execute the following statement:
select polar_truncate_proxy_routing_strategy();