Re: PostgreSQL 7.3.2 running as NT service under Windows XP not always - Mailing list pgsql-cygwin

    I still do not have a clear sense of whether there is a risk of
database corruption when Windows is shutdown/restarted and sends kill
signals to all the running processes like 'postmaster' (as mentioned
earlier, the existence of the 'postmaster.pid' file makes me a little
anxious).  However, I have at least found a hacked solution for myself
to delete the postmaster.pid file on startup.

    There does not appear to be any clean method in the Windows NT/2000/XP
operating systems to execute startup (or shutdown) scripts at specific
points in the process.  Whereas most *nix systems have a very clear
startup path, where you can introduce commands somewhere in the whole
bootstrap->kernel->init->inittab->rc.*->... sequence as
needed--including deleting PID files etc. prior to inetd services
starting up, for example--Windows does not seem to afford this.

    The bootup process in NT/2000/XP follows the usual bootstrapping
(ntldr), followed by the kernel, and then the loading of device drivers,
then services, and then finally executables registered in places like

    HKLM\Software\Microsoft\Windows\Current Version\Run

and then anything located in a user's StartUp folder (which, of course,
requires logging in first).

    But this is too late for our purposes.  And there appear to be no
obvious mechanisms in place to allow you to execute applications along
the way if needed.

    'postmaster' is effectively an NT service, so we need to delete any
existing postmaster.pid file PRIOR to NT services loading.  However, as
noted above, the sequence does not really introduce the ability to add
commands/executables until AFTER all device drivers and NT services are
loaded.

    I have tried everything from C:\autoexec.bat and C:\config.sys to the
ol' Windows 3.1 %SystemRoot%\system.ini and %SystemRoot%\win.ini trick
of using a load= or run= command to the lesser known
%SystemRoot%\Winstart.bat file, but all are ignored by Windows 2000/XP.

    End result:  the best I could come up with was creating another NT
service whose sole purpose in life was to run in the 'postgres' user
context and delete the postmaster.pid and \tmp\.s.PGSQL* lock files, and
then making the 'postmaster' service depend on that service.  This way,
'postmaster' won't start until AFTER the postmaster.pid file has been
deleted (if it exists).

    Please note a few caveats though.  Do not try creating a shell script
or .BATch file which you then setup as an NT service via cygrunsrv.  At
least when I tried, doing so led to the service firing up, executing the
script, and--and this next part is really important--SHUTTING DOWN
AGAIN.  The problem here is that if you have 'postmaster' depend on this
service, it will never fire up, as the startup sequence will go

    * system startup sequence
    * NT service to delete postmaster.pid service starts up,
      executes, and shuts down
    * 'postmaster' attempts to startup, but finding the above
      service shutdown, fails since it depends on this service.

My personal solution involved using an already owned copy of FireDaemon
(www.firedaemon.com)--so there was no out-of-pocket expense for me.
FireDaemon is basically a Windows utility similar to cygrunsrv, only
FireDaemon has much greater flexibility.  Specifically, I can have
FireDaemon launch a .BATch file, but not monitor the execution, so when
the .BATch file terminates, the FireDaemon service continues to 'run'.
This tricks 'postmaster' effectively into believing the service is still
running, so 'postmaster' fires up as it should once the
FireDaemon-created service has started (i.e., the postmaster.pid file
has been deleted).

In other words,

  1.    I created a simple .BATch file to delete the necessary files
    (note this is a .BATch file, not a Cygwin BASH shell script,
    so paths are set accordingly):

    __________________________________________________
    @echo off
    del c:\cygwin\tmp\.s.PGSQL*
    del c:\cygwin\usr\share\postgresql\data\postmaster.pid
    __________________________________________________

  2.    I then created an NT service via FireDaemon named
    'cygwin-start' which launched this .BATch file as a console
    app, running it hidden, etc.

  3.    Using the Cygwin BASH shell, I shutdown and removed the
    'postmaster' service, then rebuilt the service with a
    modified command to make 'postmaster' depend on my new
    FireDaemon-created service, as follows:

    __________________________________________________
    $ net stop postmaster
    $ cygrunsrv --remove postmaster
    $ cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D
/usr/share/postgresql/data -i" --dep ipc-daemon --dep cygwin-start
--termsig INT --user postgres --shutdown
    __________________________________________________


Voila!  It's not pretty, but it works.  And unlike shutdown
scripts--also not inherent in Windows NT/2000/XP--this handles the case
of power outages and sudden power failure where 'postmaster' doesn't
even get a chance to properly shutdown.  (As for the administrative view
of these situations, I'll leave that to the reader to determine their
needs.)

    Please note this still doesn't answer the question why the
postmaster.pid file is left behind sometimes on Windows
shutdown/restart.  But for those needing their Cygwin PostgreSQL
database up and running on startup without issue, this is just one
possible scenario.

P.S.    If I can figure out a way to use cygrunsrv to create a service
    that runs a script and then remains active (so 'postmaster'
    will load), I'll post here.  Thus far, howerver, basic attempts
    like creating a service that points to a shell script fire up,
    execute the script, and then immediately shutdown.  Possibly
    having a script that launches another shell that runs the script
    would work, as the shell would still be 'running'.  But haven't
    tried yet, so don't know.