how do I gracefully kill stale server process postgres

Question

Occasionally in our lab, our postgres 8.3 database will get orphaned from the pid file, and we get this message when trying to shut down the database:

Error: pid file is invalid, please manually kill the stale server process postgres

When this happens, we immediately do a pg_dump so we can restore the database later. But, if we just kill -9 the orphan postgres process and then start it, the database starts only with the data from the last successful shutdown. But if you psql to it before killing it, the data is all available, thus why the pg_dump works.

Is there a way to gracefully shutdown the orphaned postgres process so we don't have to go through the pg_dump and restore? Or is there a way to have the database recover after killing the orphaned process?

Answer 1

According to the documentation you could either send SIGTERM or SIGQUIT. SIGTERM is preferred. Either way never use SIGKILL (as you know from personal experience).

Edit: on the other hand what you experience is not normal and could indicate a mis-configuration or a bug. Please, ask for assistance on the pgsql-admin mailing list.

Answer 2

Never use kill -9.

And I would strongly advice you to try to figure out exactly how this happens. Where exactly does the error message come from? It's not a PostgreSQL error message. Are you by any chance mixing different ways to start/stop the server (initscripts sometimes and pg_ctl sometimes, for example)? That could probably cause things to go out of sync.

But to answer the direct question - use a regular kill (no -9) on the process to shut it down. Make sure you kill all the postgres processes if there is more than one running.

The database will always do an automatic recovery whenever it's shut down. This shuold happen with kill -9 as well - any data that is committed should be up there. This almost sounds like you have two different data directories mounted on top of each other or something like that - this has been a known issue with NFS at least before.