Postgresql server is running, but service appears stopped

postgresqlpostgresql-9.4

I am running Postgresql 9.4 as a service on Windows Server 2008 R2. It usually works as a charm, but now there is a situation when the server is apparently running (I can connect to it, Task Manager shows the running processes), but the Windows Service appears stopped.

I tried using pg_ctl, but I get:

$>pg_ctl restart -D c:\psqldata94
pg_ctl: old server process (PID: 1436) seems to be gone
starting server anyway
server starting
2016-02-23 09:59:00 CET  LOG:  could not bind IPv6 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port  5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  LOG:  could not bind IPv4 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  WARNING:  could not create listen socket for "*"
2016-02-23 09:59:00 CET  FATAL:  could not create any TCP/IP sockets

$>pg_ctl stop
pg_ctl: PID file "c:/psqldata94/postmaster.pid" does not exist
Is server running?

I tried looking for help online, but the only thing I could find, was this ominous post at psql-admin mailing list.

It's exactly as the author of the post describes: the server is running, but it does not respond to commands. I suppose I can restart the underlying Windows server, but obviously this could have grave consequences if I just kill the running processes.

Does anybody know how to gracefully stop the server under this circumstances?

Best Answer

For me, the answer lied in more careful reading of the excellent manual. Particularly the following three points:

The general discussion about server shutdown is here.

So what I did was to terminate all the backends that were visible through pg_stat_activity view by pg_terminate_backend and then logging onto the server and running pg_ctl kill TERM <pid> to the remaining running postgres processes.

Related Question