Using windbg.exe to debug PostgreSQL

Moved from Getting a stack trace of a running PostgreSQL backend on Windows because windbg.exe is not something sensible people use when there are VS Express editions.

Getting a stack trace using WinDBG

The Debugging Tools for Windows include a program called windbg.exe, the Windows Debugger. It's a very powerful and useful tool, but it's not user friendly. Windbg.exe may also be used to remotely debug a system even if you don't have full GUI access. Using windbg may interrupt the execution of the backend or possibly (though it's rather unlikely) even hold up the entire database system while the target backend is paused.

You will need to know the process ID of the backend you want to connect to. See "Getting the process ID of the PostgreSQL backend" above for how to find that out.

Once you know the process ID, you need to start WinDBG. It'll be in the Start menu under Debugging Tools for Windows. To connect to the PostgreSQL backend of interest, use the File -> Attach to Process menu item (shortcut: F6). Enter the pid you found earlier into the "process id" field and press enter.

windbg has now attached to that postgres backend. Its execution has paused, and it will appear to have hung. This is normal. You're in a command-line interface. These instructions will provide the basic steps you need, and more information can be found in the section learn more about windbg.

First, type "G" (without the quotes) and hit enter. That'll cause the backend you attached to to unpause and resume normal execution. The debugger remains attached and will trap crashes or other unusual events, and you can pause it manually yourself. While attached but not paused the debugger will have no effect on the normal operation of that PostgreSQL backend.

First, you should get a stack trace of the backend while it's running normally and make sure the stack trace is valid and useful. There's no point going to a bunch of hassle to collect a stack trace that turns out to be incomplete and useless.

Getting a stack trace of a healthy running backend

For testing, or because you want to see what a backend is busy doing, you might want to get a stack trace of a backend that's running normally. This won't give you information about a crash; see later sections for that.

First, interrupt execution using the Break command from the Debug menu. This will return you to a command line.

Now type the command:

~*k

in and press enter, then select all the output (CTRL-A) and copy it (CTRL-C) to the clipboard. You can now paste this information into a text file, email, etc. It'll look something like this:

   0  Id: b0c.b1c Suspend: 1 Teb: 7ffdf000 Unfrozen
ChildEBP RetAddr  
00cafc2c 7c90df5a ntdll!KiFastSystemCallRet
00cafc30 7c8025db ntdll!ZwWaitForSingleObject+0xc
00cafc94 7c802542 kernel32!WaitForSingleObjectEx+0xa8
00cafca8 00587866 kernel32!WaitForSingleObject+0x12
00cafcb4 0058ba4c postgres!pg_usleep+0x36 [c:\pginstaller-repo\postgres.windows\src\backend\port\win32\signal.c @ 56]
00cafd18 00596075 postgres!AutoVacLauncherMain+0x24c [c:\pginstaller-repo\postgres.windows\src\backend\postmaster\autovacuum.c @ 597]
00caff60 00502da7 postgres!SubPostmasterMain+0x2b5 [c:\pginstaller-repo\postgres.windows\src\backend\postmaster\postmaster.c @ 3900]
00caff7c 006b7aad postgres!main+0x177 [c:\pginstaller-repo\postgres.windows\src\backend\main\main.c @ 165]
00caffc0 7c817027 postgres!__tmainCRTStartup+0x10f [f:\sp\vctools\crt_bld\self_x86\crt\src\crtexe.c @ 597]
00cafff0 00000000 kernel32!BaseProcessStart+0x23

   1  Id: b0c.95c Suspend: 1 Teb: 7ffde000 Unfrozen
ChildEBP RetAddr  
0162fecc 7c90d3aa ntdll!KiFastSystemCallRet
0162fed0 7c83145e ntdll!ZwFsControlFile+0xc
0162ff14 0058777f kernel32!ConnectNamedPipe+0x52
0162ffb4 7c80b6d9 postgres!pg_signal_thread+0x9f [c:\pginstaller-repo\postgres.windows\src\backend\port\win32\signal.c @ 275]
0162ffec 00000000 kernel32!BaseThreadStart+0x37

#  2  Id: b0c.208 Suspend: 1 Teb: 7ffdc000 Unfrozen
ChildEBP RetAddr  
0163ffc8 7c951e68 ntdll!DbgBreakPoint
0163fff4 00000000 ntdll!DbgUiRemoteBreakin+0x2d

If you've collected everything you needed to you can detach from the PostgreSQL proces (debug menu -> Detach debugee) and close WinDBG. Alternately, if you want to collect a few more stack traces at different points in execution, use the "G" command to tell PostgreSQL to resume running. When you want to interrupt it again, use "Break" from the Debug menu and enter ~*k to get another set of stack traces.

Getting a stack trace of a repeatable backend crash

If you're trying to track down a backend crash and you can reasonably predictably trigger the crash in a connection of your choice, you'll probably want to use windbg. You will need to attach windbg to the backend while it's still running (see above) and issue the "G" command in windbg to get the backend to continue running. Now you need to do whatever is required to trigger the crash in the backend using your connection to it.

When the backend crashes, windbg should interrupt the crash. At this point, you can run the following command in windbg to get some stack trace information about the crash:

~*k

Crashes during postmaster startup

This useful article on debugging Windows services will help you deal with issues where the postmaster is crashing during startup of the Windows service for PostgreSQL.

Remote debugging with windbg.exe

You might not want to, or be able to, sit at the database server console for debugging. That won't stop you using windbg on PostgreSQL, though - you can launch a remote debugging server on the Pg machine and debug from another computer, possibly even via the Internet. Visual Studio supports remote debugging too.

• On the Pg server:
  • Copy dbgsrv.exe from the debugging tools for windows folder to the remote server running Pg (or install the debugging tools there).
  • Add an exception for dbgsrv.exe in the firewall of the remote machine
  • Launch dbgsrv.exe on the remote machine as an Administrator. Use the command: dbgsrv.exe -t tcp:port=12345,password=fred . (If you need to, use runas.exe to get admin rights).
• On the workstation you're debugging from:
  • Launch windbg
  • In the file menu, choose "Connect to remote stub..."
  • As the connection string enter "tcp:server=YOUR_PG_SERVER_NAME_OR_IP,port=12345,password=fred"
  • Click "OK"
  • Windbg will now try to do all work via the remote debug stub, as if you were running windbg on that machine, so you can follow the instructions as given above.

Learn more about windbg

If you want to learn more about using windbg, this article is a good starting point.

If you're familiar with gdb you'll also want to read this article on gdb for WinDBG users that's pretty handy if you're going in the other direction too.

More guidance on windbg can be found [in this windbg commands summary](http://windbg.info/doc/1-common-cmds.html).