Docs Home

OpenProxy

Overview

This section explains how to set up and run OpenProxy, which handles OpenSQL’s connection pooling, load balancing, and virtual IP failover functions.

Environment configuration

This document lists the values that can be set when OpenProxy is run. If you want to run OpenProxy with a configuration file of your choice, enter the path to the configuration file after the OpenProxy command.

General Settings

These are the values declared in [general]. The values set in [general] set network settings, such as port, and the admin name and password for OpenProxy.

Variable name
Type
Default
Description

host

String

0.0.0.0

The host address bound when the OpenProxy process starts.

port

Number

6432

It is the port bound when the OpenProxy process starts.

worker_threads

Number

5

This is the number of worker threads in the asynchronous runtime that are launched when the program is executed. It is recommended to set this to match the number of CPU cores.

connect_timeout

Number

1000

Sets the timeout applied when connecting to the PostgreSQL server. The unit is milliseconds (ms).

idle_timeout

Number

600000

Specifies the idle timeout for connections created to the PostgreSQL server. The unit is milliseconds (ms). If the server connection is not used during this time, the connection is terminated.

server_lifetime

Number

3600000

Specifies the maximum lifetime of connections created to the PostgreSQL server. The unit is milliseconds (ms). Connections created will be terminated after this time, even if they are still in use.

idle_client_in_transaction_timeout

Number

0

Specifies the maximum idle timeout value for client transactions. The unit is milliseconds (ms). If set to 0, the timeout is not applied.

healthcheck_timeout

Number

1000

The timeout for health check messages sent to the PostgreSQL server is in milliseconds. If the PostgreSQL server does not respond within that time, OpenProxy bans the server from the pool and does not send any queries.

healthcheck_delay

Number

30000

Specify the interval for performing health checks on the PostgreSQL server. The unit is milliseconds (ms). If there is no activity on the server during this time, a health check is performed.

shutdown_timeout

Number

60000

When the OpenProxy process receives a SIGINT signal and shuts down gracefully, if there are any connected clients, it waits for the client connections to terminate during that time, which is measured in milliseconds.

ban_time

Number

60

If a PostgreSQL server fails the health check, it will be banned from the pool for the specified time in seconds. Once the banned PostgreSQL server has been banned for the specified time, it will be included in the pool again and become subject to the health check.

tcp_keepalives_idle

Number

5

If the TCP socket that maintains the connection created to the PostgreSQL server is idle for the specified period of time, it begins sending keepalive packets periodically to check the status. The unit is seconds (sec).

tcp_keepalives_interval

Number

5

The unit for the interval at which keepalive packets are sent is seconds (sec).

tcp_keepalives_count

Number

5

If the PostgreSQL server does not receive a response to the specified number of Keepalive packets sent periodically, the TCP connection is terminated.

auth_type

Enum

md5

Specifies the PostgreSQL password authentication method to use for client authentication. Supports the md5 and scram-sha-256 options.

prepared_statements_cache_size

Number

0

This option is only valid when using Transaction mode Pooling. It enables a global cache to store Prepared Statements sent from the Client and specifies the size of the cache. This option must be enabled to process Prepared Statements in Transaction pool mode. Prepared statements use OpenProxy memory and PostgreSQL resources, so it is recommended not to set this value too large.

admin_username

String

-

This is the admin username for managing OpenProxy

admin_userpassword

String

-

This is the password for the admin user used to manage OpenProxy.

server_tls

Bool

false

The TLS connection to the PostgreSQL server is enabled in OpenProxy. PostgreSQL must also be configured to accept TLS connections.

verify_server_certificate

Bool

false

If server_tls is enabled, verify that the server certificate is valid. Disallow connections to “self signed certificates” that are not stored in the root store where Openproxy is running.

renew_interval

Number

5000

Specifies the period for reloading TOML configuration files and updating the primary/replica status of nodes in shards managed by Patroni..

reload_toml

Bool

true

Specifies the period for reloading TOML configuration files and updating the primary/replica status of nodes in shards managed by Patroni.

dns_cache_enabled

Bool

false

If enabled, resolves and caches the DNS for the Openproxy PostgreSQL server (overriding the default TTL provided by system DNS servers). This is useful when routing PostgreSQL servers via DNS. If the cached value obtained from a DNS query has changed, the connection pool automatically creates a new connection to the new PostgreSQL server.

dns_mas_ttl

Number

30

The time for which cached DNS values are stored. If they expire, DNS refresh begins.

Virtual Router

Set this under the [general.virtual_router] section if you want to enable OpenProxy's virtual IP-related features. If not, remove the section.

Variable name
Type
Default
Description

interface

String

-

The network interface name of the host on which to register the virtual IP.

router_id

Number

-

Virtual router IDs can have values from 1 to 255. They are used to distinguish virtual router clusters within the same network.

priority

Number

-

The priority value of the virtual router ranges from 0 to 255.

advert_int

Number

-

The interval is in seconds. VRRP advertisement packets are sent at the specified interval.

vip_addresses

Array

-

List of virtual IP addresses to be occupied.

The format should be [“192.168.35.200/24“,”192.168.35.201/24"], separated by commas, and IPv4 addresses including the netmask bit length must be provided.

pre_promote_script

String

-

Optional.

You can specify the OS command to execute before virtual IP occupation when BACKUP→MASTER promotion occurs.

pre_demote_script

String

-

Optional.

You can specify the OS command to execute before releasing the virtual IP when MASTER→BACKUP demotion occurs.

unicast_peers

Array

-

Optional. If the network environment does not support multicast, VRRP packets can be sent in unicast mode, and the IPv4 addresses of all peer virtual router nodes are entered in an array format separated by commas, such as [“192.168.0.6", “192.168.0.8”].

Pools

[pools.{pool_name}]

If you want to add a new connection, add it to [pools].

Variable name
Type
Default
Description

pool_mode

Enum

transaction

The pooling mode of OpenProxy is set in this pool unit and supports session mode and transaction mode. In session mode, one PostgreSQL server connection is provided for each client connection, while in transaction mode, each client transaction is divided and processed across multiple PostgreSQL server connections.

load_balancing_mode

Enum

random

Specifies the load balancing algorithm for replica nodes and supports random and loc. Random uses a random number generator to decide which replica to use. Loc selects the replica with the fewest connections being processed.

query_parser_enabled

Bool

false

Using the Rust library sqlparser, all queries requested by OpenProxy are parsed. This variable is used by the pooler to separate queries into read/write or extract sharding keys.

query_parser_read_write_splitting

Bool

false

When enabled, read queries are assigned to standby and write queries are assigned to primary.

primary_reads_enabled

Bool

false

When query_parser_enabled and query_parser_read_write_splitting are enabled together, read queries are distributed to the primary as well as the replica.

idle_timeout

Number

-

Reset the idle_timeout value in General settings for this pool.

connect_timeout

Number

-

Reset the connect_timeout value in General settings for this Pool unit.

Users

Openproxy's Connection pool can be used by registering multiple users.

User configuration allows for user-specific settings and further overrides of general and pool settings.

Register user-related entries in the [pools.{pool_name}.users.0] sub-entry.

Variable name
Type
Default
Description

username

String

-

The username

password

String

-

The user's password. Only MD5 authentication is supported, so the client must provide a password that matches this.

pool_size

Number

-

The maximum number of connections to PostgreSQL.

min_pool_size

Number

0

This is the minimum number of PostgreSQL connections to keep open in the pool. Specifying this value reduces the cold start time when new clients connect. Setting this value too high relative to the load can increase the number of PostgreSQL connections, wasting server resources and preventing other pools from using them.

statement_timeout

Number

0

The maximum time (in milliseconds) to wait for the server to respond to the client's query. This feature is implemented in the PostgreSQL server and is not normally used, but can be used if PostgreSQL is unstable.

pool_mode

Enum

-

Resets the pool_mode value of [pools.pool_name].

server_lifetime

Number

-

Reset the server_lifetime value in General settings.

Shards

You need to define the database server address to connect to the pools.{pool_name}.shards.0 sub-section.

Variable name
Type
Default
Description

database

String

None

This is the name of the database connected to PostgreSQL.

servers

Array

None

Set the cluster db-server address information to be accessed in array format. host/IP, port, role (primary, replica, Auto)

(Example)

servers = [ ["10.0.0.1", 5432,

"primary"],

["replica-1.internal- dns.net", 5432, "replica"],

]

use_patroni

Bool

false

To use vip-failover, set it to true.

Example of Configuration 

[general]
port = 6432
admin_username = "postgres"
admin_password = "postgres"
autoreload = 5000
worker_threads = 3

[pools.postgres]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true

[pools.postgres.users.0]
username = "postgres"
password = "postgres"
server_username = "postgres"
server_password = "postgres"
pool_size = 10
statement_timeout = 0

[pools.postgres.shards.0]
database = "postgres"
servers = [
  ["172.176.0.2", 5432, "auto"],
  ["172.176.0.3", 5432, "auto"],
  ["172.176.0.4", 5432, "auto"]
]
use_patroni = true

## virtual-ip
[general.virtual_router]
interface = "eth0"
router_id = 50
priority = 50
advert_int = 3
vip_addresses = ["178.176.0.200/24"]

The example setup above makes the following assumptions.

  • The “my_database” pooler you set up runs on the same machine as PostgreSQL.

  • postgres database is defined.

  • User: developer, password: very-security-password has CONNECT privileges in the postgres database.

Execution

Execute with CLI

Execute the installed openproxy binary as shown below. You can specify the path to the toml configuration file as an argument, and if no value is given, it defaults to the openproxy.toml file in the execution path.

$ ./openproxy /home/myUser/openproxy.toml

The following options can be specified as command line arguments.

$ openproxy --help
OpenProxy: PostgreSQL pooler for OpenSQL - fork of pgCat

Usage: openproxy [OPTIONS] [CONFIG_FILE]

Arguments:
  [CONFIG_FILE]  [env: CONFIG_FILE=] [default: openproxy.toml]

Options:
      --log-target <LOG_TARGET>
          [env: LOG_TARGET=] [default: both] [possible values: file, stdout, both]
  -l, --log-level <LOG_LEVEL>
          [env: LOG_LEVEL=] [default: INFO]
      --max-logfile-num <MAX_LOGFILE_NUM>
          [env: MAX_LOGFILE_NUM=] [default: 5]
      --log-dir <LOG_DIR>
          [env: LOG_DIR=] [default: logs]
      --revision
          Print revision number
  -F, --log-format <LOG_FORMAT>
          [env: LOG_FORMAT=] [default: text] [possible values: text, structured, debug]
  -h, --help
          Print help
  -V, --version
          Print version

  • --log-target : Specifies the target pipeline for outputting log entries written by the OpenProxy process. Supported options are file, stdout, and both.

  • -l , --log-level : Defines the log level to be written. Supported levels are ERROR, WARN, INFO, DEBUG, and TRACE .

  • --max-logfile-num: Specifies the maximum number of log files to keep. Excess log files are automatically deleted from disk.

  • --log-dir: Enter the directory path to write log files to.

  • --log-format: Specifies the format of the log entries to be written. text, structured, debug option is also supported.

The above options can also be defined as environment variables. If environment variables and command line options are given together, the command line options take precedence.

  • LOG_TARGET : Corresponds to the log target.

  • LOG_LEVEL : Corresponds to the log level definition

  • MAX_LOGFILE_NUM : Corresponds to the maximum number of log files to keep.

  • LOG_DIR : Corresponds to the directory path to write log files to.

  • LOG_FORMAT : Corresponds to the log entry format.

In addition to this, you can print help or view version or revision information

$ openproxy --help

$ openproxy --version
openproxy 0.0.2-OpenSQL-dev

$ openproxy --revision
revision number: 623

Defining it as a Systemd service

[Unit]
Description=OpenProxy connection pooler - fork of postgresml's pgcat
After=network.target
StartLimitIntervalSec=0

[Service]
User=opensql
Type=simple
Restart=always
RestartSec=1
Environment=LOG_LEVEL=info
LimitNOFILE=65536
AmbientCapabilities=CAP_NET_ADMIN CAP_NET_RAW
ExecStart=/usr/bin/openproxy /etc/openproxy.toml

[Install]
WantedBy=multi-user.target

View function

You can utilize the administrator features by connecting to the OpenProxy node with the PostgreSQL user admin using the openproxy database name. It provides functions such as viewing configuration and currently active pools, checking stat information, etc.

$ psql -h 127.0.0.1 -p 6432 -d openproxy -U postgres
openproxy=> SHOW HELP;
NOTICE:  Console usage
DETAIL:  
	SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
	SHOW LISTS
	SHOW STATS
	SET key = arg
	RELOAD
	PAUSE [<db>, <user>]
	RESUME [<db>, <user>]
	SHUTDOWN

View Version 

openproxy=> SHOW VERSION;
         version         
-------------------------
 OpenProxy 1.0.0
(1 row)

View Configuration 

openproxy=> SHOW CONFIG;
                       key                        |     value      | default | changeable 
--------------------------------------------------+----------------+---------+------------
 idle_client_in_transaction_timeout               | 30000          | -       | yes
 pools."postgres".users                           | postgres       | -       | yes
 prometheus_exporter_port                         | 9930           | -       | yes
 pools.postgres.query_parser_max_length           | unlimited      | -       | yes
 pools.postgres.load_balancing_mode               | random         | -       | yes
 pools."postgres".shard_count                     | 1              | -       | yes
 port                                             | 6432           | -       | no
 connect_timeout                                  | 1000           | -       | no
 healthcheck_timeout                              | 1000           | -       | yes
 pools.postgres.default_role                      | any            | -       | yes
 healthcheck_delay                                | 30000          | -       | yes
 pools.postgres.query_parser_read_write_splitting | true           | -       | yes
 pools.postgres.sharding_function                 | pg_bigint_hash | -       | yes
 ban_time                                         | 60             | -       | yes
 pools.postgres.pool_mode                         | transaction    | -       | yes
 shutdown_timeout                                 | 60000          | -       | yes
 host                                             | 0.0.0.0        | -       | no
 pools.postgres.primary_reads_enabled             | false          | -       | yes
 idle_timeout                                     | 600000         | -       | yes
 pools.postgres.query_parser_enabled              | true           | -       | yes
(20 rows)

View Database 

openproxy=> SHOW DATABASES;
            name            |      host      | port | database | force_user | pool_size | min_pool_size | reserve_pool |  pool_mode  | max_connections | current_connections | paused | disabled 
----------------------------+----------------+------+----------+------------+-----------+---------------+--------------+-------------+-----------------+---------------------+--------+----------
 postgres_shard_0_primary   | 192.168.131.12 | 5432 | postgres | postgres   |      1100 |             0 |            0 | transaction |            1100 |                   0 |      0 |        0
 postgres_shard_0_replica_0 | 192.168.131.13 | 5432 | postgres | postgres   |      1100 |             0 |            0 | transaction |            1100 |                   0 |      0 |        0
 postgres_shard_0_replica_1 | 192.168.131.14 | 5432 | postgres | postgres   |      1100 |             0 |            0 | transaction |            1100 |                   0 |      0 |        0
(3 rows)

View Pool

openproxy=> SHOW POOLS;
 database  |    user     |  pool_mode  | cl_idle | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us 
-----------+-------------+-------------+---------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------
 simple_db | simple_user | session     |       0 |         0 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0
 postgres  | postgres    | transaction |       0 |         0 |          0 |             0 |         0 |       3 |       0 |         0 |        0 |       0 |          0
(2 rows)

View Client 

openproxy=> SHOW CLIENTS;
 client_id  | database |   user   | application_name | state | transaction_count | query_count | error_count | age_seconds | maxwait | maxwait_us 
------------+----------+----------+------------------+-------+-------------------+-------------+-------------+-------------+---------+------------
 0x43332945 | postgres | postgres | openproxy        | idle  |                 8 |           8 |           0 |           9 |       0 |          8
 0x993A6EA2 | postgres | postgres | openproxy        | idle  |                10 |          10 |           0 |           9 |       0 |          8
 0x09CFDA65 | pgcat    | postgres | psql             | idle  |                 0 |           0 |           0 |         208 |       0 |          0
 0x1E5A084D | postgres | postgres | openproxy        | idle  |                10 |          10 |           0 |           4 |       0 |          9
(4 rows)

View Server 

openproxy=> SHOW SERVERS;
 server_id  | database_name |   user   |        address_id        |           application_name           | state | transaction_count | query_count | bytes_sent | bytes_received | age_seconds | prepare_cache_hit | prepare_cache_miss | prepare_cache_eviction | prepare_cache_size 
------------+---------------+----------+--------------------------+--------------------------------------+-------+-------------------+-------------+------------+----------------+-------------+-------------------+--------------------+------------------------+--------------------
 0x342F491B | postgres      | postgres | postgres_shard_0_primary | DBeaver 22.3.4 - SQLEditor <Console> | idle  |                41 |          41 |      18760 |         302727 |          25 |               123 |                 82 |                      0 |                 41
(1 row)

View User 

openproxy=> SHOW USERS;
   name   |  pool_mode  
----------+-------------
 postgres | transaction
(1 row)

View List

openproxy=> SHOW LISTS;
     list      | items 
---------------+-------
 databases     |     4
 users         |     2
 pools         |     4
 free_clients  |     4
 used_clients  |     0
 login_clients |     0
 free_servers  |     1
 used_servers  |     0
 dns_names     |     0
 dns_zones     |     0
 dns_queries   |     0
 dns_pending   |     0
(12 rows)

View Stat 

openproxy=> SHOW STATS;
          instance          | database |   user   | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | total_errors | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_errors | avg_xact_time | avg_query_time | avg_wait_time 
----------------------------+----------+----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+--------------+----------------+-----------------+----------+----------+------------+---------------+----------------+---------------
 postgres_shard_0_primary   | postgres | postgres |               41 |                41 |         302727 |      18760 |               0 |               39 |           14152 |            0 |              0 |               0 |        0 |        0 |          0 |             0 |              0 |             0
 postgres_shard_0_replica_0 | postgres | postgres |                0 |                 0 |              0 |          0 |               0 |                0 |               0 |            0 |              0 |               0 |        0 |        0 |          0 |             0 |              0 |             0
 postgres_shard_0_replica_1 | postgres | postgres |                0 |                 0 |              0 |          0 |               0 |                0 |               0 |            0 |              0 |               0 |        0 |        0 |          0 |             0 |              0 |             0
(3 rows)

PreviousPatroniNextTmax OpenSQL Administration

Last updated