Postgres Pro Enterprise : Documentation: 17: F.67. proxima — a combination of a proxy and a connection pooler : Postgres Professional

F.67. proxima — a combination of a proxy and a connection pooler
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-ent-17-contrib`	Home	Next

F.67. proxima — a combination of a proxy and a connection pooler #

F.67.1. Overview
F.67.2. Considerations and Limitations
F.67.3. Managing proxima
F.67.4. Configuration Parameters

proxima is a Postgres Pro Enterprise extension that combines functionality of a proxy server and a connection pooler.

F.67.1. Overview #

The proxima extension can be used on a single-node Postgres Pro Enterprise server, in a standard Postgres Pro Enterprise primary-standby cluster or a BiHA cluster. The extension offers the following features:

Proxy server: proxima becomes a single connection point for client connections and redirects queries to the primary server or the BiHA cluster leader.
Connection pooler: proxima manages backend processes to lower consumption of system resources and prevent performance degradation.
SSL support: you can configure proxima to operate with SSL connections.
Dynamic dedicated session: proxima maintains a session between the client and the backend for the entire lifetime of the client connection.

F.67.1.1. Authentication #

The proxima extension uses the same authentication rules that are listed in the pg_hba.conf file. The following authentication methods are supported:

F.67.1.2. Operation in a BiHA Cluster #

The proxima extension offers a special cluster operation mode for a BiHA cluster. When installed in the BiHA cluster, proxima automatically receives all required cluster configuration parameters from biha, such as:

number of cluster nodes
node IDs, addresses, and ports
node roles (leader or follower)

When used in the BiHA cluster, proxima registers a set of callbacks to be informed about events in the cluster. For example, when the cluster leader changes, proxima receives notification about this event and automatically redirects traffic to the new leader.

F.67.1.3. Dynamic Dedicated Session #

When executing queries, a backend may keep temporary state within one session and create objects specific for this session, such as temporary tables, temporary views, etc. This information and these objects are not available for other backends. If a connection pooler redirects a client to another backend after such an object is created, the client has no more access to the object. To avoid this issue, proxima offers the dynamic dedicated session functionality that allows to retain sessions between clients and specific backends during the client connection lifetime.

Note

If you use extensions that keep their state within a session, you must manually enable the forced dynamic dedicated session. For more information, see Section F.67.3.3.

The proxima extension establishes the dynamic dedicated session automatically if a query contains any of the following objects or SQL functions:

temporary tables
temporary sequences
temporary views
prepared queries
cursors
notification mechanisms
SET SESSION
SET SESSION AUTHORIZATION
SET ROLE
pg_advisory_lock
SET SESSION CHARACTERISTICS AS TRANSACTION

The session remains dedicated until the above mentioned objects are deleted.

The DISCARD function breaks session objects. After executing DISCARD ALL, a backend exits both the dynamic dedicated session and forced dynamic dedicated session.

F.67.2. Considerations and Limitations #

This section provides a list of considerations and limitations that must be taken into account when using proxima.

The proxima extension does not manage replication between cluster nodes. To provide data consistency on all nodes, either use biha, or configure replication manually.
IPv6 is not supported.
Elbrus, ppc64le, and s390x CPU architectures are not supported.
When used in the BiHA cluster, proxima registers a set of callbacks to be informed about events in the cluster. The callbacks are located in the biha.callbacks table of the biha_db database on BiHA cluster nodes. Do not delete these callbacks, otherwise proxima cannot react to the cluster events.
Currently, proxima has no mechanism to automatically establish dynamic dedicated session when you use extensions that keep their state within a session. For such cases, you must manually enable the forced dynamic dedicated session.
To enable the connection pooler to process a large number of client connections, you must set the limit of open files higher than the target number of connections:
```
sudo echo '* soft nofile {value}' >> /etc/security/limits.conf
sudo echo '* hard nofile {value}' >> /etc/security/limits.conf
ulimit -n {value}
```
For example, if the target number of client connections is 10000, set {value} to 11000.

F.67.3. Managing proxima #

This section describes how to install, configure, and use proxima.

F.67.3.1. Installation and Configuration #

The proxima extension is a built-in extension included into Postgres Pro Enterprise. To enable and configure proxima, perform the following steps:

Stop your Postgres Pro Enterprise server:
```
pg_ctl stop -D PGDATA_directory
```
Add proxima to shared_preload_libraries in postgresql.conf:
```
shared_preload_libraries = 'proxima'
```
If you have a multi-node cluster, repeat this step on every node.
Depending on your Postgres Pro Enterprise configuration, specify the proxima configuration parameters in postgresql.conf:
- For a single-node server, set the following configuration parameter:
```
proxima.cluster_mode = 'standalone'
```
- For a standard primary-standby cluster, set the following configuration parameters:
```
proxima.cluster_mode = 'guc'
proxima.cluster_config = 'node0_id,node0_address,node0_port,node0_role;node1_id,node1_address,node1_port,node1_role;
proxima.cluster_node_id = node_id
```
  For example:
```
proxima.cluster_mode = 'guc'
proxima.cluster_config = '0,192.168.0.57,4590,P;1,192.168.0.77,4590,S;'
proxima.cluster_node_id = 0
```
  Ensure that the proxima.cluster_config values are the same on all nodes and proxima.cluster_node_id is unique for each node.
- For a BiHA cluster, set the following configuration parameter:
```
proxima.cluster_mode = 'biha'
```
  When using proxima with biha, specifying cluster configuration parameters is not required as proxima receives this information directly from biha.
(Optional) If required, set other configuration parameters.
If you do not specify configuration parameters, proxima uses the default values. For more information about configuration parameters and their default values, see Section F.67.4.
Start the node:
```
pg_ctl start -D PGDATA_directory
```

F.67.3.2. Configuring SSL #

Configure proxima to operate with SSL connections.

Using the OpenSSL utility, generate a certificate and a private key:

openssl req -x509 -newkey rsa:4096 -keyout path_to_key -out path_to_certificate -sha256 -days period_of_validity -nodes -subj "/CN=certificate_domain"

For example:

openssl req -x509 -newkey rsa:4096 -keyout ./key.pem -out ./cert.pem -sha256 -days 256 -nodes -subj "/CN=localhost"

In postgresql.conf, specify paths to the generated certificate and private key:
```
proxima.ssl_key = '/path/to/key.pem'
proxima.ssl_cert = '/path/to/cert.pem'
```
If you do not specify the proxima.ssl_cert and proxima.ssl_key parameters, proxima uses a certificate and a key configured for Postgres Pro in ssl_cert_file and ssl_key_file.
Enable SSL for proxima by setting the proxima.ssl_enabled to on:
```
proxima.ssl_enabled = on
```

All client connections to the proxima port are now secured with SSL.

F.67.3.3. Managing Dynamic Dedicated Session Manually #

Some extensions keep their state within a session, which requires to retain the session between a client and a backend. If you use such extensions, you must manually enable the dynamic dedicated session at every connection.

To enable the forced dynamic dedicated session:

SET proxima.force_dedicated = true;

There is no need to disable the forced dynamic dedicated session.

F.67.3.4. Removing proxima #

To stop using proxima:

On all nodes of the cluster, remove the extension from the shared_preload_libraries in postgresql.conf.
Restart the nodes using pg_ctl.

F.67.4. Configuration Parameters #

The proxima extension parameters are set in the postgresql.conf configuration file.

F.67.4.1. Common Parameters #

proxima.address (text) #: Specifies the IP address of the network interface that accepts incoming connections. If set to 0.0.0.0, the incoming connections are accepted through all network interfaces of the host. The default value is 0.0.0.0. The value of this parameter must be the same on all nodes of the cluster.
proxima.port (integer) #: Specifies the TCP port where proxima accepts incoming client connections. The value can be set in the range from 0 to 65535. The default value is 4545. It is recommended to set the same proxima.port on all nodes of the cluster.
proxima.ssl_enabled (boolean) #: Enables or disables SSL for incoming client connections on proxima.port. The default value is off. If you enable SSL without specifying proxima.ssl_key and proxima.ssl_cert, proxima will use the SSL certificate configured for Postgres Pro.
proxima.ssl_cert (text) #: Specifies the path to the certificate that will be used for SSL connection via proxima.port. If not set, proxima uses the certificate specified in ssl_cert_file. If you set this parameter, you must also set proxima.ssl_key.
proxima.ssl_key (text) #: Specifies the path to the private key file. If not set, proxima uses the key specified in ssl_key_file. If you set this parameter, you must also set proxima.ssl_cert.
proxima.log_level (enum) #: Sets the log level for proxima messages. The following values are available: error, warning, info, verbose, debug. The default value is info.
proxima.workers (integer) #: Sets the number of threads that are launched for processing queries by the proxima background process. The value can be set in the range from 1 to 65535. The higher the value, the more queries can be processed. The default value is 4.

F.67.4.2. Cluster Configuration #

proxima.cluster_mode (text) #

Sets the cluster operation mode. The following modes are available:

standalone mode for a single-node Postgres Pro database.
guc mode for a standard Postgres Pro primary-standby cluster. The cluster configuration is set by the proxima.cluster_config and proxima.cluster_node_id parameters.
biha mode for a BiHA cluster. In this mode, cluster configuration is received from biha.

The default value is standalone. The value of this parameter must be the same on all nodes of the cluster.

proxima.cluster_config (text) #

Sets cluster node configuration for the guc cluster operation mode. The value of this parameter must be the same on all nodes of the cluster. The parameters for connection and identification are specified for each node as follows:

'node_id,node_address,node_port,node_role'

where:

node_id is the ID of the node specified as a value from 0 to 255. The node ID must be unique for each node of the cluster.
node_address is the IP address of the node for internal connection between cluster nodes.
node_port is the node port for internal connection between cluster nodes. This port must be different from proxima.port.
role is the role of the node in the cluster. The available values are P (Primary) and S (Standby).

Here is an example of the proxima.cluster_config value for a three-node cluster:

'0,127.0.0.1,4090,P;1,127.0.0.1,4091,S;2,127.0.0.1,4092,S;'

proxima.cluster_node_id (integer) #

Sets the unique node ID for the guc cluster operation mode. This parameter must not be changed once the cluster has started and until it is stopped. The node ID must be unique for each node of the cluster. The value can be set in the range from 0 to 255. The default value is 0.

The list of node IDs must always start with 0 and end with n-1, where n is the total number of nodes. For example, for a three-node cluster, the node IDs must be 0, 1, 2.

F.67.4.3. Configuration of the Local Backend Pool #

The following parameters set limits for the pool of local backends — Postgres Pro processes responsible for processing client queries.

proxima.backend_pool_local_total_limit (integer) #: Sets the maximum number of backends that can be created on the node. The value can be set in the range from 1 to MAX_BACKENDS. The default value is 100.
proxima.backend_pool_local_bucket_limit (integer) #: Sets the maximum number of backends that can be created within connections identified by the user + database binding. The value can be set in the range from 1 to the value set in proxima.backend_pool_local_total_limit. The default value is 100.
proxima.backend_pool_local_overdue_interval (float) #: Sets the local backend expiry time, in seconds. If a local backend is not used by clients longer than set in this parameter, the backend process will be stopped. The value can be set in the range from 1.0 to 86400.0. The default value is 10.0.

F.67.4.4. Configuration of the Remote Backend Pool #

The following parameters set limits for the pool of remote backends — logical channels that can be established through a multiplexed connection between cluster nodes to proxy queries from a standby (follower) node to the primary (leader) node. Configuring these parameters is reasonable only for multi-node clusters. The default values are optimal, and it is not recommended to change them.

proxima.backend_pool_remote_total_limit (integer) #: Sets the maximum number of logical connection channels between cluster nodes to proxy client queries. The value can be set in the range from 1 to 2^32-1. The default value is 100000.
proxima.backend_pool_remote_bucket_limit (integer) #: Sets the maximum number of logical connection channels between cluster nodes to proxy client queries within connections identified by the user + database binding. The value can be set in the range from 1 to the value set in proxima.backend_pool_remote_total_limit. The default value is 1000.
proxima.backend_pool_remote_overdue_interval (float) #: Sets the remote backend expiry time, in seconds. If a remote backend is not used by clients longer than set in this parameter, the logical connection channel between cluster nodes will be closed. The value can be set in the range from 1.0 to 86400.0. The default value is 60.0.

Prev	Up	Next
F.66. postgres_fdw — access data stored in external Postgres Pro servers	Home	F.68. ptrack — a block-level incremental backup engine for Postgres Pro

epub pdf