#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    Exclamation PostgreSQL 9.2.4 (Windows 7) - Service won't start, “could not load pg_hba.conf”


    I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the service stopped running. When I try to start the service now, I get a message saying :

    "The postgresql-x64-9.2 - PostgreSQL Server 9.2 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs."


    When I try running the program that should use the database server, I get this error :

    "A problem was encountered while attempting to log into or create the production database. Details: Could not connect to the server; Could not connect to remote socket. The application must now close"


    I have also encountered this error once while opening the same program :

    "A problem was encountered while attempting to log into or create the production database. Details: FATAL: could not load pg_hba.conf The application must now close."


    I have tried running the service logged on as a local system account as well as my own account (In the postgres service properties) to no avail. I also tried restarting my computer. After a lot of troubleshooting online, I learned that a good thing to check is the pg_log file. Here are the contents of the latest pg_log entry :

    2013-05-29 14:59:45 MDT LOG: database system was interrupted; last known up at 2013-05-29 14:58:01 MDT
    2013-05-29 14:59:45 MDT LOG: database system was not properly shut down; automatic recovery in progress
    2013-05-29 14:59:45 MDT LOG: record with zero length at 0/175BB98
    2013-05-29 14:59:45 MDT LOG: redo is not required
    2013-05-29 14:59:45 MDT LOG: database system is ready to accept connections
    2013-05-29 14:59:45 MDT LOG: autovacuum launcher started
    2013-05-29 15:07:00 MDT LOG: local connections are not supported by this build
    2013-05-29 15:07:00 MDT CONTEXT: line 1 of configuration file "C:/PostgreSQL/data/pg_hba.conf"
    2013-05-29 15:07:00 MDT FATAL: could not load pg_hba.conf
    2013-05-29 15:07:00 MDT LOG: local connections are not supported by this build
    2013-05-29 15:07:00 MDT CONTEXT: line 1 of configuration file "C:/PostgreSQL/data/pg_hba.conf"
    2013-05-29 15:07:00 MDT FATAL: could not load pg_hba.conf
    2013-05-29 15:09:03 MDT LOG: received fast shutdown request
    2013-05-29 15:09:03 MDT LOG: aborting any active transactions
    2013-05-29 15:09:03 MDT LOG: autovacuum launcher shutting down
    2013-05-29 15:09:03 MDT LOG: shutting down
    2013-05-29 15:09:03 MDT LOG: database system is shut down


    It seems to be having issues with the pg_hba.conf file, which looks like this :

    local all all trust
    host all all 127.0.0.1 255.255.255.255 trust
    host all all 0.0.0.0 0.0.0.0 trust


    As per many suggestions online, I tried editing the top line to a number of different alternatives (host all all trust / host all 127.0.0.1/32 trust / host all 192.168.0.100/24 trust , etc.). This made sense to me, as the log file was saying that local connections are unsupported by postgres and was also pointing to that line. However, none of my changes had any effect. I tried restarting my computer after every change but nothing made any difference.

    When I searched for examples of what a pg_hba.conf file normally looks like, the examples looked slightly different from my file. I noticed that in the PostgreSQL program file, in addition to pg_hba.conf, there was also a "20130529-150444-old-pg_hba.conf" file which looked a lot more like the examples I was finding online. This file has several lines of comments before these last few lines :

    # TYPE DATABASE USER ADDRESS METHOD

    # IPv4 local connections:
    host all all 127.0.0.1/32 md5
    # IPv6 local connections:
    host all all ::1/128 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #host replication postgres 127.0.0.1/32 md5
    #host replication postgres ::1/128 md5


    I was hoping that this was the original pg_hba.conf file and that if I replaced the new file with the contents of the old one, postgres would start working again. No such luck. I have been hoping for more error files to be logged in pg_log to see if the previously stated error had disappeared or changed to something else, but no more files have been logged.

    I have been troubleshooting online for a few days now and nothing I've found has worked. Sorry for having such a long question, but I wanted to be thorough and include all relevant information. I would appreciate it if anyone could shed some light on this problem or offer suggestions.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    This sounds more like a Windows 7 services handling sucks problem than a Postgres problem.

    That said, if there is any way you can make Postgres not a dependent service, but instead an "always on" type service, then your problem will probably go away. It looks like Windows is trying to be smart and shut services off when they aren't in use, but is not capable of spinning them up correctly when they are needed by a dependent program/service.

    The number of Postgres experts who are also Windows experts seems to be fairly low. (Consider that I haven't even met anyone who has developed anything for Windows in the last several years...) You might want to try consulting a Windows 7 forum.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Windows does not support sockets, and that's what the "local" connection is defining. Your first file is indeed invalid for Windows.

    This file:
    Code:
    # TYPE DATABASE USER ADDRESS METHOD
    
    # IPv4 local connections:
    host all all 127.0.0.1/32 md5
    # IPv6 local connections:
    host all all ::1/128 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #host replication postgres 127.0.0.1/32 md5
    #host replication postgres ::1/128 md5
    looks good and will definitily not cause the error message

    "CONTEXT: line 1 of configuration file "C:/PostgreSQL/data/pg_hba.con"

    So I guess you are not telling us everything (even if you tried).

    A good way of troubleshooting startup problems is to start Postgres from the commandline, not through the service.

    You can use the following:

    Code:
    pg_ctl.exe -D "\path\to\your\datadir" start
    to start the Postgres server. Any error message that doesn't make it into the logfile will then show up on the console.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by zxq9
    This sounds more like a Windows 7 services handling sucks problem than a Postgres problem.

    That said, if there is any way you can make Postgres not a dependent service, but instead an "always on" type service, then your problem will probably go away. It looks like Windows is trying to be smart and shut services off when they aren't in use, but is not capable of spinning them up correctly when they are needed by a dependent program/service.

    The number of Postgres experts who are also Windows experts seems to be fairly low. (Consider that I haven't even met anyone who has developed anything for Windows in the last several years...) You might want to try consulting a Windows 7 forum.
    The service is set to start automatically if that's what you mean. But I will take your advice and post this on some Windows 7 forums as well. Thanks
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by shammat
    Windows does not support sockets, and that's what the "local" connection is defining. Your first file is indeed invalid for Windows.

    This file:
    Code:
    # TYPE DATABASE USER ADDRESS METHOD
    
    # IPv4 local connections:
    host all all 127.0.0.1/32 md5
    # IPv6 local connections:
    host all all ::1/128 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #host replication postgres 127.0.0.1/32 md5
    #host replication postgres ::1/128 md5
    looks good and will definitily not cause the error message

    "CONTEXT: line 1 of configuration file "C:/PostgreSQL/data/pg_hba.con"

    So I guess you are not telling us everything (even if you tried).

    A good way of troubleshooting startup problems is to start Postgres from the commandline, not through the service.

    You can use the following:

    Code:
    pg_ctl.exe -D "\path\to\your\datadir" start
    to start the Postgres server. Any error message that doesn't make it into the logfile will then show up on the console.
    I tried replacing pg_hba.conf with the contents of that old file, but the service still won't start and no new errors have been logged. It's not that I'm getting the same error message, I'm not getting any messages at all to tell me what's wrong.

    I tried entering your suggested command into the command line, but I get this error message :

    'pg_ctl.exe' is not recognized as an internal or external command

    I apologize if I'm doing something wrong, but I'm pretty ignorant of everything to do with the command line. Instead of your command, I tried typing

    net start postgresql-x64-9.2.4

    I got this as a response :

    The postgresql-x64-9.2 - PostgreSQL Server 9.2 service is starting...
    The postgresql-x64-9.2 - PostgreSQL Server 9.2 service could not be started.

    The service did not report an error

    I hope that helps
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by fstephane
    'pg_ctl.exe' is not recognized as an internal or external command
    You need to run this in the directory where Postgres is installed.

    This is usually the "Program Files" directory.

    This is really, really basic computer knowledge.
    See here for some background information: http://bit.ly/1aJfJWG
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by shammat
    You need to run this in the directory where Postgres is installed.

    This is usually the "Program Files" directory.
    Ok, I managed to figure that out. I ran the command and got this as a response :

    LOG: unrecognized configuration parameter "redirect_stderr" in file "/PostgreSQL/data/postgresql.conf" line 582
    LOG: unrecognized configuration parameter "redirect_stderr" in file "/PostgreSQL/data/postgresql.conf" line 591
    FATAL: configuration file "/PostgreSQL/data/postgresql.conf" contains errors


    Here are the contents of postgresql.conf around those lines :

    #------------------------------------------------------------------------------
    # CUSTOMIZED OPTIONS
    #------------------------------------------------------------------------------

    # Add settings for extensions here

    #----------------------------------------------
    # New entries created by configuration utility
    # Created: 20130529-150444
    #----------------------------------------------
    listen_addresses '*'
    redirect_stderr true

    #----------------------------------------------
    # New entries created by configuration utility
    # Created: 20130529-171715
    #----------------------------------------------
    listen_addresses '*'
    redirect_stderr true
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by fstephane
    Here are the contents of postgresql.conf around those lines
    You are missing an = sign between the key and the value:
    Code:
    listen_addresses = '*'
    Additionally redirect_stderr is not a valid option for postgresql.conf

    You can find a list of all supported options in the manual: http://www.postgresql.org/docs/current/static/runtime-config.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by shammat
    You are missing an = sign between the key and the value:
    Code:
    listen_addresses = '*'
    Additionally redirect_stderr is not a valid option for postgresql.conf

    You can find a list of all supported options in the manual: http://www.postgresql.org/docs/current/static/runtime-config.html
    That worked! I deleted the redirect_stderr line and added the = sign and now the service will start! Thank you so much for your help.

    However, I am still getting an error when I try to open the program that is using postgres. It says :

    "A problem was encountered while attempting to log into or create the production database. Details: client encoding mismatch

    The application must now close"


    Any idea what that means?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by fstephane
    "A problem was encountered while attempting to log into or create the production database. Details: client encoding mismatch

    The application must now close"


    Any idea what that means?
    Well that seems like a problem caused by the application itself (probably by specifying an incorrect encoding during connecting - or expecting a different one). You will need to consult the support of that application.


    You might get a hint of what's going on if you enable logging of all statements in postgresql.conf by using:
    Code:
    log_statement = 'all'
    Don't forget to turn it off later, otherwise your log file will grow quite rapidly.
    Last edited by shammat; June 1st, 2013 at 07:47 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo