Difference between connections established directly to postgres and a connection established through pgbouncer - a view from netstat

 Difference between connections established directly to postgres and a connection established through pgbouncer

pgbouncer acts as a proxy for client.It is used as a connection pooler.

The pgbouncer reduces the connection latency to a greater extent which is very much useful for the OLTP apps.

Direct connection:

Client:

-bash-4.2$ psql -U postgres -h 127.0.0.1 -p 5432

psql (15.2, server 14.7)

Type "help" for help.

postgres=# \c

psql (15.2, server 14.7)

You are now connected to database "postgres" as user "postgres".

postgres=#

Netstat output:

-bash-4.2$ netstat -plantu|grep 33336

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          127.0.0.1:33336         ESTABLISHED 4997/postgres: post

tcp        0      0 127.0.0.1:33336         127.0.0.1:5432          ESTABLISHED 4996/psql

-bash-4.2$

Pgbouncer based connection:

Client:

-bash-4.2$ psql -p 6432 testdb -h 127.0.0.1 -U postgres

Password for user postgres:

psql (15.2, server 14.7)

Type "help" for help.

testdb=# \c

psql (15.2, server 14.7)

You are now connected to database "testdb" as user "postgres".

testdb=#

Netstat output:

-bash-4.2$ netstat -plantu|grep -i 6432

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:6432            0.0.0.0:*               LISTEN      4196/pgbouncer <<< ipv4 pgbouncer listener

tcp        0      0 127.0.0.1:6432          127.0.0.1:33676         ESTABLISHED 4196/pgbouncer <<< this the incoming connection

tcp        0      0 127.0.0.1:33674         127.0.0.1:6432          TIME_WAIT   -

tcp        0      0 127.0.0.1:33676         127.0.0.1:6432          ESTABLISHED 4771/psql >>> this is the outgoing connection from client

tcp        0      0 127.0.0.1:33670         127.0.0.1:6432          TIME_WAIT   -

tcp6       0      0 :::6432                 :::*                    LISTEN      4196/pgbouncer <<< ipv6 pgbouncer listener

-bash-4.2$

-bash-4.2$ netstat -plantu|grep 5432

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      1337/postmaster <<< ipv4 postgres listener

tcp        0      0 127.0.0.1:33326         127.0.0.1:5432          ESTABLISHED 4196/pgbouncer <<< pgbouncer redirecting the connection here, which is an outgoing connection

tcp        0      0 127.0.0.1:5432          127.0.0.1:33326         ESTABLISHED 4772/postgres: pgte <<< postgres accepting the incoming connection

tcp6       0      0 :::5432                 :::*                    LISTEN      1337/postmaster <<< ipv6 pgbouncer listener

-bash-4.2$ ps -ef|grep 4196

Overall picture:

YouTube Video:

Thanks for visiting the blog. Thanks