1

A windows 7 machine running a Postgres 8.4.x server stopped working properly this morning. The errors are more than strange:

  • The service is not running, according to the services control panel.
  • Astonishingly, the server is actually running, because we can connect with both pgAdmin and our own ODBC-based software, but some queries crash (that's how we found out it's broken).
  • Neither starting not stopping the server does anything, and both return with errors, either failure to access the data directory, or just no useful error at all (net helpmsg gives me exactly nothing).

Event-Viewer, most of these are there multiple times, and happen when booting or trying to start/stop the server.

PostgreSQL - Error - Se agotó el tiempo de espera al inicio del servidor
2013-12-03 21:33:32 GMT FATAL:  el archivo de bloqueo «postmaster.pid» ya existe
2013-12-03 21:33:32 GMT HINT:  ¿Hay otro postmaster (PID 2952) corriendo en el directorio de datos «C:/Program Files (x86)/PostgreSQL/8.4/data»?
pg_ctl: no se pudo encontrar el ejecutable postgres
2013-12-03 18:46:34 CET FATAL:  no se pudo crear ningún socket TCP/IP

pg_dump is able to pull a complete backup (so I did that before messing around), despite the service still not marked as running.

I've tried deleting the pid-file, as that's something I find recommended everywhere, but that didn't help.

Going through the log-files actually found a real error in a query, but that still does not solve the issue that I cannot stop or start the postgres service. Edit: And fixing that invalid query doesn't solve anything, the application still crashes.

Relevant parts of the pg_hba.conf:

host    all     all     127.0.0.1/32     md5
host    all     all     192.168.0.0/16   md5
host    all     all     fe80::/48        md5

Some logs (the others have the equivalent content):
http://pastebin.com/v9gtiDmJ
http://pastebin.com/wxYr8TUM

3
  • I did the translation. It seems, your postgresql binary can't be found, or your postgresql already runs. I suggest a postgresql reinstall as first step. If it doesn't helps, I will do.
    – peterh
    Dec 4, 2013 at 12:03
  • I've tried updating the postgres from 8.4.15 to 8.4.17 which should get the correct binaries in the correct places (assuming that's possible while the service is still running as a zombie). That didn't work at all.
    – Kdansky
    Dec 4, 2013 at 12:59
  • Ouch. Windows generally locks running executables and DLLs and prevents their replacement. (You can, however, rename the running program and put the new one in place.) If you attempted to use an MSI based installer it probably replaced some of the files but others will be replaced on reboot. You can try killing pg_ctl.exe and all the postmaster.exe processes, which should kill everything that's running. Then you can attempt to reinstall 8.4.17 and the service.
    – afrazier
    Dec 4, 2013 at 20:34

1 Answer 1

11

First for the love of your data PLEASE stop making changes to the system.
You need to fully analyze the problem before you start making changes, otherwise you run a real risk of making the problem worse.


As you've surmised Postgres is running on your system - we know this because pg_dump works. If there were no Postgres server pg_dump wouldn't have anything to talk to.
This means your problem (as it existed at the start) is entirely cosmetic (so Service Manager thinks it's down - who cares?! It's up, and that's what's important).
The "easy fix" in this case is to just ignore the situation -- If it ain't broke, don't break it!

The next easy fix (if Service Manager's opinion matters to you) is to manually stop Postgres using pg_ctl, then restart it with service manager.
This isn't an option anymore for you because you deleted the PID file. Now pg_ctl doesn't know which Postgres process to signal. (On Unix this is trivial to remedy - simply signal the Postgres process with the lowest PID to terminate and the database shuts down. I'm not sure what the equivalent is on Windows, but if you do you can do that.)

The last option is a reboot. This is what you'd do if the two options above failed to make Service Manager report a status consistent with reality.
On Unix this should gracefully shut down Postgres when all system processes get the TERM signal, and I assume Windows has equivalent behavior (but even if it doesn't that's OK - Postgres would treat it as a crash and recover the next time it starts up).


Because you upgraded your Postgres installation you now have to execute either the second or third option - you can't leave your system in its current condition.
You are currently in a state where the binaries/libraries on disk do not match the ones running, and that's not a good position to be in. To ensure that your system is in a known, consistent state you must restart Postgres.

In your case I would suggest stopping the database server, reinstalling the binaries (to ensure none were missed because they were locked), and then starting it again. You generally don't ever do an upgrade of the binaries while Postgres running - you shut it down first. At least on Unix systems you can "get away with it" most of the time, but why take the chance of something going wrong?

4
  • Thanks for the honesty. Note that I already got a backup off the system (and was restoring it on a second machine) before starting to prod the original with the stick, so there was no data at risk at any time. The problem turned out to be be more than just cosmetic (the service could not be stopped or started with pg_ctl, but re-installing pg completely did the job), and the actual crashes happened due to a varchar field with 274 characters.
    – Kdansky
    Dec 5, 2013 at 9:49
  • 1
    you can send SIGTERM without a reboot, but I'd personally probably send SIGINT and let Pg shut down the clients, pkill -s INT postgres Dec 5, 2013 at 16:54
  • @EvanCarroll That's a good point - SIGINT is definitely the way to go if you have long-running queries that are important. In this particular case (with the binaries in a potentially unknown state because of the upgrade-while-running on Windows) my goal would be "Stop it ASAP and restart with a known state". It's a matter of picking where you want to be conservative, and all other things being equal if I had long-running queries I'd probably err on letting them go through in this case since it's only a patchlevel version mismatch.
    – voretaq7
    Dec 5, 2013 at 17:43
  • You're supposed to up upvote comments that are a good idea. Dec 5, 2013 at 19:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .