Connection pool

Connection Pool creation

The connection pool to create must be specified in the openproxy.toml file.

simple_db connection pool creation

Before creating a connection pool, create a PostgreSQL database to use. some_db database is created in this example.

Connection Pool to create : simple_db

• Create a section [pools.simple_db] in openproxy.toml.

  [pools.simple_db]
  pool_mode = "session"
  query_parser_enabled = true
  query_parser_read_write_splitting = true
  primary_reads_enabled = true
  sharding_function = "pg_bigint_hash"

Set Connection Pool user information

• Create a section [pools.simple_db.users.0] in openproxy.toml.

  [pools.simple_db.users.0]
  username = "simple_user"
  password = "simple_user"
  pool_size = 5
  statement_timeout = 30000

List the cluster address and database to connect to

• Create a section in openproxy.toml called [pools.simple_db.shard.0].

  [pools.simple_db.shards.0]
  servers = [
    [ "opensql1", 5432, "Auto", ],
    [ "opensql2", 5432, "Auto", ],
    [ "opensql3", 5432, "Auto", ],
  ]
  database = "some_db"
  use_patroni = true

Full example of a simple_db connection pool creation configuration file

[pools.simple_db]
pool_mode = "session"
default_role = "primary"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true
sharding_function = "pg_bigint_hash"
prepared_statements_cache_size = 500

[pools.simple_db.users.0]
username = "simple_user"
password = "simple_user"
pool_size = 5
statement_timeout = 30000

[pools.simple_db.shards.0]
servers = [
  [ "opensql1", 5432, "Auto", ],
  [ "opensql2", 5432, "Auto", ],
  [ "opensql3", 5432, "Auto", ],
]
database = "some_db"
use_patroni = true

---

OpenProxy Execution

Execute the OpenProxy with the openproxy.toml file that sets up the connection pool to create.

---

Connection Pool Verification

Check the Connection Pool created by psql -h 127.0.0.1 -p 6432 -d openproxy -U postgres command.

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
(1 rows)

openproxy=> show databases;
             name             |    host     | port | database |  force_user   | pool_size | min_pool_size | reserve_pool |  pool_mode  | max_connections | current_connections | paused | disabled 
------------------------------+-------------+------+----------+---------------+-----------+---------------+--------------+-------------+-----------------+---------------------+--------+----------
 simple_db_shard_0_replica_0  | 178.176.0.4 | 5432 | some_db  | simple_user   |         5 |             0 |            0 | session     |               5 |                   0 |      0 |        0
 simple_db_shard_0_replica_1  | 178.176.0.2 | 5432 | some_db  | simple_user   |         5 |             0 |            0 | session     |               5 |                   0 |      0 |        0
 simple_db_shard_0_primary    | 178.176.0.3 | 5432 | some_db  | simple_user   |         5 |             0 |            0 | session     |               5 |                   0 |      0 |        0
 (3 rows)