postgresql connection manage pgbouncer pgcat - ghdrako/doc_snipets GitHub Wiki

Connection pools

A connection pool is a cache of database connections, usually local to a specific process. Its main advantage is improved performance – there’s a certain amount of overhead inherent to opening a new database connection in both the client and the server. After finishing with a connection, by checking it back into a pool instead of discarding it, the connection can be reused next time one is needed within the application. Connection pooling is built into many database adapters

HAProxy or another load balancer

PgCat pooler with sharding

PgCat is a multi-threaded proxy written in Rust using the Tokio asynchronous runtime with fail-over and load balancing

PgBouncer

Pgbouncer is a single-threaded proxy written in C based on libevent.

pgBouncer is an open-source connection pooling application that helps manage connection usage for high-traffic applications. PostgreSQL creates one new process (not thread) per connection.

pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.

Benefits

  • Your client connections may now exceed the configured max_connections value.
  • Connections can be held open, even if the database server is temporarily unavailable. Processing is delayed but not refused.
  • A connection pooler re-uses connections more efficiently and offers various pool_modes with different trade-offs.
  • Besides performance, connection poolers can help with maintenance and upgrades, by holding connections open during cutovers.

pgBouncer działa jako niezależny proxy po stronie klienta, który jest odpowiedzialny za zarządzanie połączeniami dla wielu klientów i aplikacji. Rożni sie to w porównaniu z bibliotekami używanymi wewnątrz aplikacji typu HikariCP dla java które służą do pulowania połączeń wewnątrz aplikacji. pgBouncer może działać jednocześnie z biblioteka do pulowania.

Pool modes

The pool mode specifies how a server connection may be re-used by clients.

It has a few modes of operation:

  • Session pooling: A connection is assigned when a client opens a connection and unassigned when the client closes it.
  • Transaction pooling: Connections are assigned only for the duration of a transaction, and may be shared around them. This comes with a limitation that applications cannot use features that change the “global” state of a connection like SET, LISTEN/NOTIFY, or prepared statements 4.
  • Statement pooling: Connections are assigned only around individual statements. This only works of course if an application gives up the use of transactions, at which point it’s losing a big advantage of using Postgres in the first place.

New connection creates a new backend process. server side connection pooler

Install

  1. Install PgBouncer
sudo yum install -y pgbouncer
  1. Folder permissions to postgres user
chown postgres:postgres /etc/pgbouncer/ -R
  1. Create Service file for PgBouncer and paste config into it
vi /usr/lib/systemd/system/pgbouncer.service[Unit]
Description=A lightweight connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
After=syslog.target network.target[Service]
RemainAfterExit=yesUser=postgres
Group=postgres# Path to the init file
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.iniExecStart=/usr/bin/pgbouncer -q ${BOUNCERCONF}
ExecReload=/usr/bin/pgbouncer -R -q ${BOUNCERCONF}# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300[Install]
WantedBy=multi-user.target
  1. Open PgBouncer config/init file and paste the config below - https://www.pgbouncer.org/config.html
[databases]
* = port=5432 auth_user=postgres[pgbouncer]
logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = hba
auth_hba_file = /path/to/pg_hba.conf
admin_users = postgres
stats_users = postgres
pool_mode = session
ignore_startup_parameters = extra_float_digits
max_client_conn = 200
default_pool_size = 50
reserve_pool_size = 25
reserve_pool_timeout = 3
server_lifetime = 300
server_idle_timeout = 120
server_connect_timeout = 5
server_login_retry = 1
query_timeout = 60
query_wait_timeout = 60
client_idle_timeout = 60
client_login_timeout = 60
  1. Start PgBouncer service
systemctl start pgbouncer
  1. Conect usi g pgboun er
psql -U owner -d testdb -p 6432

The last part of the config is PgBouncer config/init file(pgbouncer.ini). There are some different types of authentication for PgBouncer.

Let’s consider the default auth_file = users.txt.

md5 : Default auth method for PgBouncer.

1.Run the query below.

    SELECT CONCAT('"',pg_shadow.usename, '" "', passwd, '"') FROM pg_shadow;

2.Copy output and paste into /etc/pgbouncer/users.txt 3.In pgbouncer.ini make changes below

    auth_type = md5
    auth_file = /etc/pgbouncer/userlist.txt

hba

Don't forget to delete replication db configurations in your ''hba.conf'' file, because pgbouncer doesn’t support replication. You can use a fake ''hba.conf'' file instead of your original file.

  1. Check your pg_hba.conf and comment replication db parts. After that make sure that PgBouncer can parse it.
  2. In ‘pgbouncer.ini’ make changes below
auth_type = hba
auth_file = /path/to/pg_hba.conf

Example Pgbouncer configuration

pool_mode = session 
auth_file = users.conf 
auth_query = “SELECT * FROM pgbouncer.user_lookup($1)” 
server_tls_sslmode = verify-ca  
server_tls_ca_file = ca.crt 
server_tls_cert_file = client.crt 
server_tls_key_file = client.key