Re:

*Well the above would indicate it is not being shutdown properly.How are
you shutting it down?*

PSQL server starterd as stoped by postgresql windows service. So when I
shutdown the machine postgres shutting down by postgres windows service

*What and how are you installing?*

Using windows binary zip file, I extract zip file and create cluster using
intidb then register windows service using pg_ctl

*Who are you running the service as?*

I try to start the psql server from command prompt using pg_ctl.exe

On 7/22/2014 11:14 PM, Kalai R wrote:
> *Well the above would indicate it is not being shutdown properly.
>
> How are you shutting it down?*
> *
> *
> PSQL server starterd as stoped by postgresql windows service. So when
> I shutdown the machine postgres shutting down by postgres windows service
>
> *What and how are you installing?*
> *
> *
> Using windows binary zip file, I extract zip file and create cluster
> using intidb then register windows service using pg_ctl
> *
> *
> *Who are you running the service as?**
> *
> *
> *
> I try to start the psql server from command prompt using pg_ctl.exe

why not use the installer, which sets it all up correctly for you?

I think the problem is, you ran initdb as your user account, but the
system service is running as the special system accounbt
NETWORK_SERVICE, or similar, and there may be files in the data
directory which the NETWORK_SERVICE account doesn't have write access
writes to. you can probably fix this with the cacls command.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

*why not use the installer, which sets it all up correctly for you?*

In previous, I use installer. but I face more data corruption problem and
also server starting problem when shutting down the system. so I try to
install my self.

*I think the problem is, you ran initdb as your user account, but the
system service is running as the special system accounbt NETWORK_SERVICE,
or similar, and there may be files in the data directory which the
NETWORK_SERVICE account doesn't have write access writes to. you can
probably fix this with the cacls command.*

I think you are correct. But I want to know why database corrupted even
though I shutting down the machine properly. This is frequently happen when
I use installer also.

On Wed, Jul 23, 2014 at 11:55 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 7/22/2014 11:14 PM, Kalai R wrote:
>
>
>
> *Well the above would indicate it is not being shutdown properly. How are
> you shutting it down?*
>
> PSQL server starterd as stoped by postgresql windows service. So when I
> shutdown the machine postgres shutting down by postgres windows service
>
> *What and how are you installing?*
>
> Using windows binary zip file, I extract zip file and create cluster
> using intidb then register windows service using pg_ctl
>
> *Who are you running the service as?*
>
> I try to start the psql server from command prompt using pg_ctl.exe
>
>
> why not use the installer, which sets it all up correctly for you?
>
> I think the problem is, you ran initdb as your user account, but the
> system service is running as the special system accounbt NETWORK_SERVICE,
> or similar, and there may be files in the data directory which the
> NETWORK_SERVICE account doesn't have write access writes to. you can
> probably fix this with the cacls command.
>
>
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>

On 7/22/2014 11:42 PM, Kalai R wrote:
> *why not use the installer, which sets it all up correctly for you?*
> *
> *
> In previous, I use installer. but I face more data corruption problem
> and also server starting problem when shutting down the system. so I
> try to install my self.
>
>
> *I think the problem is, you ran initdb as your user account, but the
> system service is running as the special system accounbt
> NETWORK_SERVICE, or similar, and there may be files in the data
> directory which the NETWORK_SERVICE account doesn't have write access
> writes to. you can probably fix this with the cacls command.*
> *
> *
> I think you are correct. But I want to know why database corrupted
> even though I shutting down the machine properly. This is frequently
> happen when I use installer also.
>

It seems like Windows isn't waiting for the service to exit cleanly
before its pulling the plug. Is this a Windows *server* version, or is
it one of the newer desktop OS's like Windows 8? Windows 8 seems to
shut down rather fast, its quite possible its not bothering to wait for
services to exit.

this error...
*FATAL: could not remove symbolic link "pg_tblspc/17681": Permission
denied*
*CONTEXT: xlog redo create tablespace: 17681 "C:/GLOIER/gPRO/Data/GT"
*
suggests you're using a postgres tablespace ? I've not used tablespaces
much or at all in Windows, I don't know how well they work.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

* Is this a Windows *server* version, or is it one of the newer desktop
OS's like Windows 8? Windows 8 seems to shut down rather fast, its quite
possible its not bothering to wait for services to exit.*

I face this problem in Windows XP and Windows 7 but not all windows XP and
Windows 7.
In Windows 8 and Windows Server no problem.

*It seems like Windows isn't waiting for the service to exit cleanly before
its pulling the plug.*

I think so, but I don't know how to solve this problem.

On Wed, Jul 23, 2014 at 12:20 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 7/22/2014 11:42 PM, Kalai R wrote:
>
> *why not use the installer, which sets it all up correctly for you?*
>
> In previous, I use installer. but I face more data corruption problem and
> also server starting problem when shutting down the system. so I try to
> install my self.
>
>
> *I think the problem is, you ran initdb as your user account, but the
> system service is running as the special system accounbt NETWORK_SERVICE,
> or similar, and there may be files in the data directory which the
> NETWORK_SERVICE account doesn't have write access writes to. you can
> probably fix this with the cacls command.*
>
> I think you are correct. But I want to know why database corrupted even
> though I shutting down the machine properly. This is frequently happen when
> I use installer also.
>
>
> It seems like Windows isn't waiting for the service to exit cleanly before
> its pulling the plug. Is this a Windows *server* version, or is it one of
> the newer desktop OS's like Windows 8? Windows 8 seems to shut down
> rather fast, its quite possible its not bothering to wait for services to
> exit.
>
> this error...
> *FATAL: could not remove symbolic link "pg_tblspc/17681": Permission
> denied*
>
> *CONTEXT: xlog redo create tablespace: 17681 "C:/GLOIER/gPRO/Data/GT" *
> suggests you're using a postgres tablespace ? I've not used tablespaces
> much or at all in Windows, I don't know how well they work.
>
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>

On 07/22/2014 11:59 PM, Kalai R wrote:
> * Is this a Windows *server* version, or is it one of the newer desktop
> OS's like Windows 8? Windows 8 seems to shut down rather fast, its
> quite possible its not bothering to wait for services to exit.*
> *
> *
> I face this problem in Windows XP and Windows 7 but not all windows XP
> and Windows 7.
> In Windows 8 and Windows Server no problem.
> *
> *
> *It seems like Windows isn't waiting for the service to exit cleanly
> before its pulling the plug.**
> *
> *
> *
> I think so, but I don't know how to solve this problem.
>

First would be to show the log entries from the period immediately
before the server shutdown incorrectly.

From the previous go around on this:

http://www.postgresql.org/message-id/CAGxuanNsgNDTCv1bm+ap2tSr=vztGca_yvAFrXwW=9W0o3MOPw@mail.gmail.com

you indicated that you application does things when installing itself
and the server.

It would be nice to know what that is?

In particular an outline of the exact steps you application takes during
the install.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

I already give log entries before the shutdown incorrectly, in the previous
post

http://www.postgresql.org/message-id/CAGxuanMC4ZwcjqNCaQn-qkTJ5Kzf1pEVVT9o_9wfTet8kR_Rqw@mail.gmail.com

Also I explain the steps to install in the same post

http://www.postgresql.org/message-id/CAGxuanO8JgXepLpfXg1WHAOpuNFNRH_5HpZE0jvhQ9zGRQ_FNg@mail.gmail.com

On Wed, Jul 23, 2014 at 7:39 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 07/22/2014 11:59 PM, Kalai R wrote:
>
>> * Is this a Windows *server* version, or is it one of the newer desktop
>>
>> OS's like Windows 8? Windows 8 seems to shut down rather fast, its
>> quite possible its not bothering to wait for services to exit.*
>> *
>>
>> *
>> I face this problem in Windows XP and Windows 7 but not all windows XP
>> and Windows 7.
>> In Windows 8 and Windows Server no problem.
>> *
>> *
>> *It seems like Windows isn't waiting for the service to exit cleanly
>> before its pulling the plug.**
>> *
>> *
>>
>> *
>> I think so, but I don't know how to solve this problem.
>>
>>
> First would be to show the log entries from the period immediately before
> the server shutdown incorrectly.
>
> From the previous go around on this:
>
> http://www.postgresql.org/message-id/CAGxuanNsgNDTCv1bm+
> ap2tSr=vztGca_yvAFrXwW=9W0o3MOPw(at)mail(dot)gmail(dot)com
>
> you indicated that you application does things when installing itself and
> the server.
>
> It would be nice to know what that is?
>
> In particular an outline of the exact steps you application takes during
> the install.
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

On 23 July 2014 08:59, Kalai R <softlinne(dot)kv(at)gmail(dot)com> wrote:

> * Is this a Windows *server* version, or is it one of the newer desktop
> OS's like Windows 8? Windows 8 seems to shut down rather fast, its quite
> possible its not bothering to wait for services to exit.*
>
> I face this problem in Windows XP and Windows 7 but not all windows XP
> and Windows 7.
> In Windows 8 and Windows Server no problem.
>

> *It seems like Windows isn't waiting for the service to exit cleanly
> before its pulling the plug.*
>
> I think so, but I don't know how to solve this problem.
>
Windows will kill service processes on shutdown based on
WaitToKillServiceTimeout registry setting (this will vary by OS, and other
application might change it).
This value can be lowered by some stupid 'pseudo' OS optimizer.
Try to increase WaitToKillServiceTimeout value to for example 300000 (it's
5 min.), restart OS, and see if you can still reproduce problem on
shutdown. It problem will appear again and system shutdown hangs for 5min
then you can suspect some deadlock.

There is also bug in Windows 7/Windows Server 2008 R2 (even with SP1, and
all fixes from Windows Update):
http://support.microsoft.com/kb/2549760 (only hotfix - not available on
Windows Update) - without this hotfix changing WaitToKillServiceTimeout doesn't
have any effect!

Windows 2003 Small Business Server installer incorrectly set this value
type: http://support.microsoft.com/kb/839262 - this one might be fixed in
SP1, but I haven't time/need to test it.

(Notes below might not be helpful for this case, but it describes
problems/solutions that I had with Postgres working as a Windows service).

Another note about Windows services and shutdown is that service dependency
isn't considered during system shutdown (this one is documented/by design).
However here I think that Postgres have a problem when working as a Windows
service:
- main postmaster and forked sub-postmasters processes calls
pgwin32_signal_initialize()
(src\backend\main\main.c and src\backend\postmaster\postmaster.c),
- pgwin32_signal_initialize() registers console handler by
SetConsoleCtrlHandler(pg_console_handler,
...) - this is registered also when PG works as a Windows service,
- when pg_console_handler receives CTRL_SHUTDOWN_EVENT from OS, then it
calls pg_queue_signal(SIGINT).

Problems:
- when OS is in shutdown path, then it sends CTRL_SHUTDOWN_EVENT, and *all*
Postgres processes (main and sub/forked) will call pg_queue_signal(SIGINT)
- so main and sub processes will start to shutdown independently? Can this
have any bad consequences?
- even if Postgres is running as a Windows service (for example by pg_ctl),
then postgres.exe process can exit even before pg_ctl will receive event
about shutdown - this one was a problem for me, because in our app. I need
to wait first that our service stops, and then I let Postgres to stop (I
have my own service - not pg_ctl - which will start/stop postgres.exe
process).

I think that CTRL_SHUTDOWN_EVENT should be removed from pg_console_handler,
because as docs says:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms683242(v=vs.85).aspx

CTRL_SHUTDOWN_EVENT
A signal that the system sends when the system is shutting down.
Interactive applications are not present by the time the system sends this
signal, therefore it can be received only be services in this situation.

so it will be called only for service processes (because app. processes
won't be preset on shutdown),
but in that case, service should be responsible to send shutdown signal to
Postgres - like pg_ctl do it by kill(postmasterPID, SIGINT).

This code with CTRL_SHUTDOWN_EVENT (intially in libpg/pgsignal.c) is since:
Revision: f06e79525a57ccbf54ae5d0b673cd904ca978d67
Date: 2004-02-08 23:28:57
so I'm not sure if I'm missing something here, or this bug wasn't noticed
for 10 years :)

My workaround: own service which will start/stop Postgres (mainly to wait
for our service to stop, running initdb.exe in context of NetworkService,
etc.).
I've also written small Postgres module loaded via shared_preload_libraries
which will call SetConsoleCtrlHandler, and my HandlerRoutine which simply
returns TRUE. Because of this pg_console_handler won't be called (so no
processing CTRL_SHUTDOWN_EVENT by any Postgress process).

Best regards,
Krystian Bigaj

Krystian Bigaj <krystian(dot)bigaj(at)gmail(dot)com> writes:
> - when pg_console_handler receives CTRL_SHUTDOWN_EVENT from OS, then it
> calls pg_queue_signal(SIGINT).

> Problems:
> - when OS is in shutdown path, then it sends CTRL_SHUTDOWN_EVENT, and *all*
> Postgres processes (main and sub/forked) will call pg_queue_signal(SIGINT)
> - so main and sub processes will start to shutdown independently? Can this
> have any bad consequences?

Hm. We ought to have that sending SIGTERM instead, so as to mimic the
situation when Unix "init" is trying to shut down the system. It might be
that SIGINT will more or less work, but the postmaster logic is designed
to work with global SIGTERM as being the clean-up-ASAP trigger. As an
example, backends servicing remote applications (which will *not* have
already gotten killed) would not exit in response to SIGINT.

> I think that CTRL_SHUTDOWN_EVENT should be removed from pg_console_handler,

That does not sound like a good idea, at least not if Windows has the
same behavior as "init" does of proceeding to hard kills after some
grace period.

regards, tom lane

On 07/23/2014 10:30 AM, Kalai R wrote:
> I already give log entries before the shutdown incorrectly, in the
> previous post
>
> http://www.postgresql.org/message-id/CAGxuanMC4ZwcjqNCaQn-qkTJ5Kzf1pEVVT9o_9wfTet8kR_Rqw@mail.gmail.com
>
>
> Also I explain the steps to install in the same post
>
> http://www.postgresql.org/message-id/CAGxuanO8JgXepLpfXg1WHAOpuNFNRH_5HpZE0jvhQ9zGRQ_FNg@mail.gmail.com
>
>

Yes, but these are for a different iteration of your application. One
where you where using the installer, but doing a silent install. If I am
following correctly you have changed that procedure:

"
What and how are you installing?

Using windows binary zip file, I extract zip file and create cluster
using intidb then register windows service using pg_ctl

"

So I figured, new procedure, new logs on the theory that the errors
would not be the same.

So, do you have logs from this most recent problem and are the errors
the same?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

On 23 July 2014 22:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Krystian Bigaj <krystian(dot)bigaj(at)gmail(dot)com> writes:
> > - when pg_console_handler receives CTRL_SHUTDOWN_EVENT from OS, then it
> > calls pg_queue_signal(SIGINT).
>
> > Problems:
> > - when OS is in shutdown path, then it sends CTRL_SHUTDOWN_EVENT, and
> *all*
> > Postgres processes (main and sub/forked) will call
> pg_queue_signal(SIGINT)
> > - so main and sub processes will start to shutdown independently? Can
> this
> > have any bad consequences?
>
> Hm. We ought to have that sending SIGTERM instead, so as to mimic the
> situation when Unix "init" is trying to shut down the system. It might be
> that SIGINT will more or less work, but the postmaster logic is designed
> to work with global SIGTERM as being the clean-up-ASAP trigger. As an
> example, backends servicing remote applications (which will *not* have
> already gotten killed) would not exit in response to SIGINT.
>
> > I think that CTRL_SHUTDOWN_EVENT should be removed from
> pg_console_handler,
>
> That does not sound like a good idea, at least not if Windows has the
> same behavior as "init" does of proceeding to hard kills after some
> grace period.
>
> regards, tom lane
>

I'm not really familiar with Unix and it's SIG-commands. I know only
about SIGINT/SIGTERM from Postgres documentation.

However form what I see is that when Postgress is running by pg_ctl from
service, then it will receive SIGINT (independently and in general in
unspecified order)
- *each* postgres.exe process will queue itself SIGINT (because of
CTRL_SHUTDOWN_EVENT),
- pg_ctl will send SIGINT to main postmaster process (and possibly it will
pass that command to sub-processes)
So there are two independent paths where SIGINT are sent, and pg_ctl
doesn't have really a control when postgres.exe receives SIGINT.
This CTRL_SHUTDOWN_EVENT is not used when postgres.exe is run on *user
session* - so removing it won't change anything.

I see only two cases where CTRL_SHUTDOWN_EVENT might be need (all of there
where postgres.exe is run on service session):
- postgres.exe run by pg_ctl.exe, but pg_ctl service process was
terminated/killed, and then system was shutdown
- someone starts postgres.exe from own service, but doesn't send
SIGINT/SIGTERM command to postgres.exe on service system shutdown (but he
must for service stop)
As I previously wrote, I have workaround for it, so if you think that this
change would break compatibility and don't want to change it, then I'm
really fine with it.

However I've probably found something with pg_ctl.c regarding shutdown and
maybe that suspicious postgres.exe process termination on Windows.

1) shutdownEvent is signaled in pgwin32_ServiceMain
by SERVICE_CONTROL_STOP/SERVICE_CONTROL_SHUTDOWN in pgwin32_ServiceHandler
There is dwWaitHint = 10000.

2)
...
/* Wait for quit... */
ret = WaitForMultipleObjects(2, shutdownHandles, FALSE, INFINITE);

pgwin32_SetServiceStatus(SERVICE_STOP_PENDING);
switch (ret)
{
case WAIT_OBJECT_0: /* shutdown event */
kill(postmasterPID, SIGINT);

/*
* Increment the checkpoint and try again Abort after 12
* checkpoints as the postmaster has probably hung
*/
while (WaitForSingleObject(postmasterProcess, 5000) == WAIT_TIMEOUT &&
status.dwCheckPoint < 12)
status.dwCheckPoint++; <---- missing call
to pgwin32_SetServiceStatus(SERVICE_STOP_PENDING)
or SetServiceStatus(hStatus, (LPSERVICE_STATUS) &status);
break;
...

There is incremented dwCheckPoint every 5000ms, but that status is not
updated (missing pgwin32_SetServiceStatus/SetServiceStatus), so SCM after
10s (dwWaitHint = 10000) will not receive incremented dwCheckPoint, and
it's allowed to kill that process (because this service didn't respond with
dwWaitHint). It kills pg_ctl.exe, but when all services stops, then it
simply terminates other remaining processes - in this case postgres.exe

http://msdn.microsoft.com/en-us/library/windows/desktop/ms685996(v=vs.85).aspx
dwWaitHint:
...
If the amount of time specified by dwWaitHint passes, and dwCheckPoint has
not been incremented or dwCurrentState has not changed, the service control
manager or service control program can assume that an error has occurred
and the service should be stopped.
...

So if main postgres.exe (run by pg_ctl.exe service process) won't shutdown
in 10s (for any reason) it might be terminated/killed by Windows/SCM.

Best regards,
Krystian Bigaj

Thank You so much Krystian Bigaj.

Since last 2 years I had suffering this problem. But today I got solution
from you.
I am developing .Net application with Postgres.
I am using WCF service (host as windows service) to connect postgres
database.

*My workaround: own service which will start/stop Postgres (mainly to wait
for our service to stop, running initdb.exe in context of NetworkService,
etc.).*
*I've also written small Postgres module loaded via
shared_preload_libraries which will call SetConsoleCtrlHandler, and my
HandlerRoutine which simply returns TRUE. Because of
this pg_console_handler won't be called (so no processing
CTRL_SHUTDOWN_EVENT by any Postgress process).*

* I need to wait first that our service stops, and then I let Postgres to
stop (I have my own service - not pg_ctl - which will start/stop
postgres.exe process).*

I have similar situation to you. I also want create my own service to start
/stop postgres. Please let me know how to start/stop postgres without
pg_ctl. Could you share code for your small postgres module loaded via
shared_preloaded libraries?

Thanks & Regards
Kalai

On Thu, Jul 24, 2014 at 3:59 AM, Krystian Bigaj <krystian(dot)bigaj(at)gmail(dot)com>
wrote:

> On 23 July 2014 22:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Krystian Bigaj <krystian(dot)bigaj(at)gmail(dot)com> writes:
>> > - when pg_console_handler receives CTRL_SHUTDOWN_EVENT from OS, then it
>> > calls pg_queue_signal(SIGINT).
>>
>> > Problems:
>> > - when OS is in shutdown path, then it sends CTRL_SHUTDOWN_EVENT, and
>> *all*
>> > Postgres processes (main and sub/forked) will call
>> pg_queue_signal(SIGINT)
>> > - so main and sub processes will start to shutdown independently? Can
>> this
>> > have any bad consequences?
>>
>> Hm. We ought to have that sending SIGTERM instead, so as to mimic the
>> situation when Unix "init" is trying to shut down the system. It might be
>> that SIGINT will more or less work, but the postmaster logic is designed
>> to work with global SIGTERM as being the clean-up-ASAP trigger. As an
>> example, backends servicing remote applications (which will *not* have
>> already gotten killed) would not exit in response to SIGINT.
>>
>> > I think that CTRL_SHUTDOWN_EVENT should be removed from
>> pg_console_handler,
>>
>> That does not sound like a good idea, at least not if Windows has the
>> same behavior as "init" does of proceeding to hard kills after some
>> grace period.
>>
>> regards, tom lane
>>
>
> I'm not really familiar with Unix and it's SIG-commands. I know only
> about SIGINT/SIGTERM from Postgres documentation.
>
> However form what I see is that when Postgress is running by pg_ctl from
> service, then it will receive SIGINT (independently and in general in
> unspecified order)
> - *each* postgres.exe process will queue itself SIGINT (because of
> CTRL_SHUTDOWN_EVENT),
> - pg_ctl will send SIGINT to main postmaster process (and possibly it will
> pass that command to sub-processes)
> So there are two independent paths where SIGINT are sent, and pg_ctl
> doesn't have really a control when postgres.exe receives SIGINT.
> This CTRL_SHUTDOWN_EVENT is not used when postgres.exe is run on *user
> session* - so removing it won't change anything.
>
> I see only two cases where CTRL_SHUTDOWN_EVENT might be need (all of there
> where postgres.exe is run on service session):
> - postgres.exe run by pg_ctl.exe, but pg_ctl service process was
> terminated/killed, and then system was shutdown
> - someone starts postgres.exe from own service, but doesn't send
> SIGINT/SIGTERM command to postgres.exe on service system shutdown (but he
> must for service stop)
> As I previously wrote, I have workaround for it, so if you think that this
> change would break compatibility and don't want to change it, then I'm
> really fine with it.
>
> However I've probably found something with pg_ctl.c regarding shutdown and
> maybe that suspicious postgres.exe process termination on Windows.
>
> 1) shutdownEvent is signaled in pgwin32_ServiceMain
> by SERVICE_CONTROL_STOP/SERVICE_CONTROL_SHUTDOWN in pgwin32_ServiceHandler
> There is dwWaitHint = 10000.
>
> 2)
> ...
> /* Wait for quit... */
> ret = WaitForMultipleObjects(2, shutdownHandles, FALSE, INFINITE);
>
> pgwin32_SetServiceStatus(SERVICE_STOP_PENDING);
> switch (ret)
> {
> case WAIT_OBJECT_0: /* shutdown event */
> kill(postmasterPID, SIGINT);
>
> /*
> * Increment the checkpoint and try again Abort after 12
> * checkpoints as the postmaster has probably hung
> */
> while (WaitForSingleObject(postmasterProcess, 5000) == WAIT_TIMEOUT &&
> status.dwCheckPoint < 12)
> status.dwCheckPoint++; <---- missing call
> to pgwin32_SetServiceStatus(SERVICE_STOP_PENDING)
> or SetServiceStatus(hStatus, (LPSERVICE_STATUS) &status);
> break;
> ...
>
> There is incremented dwCheckPoint every 5000ms, but that status is not
> updated (missing pgwin32_SetServiceStatus/SetServiceStatus), so SCM after
> 10s (dwWaitHint = 10000) will not receive incremented dwCheckPoint, and
> it's allowed to kill that process (because this service didn't respond with
> dwWaitHint). It kills pg_ctl.exe, but when all services stops, then it
> simply terminates other remaining processes - in this case postgres.exe
>
>
> http://msdn.microsoft.com/en-us/library/windows/desktop/ms685996(v=vs.85).aspx
> dwWaitHint:
> ...
> If the amount of time specified by dwWaitHint passes, and dwCheckPoint has
> not been incremented or dwCurrentState has not changed, the service control
> manager or service control program can assume that an error has occurred
> and the service should be stopped.
> ...
>
> So if main postgres.exe (run by pg_ctl.exe service process) won't shutdown
> in 10s (for any reason) it might be terminated/killed by Windows/SCM.
>
> Best regards,
> Krystian Bigaj
>
>

On 24 July 2014 07:51, Kalai R <softlinne(dot)kv(at)gmail(dot)com> wrote:

> Thank You so much Krystian Bigaj.
>
> Since last 2 years I had suffering this problem. But today I got solution
> from you.
> I am developing .Net application with Postgres.
> I am using WCF service (host as windows service) to connect postgres
> database.
>
> *My workaround: own service which will start/stop Postgres (mainly to wait
> for our service to stop, running initdb.exe in context of NetworkService,
> etc.).*
> *I've also written small Postgres module loaded via
> shared_preload_libraries which will call SetConsoleCtrlHandler, and my
> HandlerRoutine which simply returns TRUE. Because of
> this pg_console_handler won't be called (so no processing
> CTRL_SHUTDOWN_EVENT by any Postgress process).*
>
> * I need to wait first that our service stops, and then I let Postgres to
> stop (I have my own service - not pg_ctl - which will start/stop
> postgres.exe process).*
>
>
> I have similar situation to you. I also want create my own service to
> start /stop postgres. Please let me know how to start/stop postgres without
> pg_ctl. Could you share code for your small postgres module loaded via
> shared_preloaded libraries?
>
How to start postgres you can find here:
http://www.postgresql.org/docs/9.3/static/server-start.html
You could even use pg_ctl to start/stop postgres.exe process from your
service, but not using pg_ctl as service handler - I think that this should
be sufficient for you, and it's very easy to do, just simply run:
- pg_ctl.exe start -D "data dir" -o "--shared_preload_libraries=your.dll"
and to stop run:
- pg_ctl.exe stop D "data dir"
from your service.

For additional parameters read docs at:
http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html

My PG module looks like this (pseudo code, my code is in Delphi):

dllMain(...)
{
switch(reason)
case DLL_PROCESS_ATTACH:
SetConsoleCtrlHandler(HandlerRoutine, TRUE);

case DLL_PROCESS_DETACH:
SetConsoleCtrlHandler(HandlerRoutine, FALSE);
}

BOOL HandlerRoutine(...)
{
return TRUE; // this will prevent call to next handler routine declared
in postgres process: src\backend\port\win32\singal.c: pg_console_handler <-
won't be called
}

Also that DLL module must export function Pg_magic_func which returns
static/global pointer to initialized Pg_magic_struct structure:
http://doxygen.postgresql.org/structPg__magic__struct.html

To start/stop postgres.exe I'm using:
- CreateProcess - starting postgres.exe, here I'm adding that
--shared_preload_libraries
- CallNamedPipe - sending stop or ping signal (parts of code
from src\port\kill.c)
- RegisterWaitForSingleObject - notification if postgres.exe process will
be abnormally terminated (in that case I do simple logging and terminating
my DB service - to not leave zombie service without really working
postgres.exe)
- pg_isready.exe - while starting service I'm waitng for Postgres to be
ready to accecpt connections, if it's ready, then I finally set my db
service state as running.
- on service start I'm checking if any postgres.exe is running on my
cluster directory, and I'm sending SIGTERM to it, and waiting for process
terminate
- stopping service: before I will send SIGINT signal to postgres, first I
wait for my main service appication to close clearly.
- my DB service also creates database cluster (initdb.exe) - because it's
too complicated to run initdb.exe on NetworkService while running on user
session (I know that I could use cacls/icacls - and I did before, but I had
customers who had mess in ACLs on installation drive)

Best regards,
Krystian Bigaj