Postgresql Service and Server synch up issue.

["Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@xxxxxxxxxxx>]

Hi All,

 

We are using postgreql 9.2 as a main backend server for our project. We have been using this server since last year. We have configured  postgresql as a windows service.  Till last week, we have not faced any issues to start server from service console management, but last week we have faced a issue that when server was started , service was timed out and pg_ctl.exe closed, while rebooting.

 

This issue is faced by few people as described in below links.

 

http://www.postgresql.org/message-id/flat/E1USOeY-00024n-1Y@xxxxxxxxxxxxxxxxxxxxxxx#E1USOeY-00024n-1Y@xxxxxxxxxxxxxxxxxxxxxxx

http://postgresql.1045698.n5.nabble.com/Unreliable-quot-pg-ctl-w-start-quot-again-td5435767.html

 

Based on the links, I have analyzed the pg_ctl.c code and found that.

 

write_eventlog(EVENTLOG_INFORMATION_TYPE, _("Waiting for server startup...\n"));

if (test_postmaster_connection(true) != PQPING_OK)

{

write_eventlog(EVENTLOG_ERROR_TYPE, _("Timed out waiting for server startup\n"));

      pgwin32_SetServiceStatus(SERVICE_STOPPED);

      return;

}

 

test_postmaster_connection(bool do_checkpoint)

{

…

…

…

      for (i = 0; i < wait_seconds; i++)

      {

…

…

            if (i >= 5)

            {

                  struct stat statbuf;

 

                  if (stat(pid_file, &statbuf) != 0)

                        return PQPING_NO_RESPONSE;

 

                  if (found_stale_pidfile)

                  {

                        write_stderr(_("\n%s: this data directory appears to be running a pre-existing postmaster\n"),

                                          progname);

                        return PQPING_NO_RESPONSE;

                  }

            }

      }

 

It is checking only 5 seconds, whether postmaster.pid is available or not. If not then it will send “Timed out waiting for server startup” log and followed by this log “The Postgres service entered the stopped state.”

 

As per the link, I can change 5 to 20 or some value to avoid timeout.

 

Please help me here, how can I resolve this issue in a better way.

 

Thanks & Regards,

Ramkumar.