Postgresql connection timing-out too quickly



  • I'm running a powershell script on windows that connects to a postgres database.

    Under normal conditions everything works fine, but I'm trying to make the system more robust, and make everything re-start automatically after a power failure or re-boot.

    The problem is that although both the script and the database successfully start on boot-up, the script does so much more quickly - in fact it re-starts before the database becomes available.

    The script connects with a connection-string that includes this....

    Timeout=60;CommandTimeout=8
    

    My understand (perhaps flawed?) is that Timeout=60 refers to the connection timeout, and should mean "attempt to connect to the database for up to 60 seconds and if no connection is made quit with an error message". However, in practice the connection attempt fails immediately with the error

    the database system is starting
    

    Why is it failing immediately and not waiting for 60 seconds? Is there any way of testing if the database has re-started before attempting to connect? Or do I just need to keep attempting to connect until it's successful?



  • The timeout only works as you expect once the database is up and running and in a fit state to "negotiate" and accept connections. It's when that "negotiation" takes too long that you get the timeout.

    However, until the database gets into that state, it's effectively not there, as if the server was down, so the timeout argument doesn't come into play.

    Your PostgreSQL cluster will probably be starting as a Windows Service.
    Services can have dependencies between them so that they start u pin the "right order". You may have to package up your script(s) so that they can be controlled through the same , Service infrastructure.

    Or you just put a call to https://serverfault.com/questions/432322/how-to-sleep-in-a-batch-file into your script and have it "wait" for a bit, in the hope that the database will be ready by the time it carries on.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2