Log in

No account? Create an account
Debugging PostgreSQL - Nick [entries|archive|friends|userinfo]

[ website | gagravarr.org ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Debugging PostgreSQL [Feb. 8th, 2005|01:03 pm]
You now the situation. Maybe PostgreSQL isn't starting up right. Maybe your shiny new extension module won't load with the dreaded "ERROR: could not load library "...": dynamic load error". Whatever it is, PostgreSQL isn't behaving, and its log files aren't much help.

Having dealt with this quite a bit over the last few days (mostly related to building contrib modules for the new Windows version of pg 8), here is my guide:
  1. Get a shell running as the user postgres normally runs as (typically postgres, but do check).
    On linux, a simple "su - postgres" should do you. If the shell is set to false or something like that, chuck on -s /bin/bash
    On windows, you'll need to know the password (if you reset it in user admin, be sure to update it in the login tab of the service definition). Armed with that, run "runas /user:postgres cmd.exe", and tap in the password
  2. Ensure that the normal postgres has stopped
  3. Get yourself into the bin directory (where postmaster and postgres live)
  4. Run "postmaster -d 5 -D ../data/"
    This should give you lots of debugging info to the screen showing what it's up to
  5. Run your problem psql command / script, and see what shows on the postmaster window
    (If it's a missing dynamic library, on windows you'll get a popup window telling you the missing library, on linux you'll see an ERROR line on the postmaster console saying the missing library)
  6. Shut down the postmaster with a control+c

Update: It seems that with Windows 2003 SP1, you have to do all of this on the console (and not a terminal services connection), otherwise you still won't get to see the missing DLL error.