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.
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.
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.
|
1 |
├── pgbouncer<br>│ ├── pgbouncer.ini<br>│ └── userlist.txt<br>├── postgres<br>│ ├── add_function.sql<br>│ ├── add_user.sql<br>└── systemd<br> ├── pgbouncer_override<br> └── pgbouncer.socket<br> |
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.
|
1 |
[databases]<br>* = host=localhost<br><br>[users]<br># left blank <br><br>[pgbouncer]<br>logfile = /var/log/postgresql/pgbouncer.log<br>pidfile = /var/run/postgresql/pgbouncer.pid<br><br>;; these parameters are implicitly disabled<br>listen_addr = 0.0.0.0<br>listen_port = 6432<br><br>unix_socket_dir = /var/run/postgresql<br>auth_type = md5<br>auth_file = /etc/pgbouncer/userlist.txt<br>auth_user = pgbouncer<br>auth_query = SELECT p_user, p_password FROM public.lookup($1)<br><br>pool_mode = session<br><br>;; Use <appname - host> as application_name on server.<br>application_name_add_host = 1 |
Contains a single user account and its password for authentication. Note that the md5 hashed password is pgbouncer.
|
1 |
"pgbouncer" "md5be5544d3807b54dd0637f2439ecb03b9" |
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“:
|
1 |
--<br>-- EXECUTE AS SUPERUSER postgres<br>--<br>CREATE ROLE pgbouncer LOGIN WITH PASSSWORD pgbouncer; |
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.
|
1 |
--<br>-- EXECUTE AS SUPERUSER postgres<br>--<br>-- execute on each database user accounts will login<br>--<br>CREATE FUNCTION public.lookup (<br> INOUT p_user name,<br> OUT p_password text<br>) RETURNS record<br> LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS<br>$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;<br><br>-- make sure only "pgbouncer" can use the function<br>REVOKE EXECUTE ON FUNCTION public.lookup(name) FROM PUBLIC;<br>GRANT EXECUTE ON FUNCTION public.lookup(name) TO pgbouncer; |
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:
|
1 |
systemctl edit pgbouncer |
|
1 |
#<br># systemctl edit pgbouncer<br># systemctl daemon-reload<br>#<br><br>[Unit]<br>Requires=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:
|
1 |
vi /etc/systemd/system/pgbouncer.socket |
|
1 |
#<br># vi /etc/systemd/system/pgbouncer.socket<br># systemctl daemon-reload<br>#<br><br>[Unit]<br>Description=sockets for PgBouncer<br>PartOf=pgbouncer.service<br><br>[Socket]<br>ListenStream=6432<br>ListenStream=6433<br>ListenStream=6434<br>ListenStream=/var/run/postgresql/.s.PGSQL.6432<br><br>ReusePort=true<br>RemoveOnStop=true<br><br>[Install]<br>WantedBy=sockets.target |
|
1 |
systemctl daemon-reload |
After all the configuration files have been created and edited, this is what we get:
|
1 |
systemctl restart pgbouncer |
|
1 |
netstat -tlnp |
|
1 |
Proto Recv-Q Send-Q Local Address Foreign Address State <br>tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN <br>tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN <br>tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN <br>tcp6 0 0 :::22 :::* LISTEN <br>tcp6 0 0 ::1:5432 :::* LISTEN <br>tcp6 0 0 :::6432 :::* LISTEN <br>tcp6 0 0 :::6433 :::* LISTEN <br>tcp6 0 0 :::6434 :::* LISTEN |
Finally, perform the following:
Now validate the ports:
|
1 |
for u in 5432 6432 6433 6434<br>do<br> echo "==== port: $u ==="<br> export PGHOST=localhost PGPORT=$u PGDATABASE=db01<br> psql "user=usr1 password=usr1" -c "select 'hello world' as greetings"<br>done |
And here’s the output:
|
1 |
==== port: 5432 ===<br> greetings <br>-------------<br>hello world<br>(1 row)<br><br>==== port: 6432 ===<br> greetings <br>-------------<br>hello world<br>(1 row)<br><br>==== port: 6433 ===<br> greetings <br>-------------<br>hello world<br>(1 row)<br><br>==== port: 6434 ===<br> greetings <br>-------------<br>hello world<br>(1 row) |
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.
