Killing idle postgres processes

postgres

8 Replies

16682 Views

Odoo does not close idle postgresql processes by default. Is there a way to close idle postgres processes (without restarting odoo service)?

Axel Mendoza

Best Answer

You could do it using pg_bouncer as connection pooling in front of your postgresql database and config odoo to connect to it. Pg_bouncer could close the idle transactions for you by setting a timeout for the idle connections that it's managed. Also it could increase your connections number for Odoo and others postgresql consumers by a really big number difference in compared with the real postgresql connections

Mario Gielissen

Author

Thanks, I'm currently testing it. Has pgbouncer any performance impact on Odoo?

Axel Mendoza

normally an improvement of Odoo performance because of the pg_bouncer connection pooling. I use transaction timeout to close the idle queries. The only thing could be when you are using pg_bouncer to pool all your database and you try to create a new database through pg_bouncer, that is not working completely in my setup

Mario Gielissen

Author

It seems to work, after around 10 minutes the idle processes are gone.

Mario Gielissen

Author

I can create a new database without problems.

Axel Mendoza

Could you share your pg_bouncer config that allow you to create and proxy new databases?. It have been a while since I mount it and maybe I just dont config something needed for that

Mario Gielissen

Author

[databases]

* = port=5432

[pgbouncer]

logfile = /var/log/postgresql/pgbouncer.log

pidfile = /var/log/postgresql/pgbouncer.pid

listen_addr = *

listen_port = 6432

unix_socket_dir = /var/run/postgresql

auth_type = trust

auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

stats_users = postgres

pool_mode = transaction

server_reset_query = DISCARD ALL;

server_check_query = select 1

server_check_delay = 10

max_client_conn = 1000

default_pool_size = 20

log_connections = 1

log_disconnections = 1

Enjoying the discussion? Don't just read, join in!

Create an account today to enjoy exclusive features and engage with our awesome community!

Related Posts	Replies	Views
In debian package v8, can't set database name postgres	3 feb. 24	6848
Odoo 11 is slow to backup because of multiple postgres processes postgres	0 feb. 21	2791
Serverless Postgres development postgres	2 nov. 25	553
How to calculate database growth in postgres? Solved postgresql postgres	2 okt. 23	3248
createuser: could not connect to database postgres: could not connect to server: No such file or directory. postgres odoov11	1 juni 23	6976

This question has been flagged

Enjoying the discussion? Don't just read, join in!