Before doing a deep dive into the subject, a short outline about PgBouncer, its a lightweight connection pooler for PostgreSQL that dramatically reduces the processing time and resources for maintaining a large number of client connections to one or more databases. Typically used to increase the number of user connections that can be handled in a high performance environment. For more details on Installing/Configuring PgBouncer refer to the documentation here.

Like other tools, PgBouncer has astderr/syslog logging architecture to record connection, disconnection, and  pooler_errors with different verbosity levels. As of now, the greater part of logging go to one single file “pgbouncer.log” and grows endlessly. Sometimes, it might be a potential risk of making a system unresponsive due to lack of disk space on the log file location. At present, PgBouncer logging has no in-built configuration to rotate logs on the basis of age or size, hence it forces users to choose alternative methods. IMO, there are two approaches to handle it :-

1. Configure PgBouncer in “syslog” method to rely on OS log rotation or
2. Configure log rotation using OS utilities on “pgbouncer.log” file.

Method 1:

Its pretty straightforward to configure syslog in PgBouncer, set “syslog” to 1 (default 0); give a name to begin the log line in OS logs in “syslog_ident” (default ‘pgbouncer’) and specify the facility details in “syslog_facility” (default daemon). A sample output from my OS logs(/var/log/messages):

Note: If “syslog” enabled, comment or blank out the “logfile” parameter, else it will be additional logging. 

Method 2:

Logrotate is one of the OS utility that has an ability to rotate logs systematically and archive to reduce an operating system’s disk space requirement. Each log file may be handled daily, weekly, monthly, or when it grows too large. A default configuration file “/etc/logrotate.conf” defines the log rotation age/size/interval.  Using this tool logs can be kept longer with less disk space. Many people have articulated about the usage of the utility which you can discover it over net anyway, thus am jumping directly into the implementation phase.

First, create a configuration file in /etc/logrotate.d/ directory for pgbouncer logs. I have named it as “/etc/logrotate.d/pgbouncer” with below details:

About the configuration file, first line indicate the pgbouncer log file location(“logfile” parameter values in pgbouncer.ini file) and next are the parameters that work on rotation thresholds like; how many log files to maintain (rotate); issue no error and go on to next log (missingok); what script should be executed pre/post rotation (prerotate/postrotate); run once or multiple times pre/post scripts (sharedscripts); do not rotate the log if it is empty (notifempty); after rotation an old log file should be compressed with gzip utility (compress/nocompress); on how much size log rotation should be performed (size); how often to rotate a particular log (daily); and what permission new log file should be (create).

Now we can see new log files rotated with 10M size. (We can even force the rotation with command “logrotate -f /etc/logrotate.conf”)

That was simple right , now lets check the same on Windows environment.

On Windows:

I know very less about windows utilities, consequently I did some googling and found a Windows version utility called “LogRotateWin”  which works same like Linux version of logrotate. For more details refer to detailed documentation available on Installation/Configuration/Usage here. 

Let’s see how it works, first download “.msi” version of LogRotateWin available on the site as “logrotateSetup*.zip” file. Extract and execute the “.msi” file, it will install the utility to “c:\Program Files (x86)\LogRotate” location. You can find the default configuration file(logrotate.conf) under “c:\Program Files (x86)\LogRotate\Content”. 

Next, edit the “c:\Program Files (x86)\LogRotate\Content\logrotate.conf” file and specify the full path of “pgbouncer.log” file with same rotation parameters. A sample copy of my configuration file tested on Windows 10. (Note: Below parameter values are used to test the utility)

To verify, I have forced the log rotation with “-f” option

Here’s the result:

Nice right !!!.

On most Linux distributions, logrotate runs daily using “logrotate.conf” as part of cronjob, similarly on Windows, we can schedule a task in Windows Task Scheduler to rotate the logs daily. FYI, I have not explored much on “LogRotateWin” utility just a basic level. In case, if you encounter any issue please post it on logrotate General Discussion forum.

Thank you for reading. 

Please feel free to write me an email to share your comments & thoughts.

–Raghav