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: http://code.google.com/p/msysgit/downloads/list?can=3&q=official+Git
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.
the gabriellephant 