Configuring PgBouncer for Multi-Port Access
source link: https://www.percona.com/blog/configuring-pgbouncer-for-multi-port-access/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Configuring PgBouncer for Multi-Port Access
June 5, 2023
From time to time, situations occur where unusual circumstances dictate out-of-the-box thinking.
For example, suppose you have a system where you’ve installed multiple data clusters onto a single host. What you end up with is a host breaking up and sharing valuable system resources, i.e., CPU, RAM, disk, etc., between multiple instances of PostgreSQL data clusters. While easy to do in a development environment, it does represent technical debt when it reaches production. Sooner or later, one must address this issue; otherwise, one can suffer the consequences of handicapping your entire database infrastructure.
Let’s now move forward in time: your system has scaled, of course, and this shortcut of using multiple data clusters on a single host has now become a performance bottleneck. The problem is you either don’t or can’t refactor your application servers; something, maybe, about not having enough time in the day. And, as you may already know, while Postgres can sit on both a UNIX DOMAIN socket and IPv4, IPv6 port, etc., one is nevertheless constrained to listen to just the one port.
So what do you do?
For the experienced sysadmin, there are actually quite a number of “magical” techniques. However, in this case, with a little help from systemd, which manages all service processes, we will solve this little puzzle using PgBouncer with a concoction of configuration files.
Scenario
Configure the system such that Postgres resides on its default port of 5432 and PgBouncer sits on three ports, i.e., 6432, 6433, and 6433, accessing the resident Postgres server.
The PgBouncer connection pooler will use an administrative account, the ROLE PgBouncer, for the purpose of user authentication. Authentication is to be achieved by accessing the Postgres server’s pg_shadow table and comparing the resultant hash to all incoming connections (this won’t work for cloud setups such as, for example, Amazon RDS).
A set of Systemd configuration files will be created and edited in order to manage the PgBouncer service.
About the files
Below is a summary of the files and how they will be edited. Remember, these configuration files are of a hypothetical nature using minimal settings, which, of course, will need to be updated to match a realistic production environment.
├── pgbouncer │ ├── pgbouncer.ini │ └── userlist.txt ├── postgres │ ├── add_function.sql │ ├── add_user.sql └── systemd ├── pgbouncer_override └── pgbouncer.socket |
pgbouncer.ini
This configuration file defines all behavior and is installed in its default location, “/etc/pgbouncer”.
Only one domain socket is used. For our purposes, the listen_port runtime parameter is just noise and is superseded by the other runtime parameters as declared in file pgbouncer.socket.
TIP: Backup the original pgbouncer.ini as it references ALL runtime parameters.
[databases] * = host=localhost [users] # left blank [pgbouncer] logfile = /var/log/postgresql/pgbouncer.log pidfile = /var/run/postgresql/pgbouncer.pid ;; these parameters are implicitly disabled listen_addr = 0.0.0.0 listen_port = 6432 unix_socket_dir = /var/run/postgresql auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt auth_user = pgbouncer auth_query = SELECT p_user, p_password FROM public.lookup($1) pool_mode = session ;; Use <appname - host> as application_name on server. application_name_add_host = 1 |
userlist.txt
Contains a single user account and its password for authentication. Note that the md5 hashed password is pgbouncer.
"pgbouncer" "md5be5544d3807b54dd0637f2439ecb03b9" |
add_user.sql
Adds the ROLE “pgbouncer” to the Postgres data cluster. While under normal circumstances this is not required, PgBouncer uses this ROLE in order to validate all logins.
For our purposes, the password is “pgbouncer”:
-- EXECUTE AS SUPERUSER postgres CREATE ROLE pgbouncer LOGIN WITH PASSSWORD pgbouncer; |
add_function.sql
This is a user-defined function that ROLE PgBouncer executes in order to obtain the password hash from pg_shadow.
This SQL statement must be executed against each and every database that is to be accessed by any ROLE connection using PgBouncer.
TIP: Execute this SQL against database template1, and the function call will be included with every newly created database thereafter.
-- EXECUTE AS SUPERUSER postgres -- execute on each database user accounts will login CREATE FUNCTION public.lookup ( INOUT p_user name, OUT p_password text ) RETURNS record LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS $$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$; -- make sure only "pgbouncer" can use the function REVOKE EXECUTE ON FUNCTION public.lookup(name) FROM PUBLIC; GRANT EXECUTE ON FUNCTION public.lookup(name) TO pgbouncer; |
pgbouncer_override
This systemd drop-in file overrides key options in the default PgBouncer unit file and will never be overwritten even after updating the PgBouncer Linux RPM/DEB packages.
As root, execute the following command and paste the contents of the provided file pgbouncer_override:
systemctl edit pgbouncer |
# systemctl edit pgbouncer # systemctl daemon-reload [Unit] Requires=pgbouncer.socket |
pgbouncer.socket
This is the secret sauce; this file enables PgBouncer to listen on all three ports, 6432, 6433, and 6434. You will note that adding or removing ports is a simple matter of adding or removing addresses as per the format shown in the file below.
As root: create this file and perform a daemon reload:
vi /etc/systemd/system/pgbouncer.socket |
# vi /etc/systemd/system/pgbouncer.socket # systemctl daemon-reload [Unit] Description=sockets for PgBouncer PartOf=pgbouncer.service [Socket] ListenStream=6432 ListenStream=6433 ListenStream=6434 ListenStream=/var/run/postgresql/.s.PGSQL.6432 ReusePort=true RemoveOnStop=true [Install] WantedBy=sockets.target |
systemctl daemon-reload |
Putting it all together
After all the configuration files have been created and edited, this is what we get:
systemctl restart pgbouncer |
netstat -tlnp |
Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN tcp6 0 0 :::22 :::* LISTEN tcp6 0 0 ::1:5432 :::* LISTEN tcp6 0 0 :::6432 :::* LISTEN tcp6 0 0 :::6433 :::* LISTEN tcp6 0 0 :::6434 :::* LISTEN |
Finally, perform the following:
- sudo as Postgres
- create a database db01
- create a ROLE usr1 accessing database db01
- update pg_hba.conf and postgresql.conf allowing localhost connections by PgBouncer
Now validate the ports:
for u in 5432 6432 6433 6434 echo "==== port: $u ===" export PGHOST=localhost PGPORT=$u PGDATABASE=db01 psql "user=usr1 password=usr1" -c "select 'hello world' as greetings" |
And here’s the output:
==== port: 5432 === greetings ------------- hello world (1 row) ==== port: 6432 === greetings ------------- hello world (1 row) ==== port: 6433 === greetings ------------- hello world (1 row) ==== port: 6434 === greetings ------------- hello world (1 row) |
Conclusion
Because PostgreSQL was designed from the ground up to work in conjunction with the OS, i.e., a UNIX type of operating system, we’ve been able to solve an interesting problem using a novel approach. And while systemd is ubiquitous, it isn’t normally considered part of a solution to a Postgres puzzle. We call this hacking 🙂
Happy Travels!
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
Share This Post!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK