Re: Firedaemon and PosgreSQL Restarting - Cab Frank Seesink - Mailing list pgsql-cygwin

My Deja wrote:
> Hello Frank,
>
> thanks for answering, my previous post dropped below my mail window's
> latest email list and for a while I thought you had deserted the
> PostgreSQL world and gone over to the worlds of Oracle and SQL Server :-).

Hehehe, you're funny. :-)  No no, that's not really my style. ;-)  I did
drop off for awhile as for checking the mailing list, but that's about
it.  (I get so much email that I have my subscription set not to send me
email.  Instead, I use Mozilla's newsreader and hook into Gmane.org's
site; i.e., I manually 'poll' the mailing list rather than have it
'interrupt driven' by filling my Inbox.  When I get busy, more time
passes between checks. :-/)

Besides, I believe all software products, including Oracle and SQL
Server, have their place.  (Just don't ask me where that place is.  I
want to remain civilized. ;-) )

Seriously, though, read your setup, and you seem pretty current.  That
helps.  The latest versions of Cygwin, CygIPC, etc., are quite solid.
CygIPC cleans up after itself now, deleting its files when it's
shutdown.  And the term signals sent during a Windows shutdown are now
being dealt with much better than in the past.  In fact, my use of
FireDaemon is now strictly for handling cases where the PostgreSQL box
goes down unexpectedly, not giving pgSQL time to delete its PID file.

End goal:  make sure PID file is deleted on startup prior to PostgreSQL
attempting startup.

For those who do not have FireDaemon, it may be possible to achieve this
using cygrunsrv, but haven't confirmed this.  Basically, we're trying to
achieve what under *nix is rather routine; namely, the orderly startup
sequence of the system to guarantee no PID file on system startup.
Unlike *nix, however, Windows is a real pain in the ass about this, as
its daemons ('services') can be classified in different categories, but
you cannot manually order the startup sequence.  Each boot of a Windows
box can have the services coming up in a different order.  This is a
problem, as you don't want PostgreSQL starting, just to have its PID
file deleted out from under it due to the bootup order being mangled.

The only control you really have in Windows is to define the
'dependencies' between services.  Using Jason's README as a good
example, you can specify that the postmaster service 'depends on' the
ipc-daemon2 service.  This guarantees that any attempt to launch
postmaster will first make sure that ipc-daemon2 is running.  Extending
this concept, we can make postmaster depend not only on ipc-daemon2, but
ALSO on another service, say pgsqlstartup, which is a service that is
effectively a script/batch file that simply makes sure the PostgresQL
PID file has been deleted.

But there's a small catch.  We really only want this sequence--delete
PID, start ipc-daemon2, start postmaster--to occur on Windows startup.
After that, if you do a 'net stop postmaster' and then a 'net start
postmaster', say after you have adjusted the pg_hba.conf settings, you
don't want a script always deleting the PID file first.  Why?  Under
normal conditions, stopping postmaster will automatically delete the PID
file, which is proper.  However, what if postmaster segfaults during
normal operation?  Typically in such cases you want the PID file left to
prevent you from just restarting the postmaster service, as the db files
may require some maintenance/recovery first.  However, if you have
things configured that EVERY time you stop/start postmaster, the script
executes that deletes the PID, you won't have that 'protection.'  Of
course, maybe you want this.  Your call. :-)

One last thing.  Windows dependencies are such that if postmaster
depends on ipc-daemon2, then if ipc-daemon2 is not running, postmaster
will simply not start.  As our simple script/batch file just deletes a
few files, it will run and quit almost instantly, leaving postmaster
unable to start due to this dependency.  Luckily, FireDaemon allows us
to run a script and then claim the service is still running (even though
it's not doing anything), thereby allowing postmaster to start.

Not sure if all this makes sense, so let's just do the steps, ok?

____________________________________________________________
1.  Create a .BATch file that you wish to execute on Windows startup.

     For this example we'll say it's a file C:\BATCH\pgsqlstartup.bat
     which contains the following:
    __________________________________________________
    :* REMOVE PostgreSQL files left behind on bad shutdown, etc.
    chmod 777 C:\cygwin\tmp\.s.PGSQL*
    del /F /Q C:\cygwin\tmp\.s.PGSQL*
    chmod 777 C:\cygwin\var\postgresql\data\postmaster.pid
    del /F /Q C:\cygwin\var\postgresql\data\postmaster.pid
    __________________________________________________

     The chmod commands are needed to guarantee the script CAN delete
     the files.

____________________________________________________________
2.  Run FireDaemon and create a new service.

     The details we'll use for this example are as follows:
    ________________________________________
    'Program' tab
**    Short Name:          [pgsqlstartup                   ]
    Display Name:        [PostgreSQL (system startup)    ]
    Custom Prefix string [ ]
    Description:         [                               ]

    Console Application: [x]
    Working Directory:   [C:\BATCH                       ]
**    Executable:          [C:\BATCH\pgsqlstartup.bat      ]
    Parameters:          [                               ]
    Start-up Time (msec):[3000                           ]
    ________________________________________
    'Settings' tab
    Show Windows:        [Hidden]
    Load Order Group:    [                               ]
**    Logon Account:       [.\postgres]   Password: [****  ]
                                Confirm Password: [****  ]

**    Start-Up Mode:       [Automatic]
**    Upon Program Exit:   [No Action (monitoring is disabled)]
    Graceful Shutdown:   [x]   Max Shutdown Delay(msec):[5000]
    ________________________________________
    ...
    other tabs I believe can be left at their defaults
    ________________________________________

     EXPLANATION:  The lines above marked with '**' are key.
    * The short name is the NT service name.  This is the name
      you use when typing 'net stop <service>', etc.  It is
      like 'postmaster' for PostgreSQL, and we'll be using
      this name below to make postmaster depend on this.
      The display name is what you'll see in the Windows
      management console GUI when looking at services.
    * The executable is the actual .BATch file you'll run,
      created in step #1 above.
    * The logon account is the SAME account used by postmaster.
      This is to make sure the .BATch file has the rights to
      get to, let alone delete, the necessary files.  Also,
      it's generally a bad idea to have a service (ESPECIALLY
      one like this which simply executes a .BATch file) running
      with any more authority than necessary.  If you ran this
      as 'Local System', just think what someone could do if
      they could simply modify the pgsqlstartup.bat file! OW!
    * Start-Up Mode is 'Automatic' to make sure it fires up on
      system startup.
    * The 'No Action' setting is KEY!  This basically has the
      service run (which takes no time at all) and then REMAIN
      listed as a 'Running' NT service.  If you do not do this,
      then the service will execute the .BATch file, the .BATch file
      will terminate, the service will shutdown, and when PostgreSQL
      attempts to start, since it 'depends' on this service,
      will fail to start.

____________________________________________________________
3.  Make PostgreSQL (postmaster) depend on this new service.

In order to achieve this, have Jason's README handy, and notice the step
where you make 'postmaster' an NT service using cygrunsrv (step #4 in
the NT service installation as I write this).  Notice the switch '--dep
ipc-daemon2'.  We simply want to add a second switch, making postmaster
depend on both ipc-daemon2 AND pgsqlstartup.  To do this, we'll execute
the following commands:

REMOVE the current postmaster service:

    $ cygrunsrv --remove postmaster

INSTALL postmaster anew with extra dependency:

    $ cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D
/var/postgresql/data -i" --dep ipc-daemon2 --dep pgsqlstartup --termsig
INT --user postgres --shutdown

____________________________________________________________
4.  Voila!  Test new setup.

If all has gone well, you should be all set.  You may wish to test this
setup by doing the following:

    1.  Shutdown PostgreSQL if it's running.
    2.  Create some fake files to delete (for testing purposes):

        $ touch /var/postgresql/data/postmaster.pid
        $ touch /tmp/.s.PGSQL.test

    3.  Bring up a Command Prompt and execute the following to
        stop/start the pgsqlstartup service:

        C:\> net stop pgsqlstartup
        C:\> net start pgsqlstartup

    4.  Check if the files created in step #2 have been deleted.

If files were deleted, you should be good to go!  Hope this helps.  And
if you have trouble, please let me know.

P.S.    If any of the paths above are incorrect, adjust as needed,
    keeping in mind that the .BATch file uses Windows style "\"s
    whereas Cygwin uses the *nix "/"s.  You may also notice the
    .BATch file gives the absolute path to the files needing to be
    deleted.  If Cygwin's base installation is not in C:\Cygwin,
    be sure to adjust that as well.


> Here are my systems settings.
>
> The OS is Windows 2000 Professional SP2
>
> Firedaemon is version 1.6 GA
>
> Below are my Cygwin/PostgreSQL settings.
>
> $ cygcheck -c cygipc cygrunsrv cygwin postgresql
> Cygwin Package Information
> Package              Version        Status
> cygipc               2.02-1         OK
> cygrunsrv            0.97-1         OK
> cygwin               1.5.5-1        OK
> postgresql           7.4-1          OK
>
> It think it is faily up to date and I dread going through the install
> process again, although I have to admit that Jason's install method is
> correct if you follow it carefully and exactly.
>
> Regards
>
> My Deja
>
>> $ cygcheck -c cygipc cygrunsrv cygwin postgresql
>> Cygwin Package Information
>> Package              Version        Status
>> cygipc               2.02-1         OK
>> cygrunsrv            0.97-1         OK
>> cygwin               1.5.5-1        OK
>> postgresql           7.4-1          OK
>
>
>
>
> Frank Seesink wrote:
>
>> My Deja!
>>
>> Wow.  I've never been asked for help so directly. :-)  Sure, I can try
>> to help.
>>
>> Quick question first if I may, though.  Please describe your
>> configuration in as much detail as possible.  For example, currently I
>> am running
>>
>> * Windows XP Professional SP1a
>>
>> and the output of the command
>>
>>     $ cygcheck -c cygipc cygrunsrv cygwin postgresql
>>
>> yields
>>
>>     Cygwin Package Information
>>     Package              Version        Status
>>     cygipc               2.02-1         OK
>>     cygrunsrv            0.97-1         OK
>>     cygwin               1.5.6-1        OK
>>     postgresql           7.4.1-3        O
>>
>> And as I am using PostgreSQL v7.4.1 and started from scratch using
>> Jason's README, my data (and PID file) is now in /var/postgresql/data.
>>
>> Please note that if you are not running the latest versions of
>> cygipc/postgresql, I highly recommend upgrading if at all possible, as
>> CygIPC is now a proper package of Cygwin (ipc-daemon2 basically), no
>> longer requiring a separate install.  CygIPC2 also properly cleans up
>> after itself on system shutdowns, removing its files.  Of course, if
>> you currently are running anything earlier than PostgreSQL v7.4, you
>> will need to follow the usual procedure of pg_dump'ing your data and
>> then restoring it once you have upgraded PostgreSQL.  And be sure to
>> RE-read Jason's README as the default location of PostgreSQL data has
>> changed (though it's not written in stone or anything).
>>
>> Once you respond, I can try to give you specific instructions on how
>> to set things up so that, even in the case of power/system failure,
>> where PostgreSQL does not shutdown properly, you can be sure that on
>> reboot, your dbms comes up.  It's really not that difficult.  The
>> hardest part is getting the service dependencies right.  Then it's
>> just a matter of a batch file and some basic FireDaemon settings.
>>
>>
>> My Deja wrote:
>>
>>> I am posting this query in relation to these threads at
>>> groups.google.com  search 'firedaemon group:comp.databases.postgresql.*'
>>>
>>> PostgreSQL 7.3.2 running as NT service under Windows XP not always
>>>
>>> and
>>>
>>> Leftover PID files.
>>>
>>> Whenever I have to force a reboot, PostgreSQL does not start until I
>>> remove the PID file.
>>>
>>> I have even acquired FireDaemon to help me deal with the problem, but I
>>> am finding the configuration for PostgreSQL troublesome.
>>>
>>> Can Frank Seesink help me?
>>>
>>> Regards
>>>
>>> My Deja
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>