Menu

Subscribe

Sane logging for postgres

Getting situated

I’m using CentOS 7, and my logs are at /var/lib/pgsql/data/pg_log by default. Or if not by default, then because of a setup program I ran a long time ago. How did I figure out where the logs are located? It takes a few queries…

$ psql
postgres=# show data_directory;  
   data_directory    
---------------------
 /var/lib/pgsql/data
(1 row)

postgres=# show log_directory;  
 log_directory 
---------------
 pg_log
(1 row)

So append the log directory to the data directory and you have your log directory. Not too bad…

But now we might want to change those settings. So we’ll have to find the right conf file. In this case, the aptly named postgresql.conf.

$ sudo find / -name "postgresql.conf" -print
/var/lib/pgsql/data/postgresql.conf

In that file, go down to the logging section. You can just search for logging_collector.

A lot of the values will be commented out, so if you don’t happen to have the defaults memorized offhand, you can check in postgres itself as I did above for the log_directory (also see [1]).

In my case, postgres has been set up to rotate the logs every day of the week. That’s not too bad - it’s what’s recommended in [2], but I don’t look at these logs very often, so if I need to check something, it might well be more than a week later.

Changing the configuration

I stole all of these defaults from 2, which was a really great reference. It explains some settings that could be enabled for better debugging capabilities. These checkboxes might not render on Ghost, but let's see...

  • [ ] logging_collector = on
  • [ ] log_truncate_on_rotation=on
  • [ ] log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
  • [ ] log_checkpoints = on
  • [ ] log_autovacuum_min_duration = 0
  • [ ] log_temp_files = 0
  • [ ] log_lock_waits = on

And my own edits:

  • [ ] log_rotation_size = 1GB
  • [ ] log_filename = 'postgresql.log'

Logrotate

So originally I thought it would make sense to just have one log file that overwrites at every gigabyte, but then I realized that everything at the end of the file would be lost if I needed it.

CentOS comes with logrotate, which is a neat little application that I didn’t previously know about, and which does exactly what I want. And more! If I had email set up on this machine, I could even have logrotate email me compressed versions of the logs. It apparently runs as a cron job at some point during the day.

So to use logrotate, I really just have to add a config file to /etc/logrotate.d. I’ll keep it pretty simple here. Basically, I want the files to rotate when they hit 200 megabytes, and I want no more than 5 previous copies kept, and I don't want to write a cron script.

# /etc/logrotate.d/postgresql

/var/lib/pgsql/data/pg_log/postgresql.log {
    rotate 5
    size 200M
}

You can even test the rotation out without waiting for the file to hit 200M. And not by changing the size limit, you dog.

  • sudo logrotate -d /etc/logrotate.d/postgresql will show you what would happen next time logrotate runs and hits your file. The -d specifies debug mode, so nothing will actually change.
  • sudo logrotate --force /etc/logrotate.d/postgresql will rotate the file for you, and give you a taste for what will happen later.

For all of the postgres changes to take effect, you’ll probably have to restart your postgresql: sudo service postgresql restart. Make sure to tail your logs while you’re doing this! There ended up being an error in one of my config files, so I had a stressful couple of minutes trying to find out where the error was coming from while the database was down. Thankfully it was just a dev server. This time…

Resources

Logrotate