Thread:
Well the above would indicate it is not being shutdown properly.
How are you shutting it down?
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 serviceWhat 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_ctlWho 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?
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@hogranch.com> wrote:
why not use the installer, which sets it all up correctly for you?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 serviceWhat 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_ctlWho are you running the service as?I try to start the psql server from command prompt using pg_ctl.exe
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.
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@hogranch.com> wrote:
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.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.
this error...FATAL: could not remove symbolic link "pg_tblspc/17681": Permission deniedCONTEXT: 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.klaver@aklaver.com
I already give log entries before the shutdown incorrectly, in the previous post
Also I explain the steps to install in the same post
On Wed, Jul 23, 2014 at 7:39 PM, Adrian Klaver <adrian.klaver@aklaver.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 desktopquite possible its not bothering to wait for services to exit.*
OS's like Windows 8? Windows 8 seems to shut down rather fast, its
**
*
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.klaver@aklaver.com
On 23 July 2014 08:59, Kalai R <softlinne.kv@gmail.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:
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
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.klaver@aklaver.com
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.bigaj@gmail.com> wrote:
I'm not really familiar with Unix and it's SIG-commands. I know only about SIGINT/SIGTERM from Postgres documentation.On 23 July 2014 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:Krystian Bigaj <krystian.bigaj@gmail.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 laneHowever 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_ServiceHandlerThere 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.exedwWaitHint:...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.kv@gmail.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
Krystian Bigaj