Hello, do someone has already installed odoo with pgcat ?
the repository is on github postgresml/pgcat
Pgcat is a PostgreSQL pooler and proxy (like pgbouncer) with support for sharding, load balancing, failover and mirroring, with Multi-threaded runtime
The big benefit of PgCat is multi-threaded. If you use pgbouncer at scale, you very likely have run into the problem that pgbouncer is ultimately a single-threaded asynchronous connection based pooler.
It is pretty good at handling a lot of connections, but if you have a lot of activities for individual connections, that's where you'll run into bottlenecks. PgCat don't have this issue.
I have tried it with success (work very well), my config is in the comment below.
The dev version usually has some errors with odoo, the version that currently works for me is 1.1.1 and 1.1.0
some features
| Feature | Status | Comments |
|---|---|---|
| Transaction pooling | Stable | Identical to PgBouncer with notable improvements for handling bad clients and abandoned transactions. |
| Session pooling | Stable | Identical to PgBouncer. |
| Multi-threaded runtime | Stable | Using Tokio asynchronous runtime, the pooler takes advantage of multicore machines. |
| Load balancing of read queries | Stable | Queries are automatically load balanced between replicas and the primary. |
| Failover | Stable | Queries are automatically rerouted around broken replicas, validated by regular health checks. |
| Admin database statistics | Stable | Pooler statistics and administration via the pgbouncer and pgcat databases. |
| Prometheus statistics | Stable | Statistics are reported via a HTTP endpoint for Prometheus. |
| SSL/TLS | Stable | Clients can connect to the pooler using TLS. Pooler can connect to Postgres servers using TLS. |
| Client/Server authentication | Stable | Clients can connect using MD5 authentication, supported by libpq and all Postgres client drivers. PgCat can connect to Postgres using MD5 and SCRAM-SHA-256. |
| Live configuration reloading | Stable | Identical to PgBouncer; all settings can be reloaded dynamically (except host and port). |
| Auth passthrough | Stable | MD5 password authentication can be configured to use an auth_query so no cleartext passwords are needed in the config file. |
| Sharding using extended SQL syntax | Experimental | Clients can dynamically configure the pooler to route queries to specific shards. |
| Sharding using comments parsing/Regex | Experimental | Clients can include shard information (sharding key, shard ID) in the query comments. |
| Automatic sharding | Experimental | PgCat can parse queries, detect sharding keys automatically, and route queries to the correct shard. |
| Mirroring | Experimental | Mirror queries between multiple databases in order to test servers with realistic production traffic. |
THIS CONFIG WORK FOR ME with docker container pgcat 1.1.1 and postgres installed in the host
# in postgresql.conf use
listen_addresses = '*'
# and in pg_hba.conf
host all all 172.19.0.0/16 md5
#################################################
[general]
host = "172.19.0.250" # or "0.0.0.0" ip for client login
port = 6431 # port for client login
worker_threads = 10
autoreload = 15000
server_tls = false
verify_server_certificate = false
#statement_timeout = 0
idle_client_in_transaction_timeout = 0 # milliseconds
# How long to wait before aborting a server connection (ms).
connect_timeout = 5000 # milliseconds
# How long an idle connection with a server is left open (ms).
idle_timeout = 30000 # milliseconds
# Admin like postgres
admin_username = "odoo"
admin_password = "SecurePasswd"
[pools.odoo.users.1] # [pools.database.users.(user_index)] this case is user 1
username = "odoo"
password = "SecurePasswd"
pool_size = 100
pool_mode = "transaction"
[pools.odoo.shards.0] # [pools.database.shards.(shard_id)] this case I use 0 for my localhost
servers = [
["172.19.0.1", 5432, "primary"]]
database = "odoo"
[pools.database1.users.1] # user 1
username = "odoo"
password = "SecurePasswd"
pool_size = 100
pool_mode = "transaction"
[pools.database1.shards.0]
servers = [
["172.19.0.1", 5432, "primary"]]
database = "database1"
[pools.database2.users.2] # for user 2
username = "user2"
password = "SecurePasswd"
pool_size = 100
pool_mode = "transaction"
[pools.database2.shards.0] # id for localhost
servers = [
["172.19.0.1", 5432, "primary"]]
database = "database2"
[pools.database3.users.2] # user 2
username = "user2"
password = "SecurePasswd"
pool_size = 100
pool_mode = "transaction"
[pools.database3.shards.0]
servers = [
["172.19.0.1", 5432, "primary"]]
database = "database3"
[pools.database4.users.3] # user 3
username = "user3"
password = "SecurePasswd"
pool_size = 100
pool_mode = "transaction"
[pools.database4.shards.0]
servers = [
["172.19.0.1", 5432, "primary"]]
database = "database4"
###################################################
New version of pgcat 1.2.0 work nice
my config
THIS CONFIG WORK FOR ME pgcat 1.2.0 in the host and postgres installed in the host
# in postgresql.conf use
listen_addresses = '127.0.0.1'
# and in pg_hba.conf
host all all 127.0.0.1/32 md5
for docker
host = "172.19.0.1" # custom docker network ip for client login
port = 8431 # port for client login