Postgres logging… on Windows.

by gorthx

Yep, you read that right. My regular readers (hi, dad!) are probably wondering “Who are you, and what have you done with gabrielle?” so let me explain. We’re currently demoing netbrain where I work; it’s a dynamic network mapping and troubleshooting tool. It runs on Windows, and uses Postgres as its backend database. Naturally, I wanted to have a look-see [1]. And since the general rule is “When in doubt, go with what you know”, I figured I’d set up Pg logging.

The first order of business was to find postgresql.conf and pg_hba.conf. Now, I’ve been out of the Windows world for a while and had to feel my way around, and it was a lot like trying to type while wearing a pair of fur mitts. Everything took me three times as long. By the time I had clicky-clicked my way to Computer/OS (C:)/Program Files (x86)/NetBrain/Postgresql 8.4/database, I clung to the conf files like flotsam in the tide. Then I got nervous about editing them with notepad and decided I needed to install Vim first.

Step 1, as always: Make a backup! I called mine postgresql.conf.distro. Normally I’d set up a git repo, but we have a (time limited!) demo license, and I didn’t want to spend time installing git on Windows [2].

So, logging. I wanted to see who’s connecting, and any “long” queries, so I changed postgresql.conf like so:
log_destination = eventlog
log_min_duration_statement = 1s # for now, will increase once I figure out what's "normal" for this database
log_statement = ddl # same
log_connections = on
log_disconnections = on
log_line_prefix = '<%r %d %u>'

You can log to another location (which I haven’t tried yet), but database shutdown and startup messages are going to go to the Windows eventlog, regardless, so you’ll have to watch two things at once [3].

Now I needed to restart the database [4]. Restarting is pretty straightforward: head over to Server Manager -> Configuration -> Services, find Pg in the list, right-click, restart.

Then I needed to figure out how to watch my log messages without ‘tail’. Hm.
Server Manager -> Diagnostics -> Event Viewer -> Windows Logs -> Application…and I see Pg messages!

I created a custom view so I wouldn’t have to look at the rest of the windows stuff; I just checked the “By source” radio button, and selected PostgreSQL from the drop-down. Voila! It doesn’t automatically update; you have to click the ‘refresh’ button on the lower right. Which is kind of a bother, but it is better than nothing.

This is good enough for now; I have other things to investigate while we still have the demo license. The ultimate plan is to get this logging to something that fouine can read. I got some good tips from the PDXPUGers at our meeting last week & am looking forward to checking them out.

1 – “What’s the worst that could happen?”
2 – Which is one of the first things I’ll do when we deploy to production. git for Windows:
3 – Tim of PDXPUG fame tells me there are ways around this.
4 – Pg on windows is a “service”, so my choices are start, stop, and restart. There’s no reloading to e.g. re-read your pg_hba.conf.

5 Comments to “Postgres logging… on Windows.”

  1. Welcome back to blog land. I assume is now retired? Good choice on avoiding Notepad. One of these days someone will enlighten me on what it is good for. If you need to text edit in Windows with the tools it brings, use Wordpad, it actually works. I would be interested in learning a viable tail alternative in Windows, short of going the Cygwin route. At any rate this blog explains the disturbance in the ‘Force’.

  2. @Adrian Thanks! Yes, B&T is retired, at least for the time being. I’m sure there’s going to be more to this story, so be prepared for a greater disturbance. ;)

  3. About reloading, last time I had to do training with a computer under Windows, the trainee had its pg_hba.conf file automagically reloaded by PostgreSQL when changing it…I was quite puzzeled :)

    I don’t know if it applies to postgresql.conf file though.

  4. If you want to use pfFouine, I would set log_destination back to ‘stderr’, this will end up in pg_log folder under your data directory.

    Also, it’s no tail, but Textpad from will at least auto-refresh and is safe to edit your .conf files.

  5. @Jehan-Guillaume: I will have to investigate that! I would have been puzzled as well.

%d bloggers like this: