Before doing a deep dive into the subject, a short outline about PgBouncer, it is 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 a stderr/syslog logging architecture to record connection, disconnection, and pooler_errors with different verbosity levels. As of now, the greater part of logging goes to one single file "pgbouncer.log" and grows endlessly. Sometimes, it might be a potential risk of making a system unresponsive due to a 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:
- Configure PgBouncer in "syslog" method to rely on OS log rotation or
- 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):
Aug 5 16:54:27 raghavt pgbouncer[62549]: C-0x1cdfe60: postgres/postgres@unix(62621):6432 login attempt: db=postgres user=postgres tls=no
Aug 5 16:54:27 raghavt pgbouncer[62549]: S-0x1ce4b10: postgres/postgres@127.0.0.1:5432 new connection to server (from 127.0.0.1:38947)
Aug 5 16:54:27 raghavt pgbouncer[62549]: C-0x1cdfe60: postgres/postgres@unix(62621):6432 closing because: client close request (age=0)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 the 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 the internet 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:
/var/log/pgbouncer/pgbouncer.log {
rotate 10
missingok
sharedscripts
notifempty
nocompress
size 10m
daily
create 0640 postgres postgres
postrotate
/bin/kill -HUP `cat /var/pgbouncer-postgres/pgbouncer.pid 2> /dev/null` 2>/dev/null ||true
endscript
}
About the configuration file, the first line indicates the pgbouncer log file location("logfile" parameter values in pgbouncer.ini file), and next are the parameters which work on rotation thresholds such as: 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"):
[root@172.16.210.202 pgbouncer]# ls -lrth
total 16K
-rw-r-----. 1 postgres postgres 10M Jul 27 15:30 pgbouncer.log-20160727
-rw-r-----. 1 postgres postgres 11K Jul 27 18:32 pgbouncer.logThat was simple, right? Now let's check the same on a Windows environment.
On Windows
I know much less about Windows utilities; consequently, I did some Googling and found a Windows version utility called "LogRotateWin", which works like the 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)
c:\Program Files (x86)\LogRotate\Content>more logrotate.conf
"c:\Program Files (x86)\PgBouncer\log\pgbouncer.log" {
rotate 10
copytruncate
create
missingok
sharedscripts
nocompress
size 200k
daily
}
To verify, I have forced the log rotation with the "-f" option:
c:\Program Files (x86)\LogRotate>logrotate.exe -f Content\logrotate.conf
logrotate: Force option set to true
The results:
C:\Program Files (x86)\PgBouncer\log>dir
Volume in drive C has no label.
Volume Serial Number is F226-9FFB
Directory of C:\Program Files (x86)\PgBouncer\log
08/08/2016 01:31 PM <DIR> .
08/08/2016 01:31 PM <DIR> ..
08/08/2016 01:31 PM 0 pgbouncer.log
08/08/2016 01:31 PM 6,626 pgbouncer.log.1
08/08/2016 01:31 PM 13,252 pgbouncer.log.2
3 File(s) 19,878 bytes
2 Dir(s) 26,905,051,136 bytes free
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.