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.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.

userlist.txt

Contains a single user account and its password for authentication. Note that the md5 hashed password is pgbouncer.

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”:

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.

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:

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:

Putting it all together

After all the configuration files have been created and edited, this is what we get:

Finally, perform the following:

  1. sudo as Postgres
  2. create a database db01
  3. create a ROLE usr1 accessing database db01
  4. update pg_hba.conf and postgresql.conf allowing localhost connections by PgBouncer

Now validate the ports:

And here’s the output:

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!

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments