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

Question

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.

Halide

Answer 1

The local entry is not supported on Windows machines. See E.20.3.1.3. Authentication.

Reject local lines in pg_hba.conf on platforms that don't support Unix-socket connections (Magnus Hagander)

Formerly, such lines were silently ignored, which could be surprising. This makes the behavior more like other unsupported cases.

Change your configuration from:

local  all    all     trust

to:

host   all    all   127.0.0.1/32  trust
host   all    all   ::1/128       trust

For more info, consult 19.1. The pg_hba.conf File.

Assignee

Answer 2

I had this problem with Postgresql version 8.3 after installing it on a Windows 7 machine. Initially it was working normally, but suddenly it stopped (probably after some Windows update). Many tentatives were made changing permissions, but it didn't put it to work. Some months later I found the problem in a file which was empty, meanwhile it should have some data. Verify this path: C:\Program Files (x86)\Postgresql\8.3\data\ (where 8.3 must be replaced to your version number). Check if the file Postmaster.pid is empty or not (open it using Wordpad or Notepad). If it is empty, rename it to _Postmaster.pid (or other name). Go to Start menu and at the command box type services.msc Press Enter Locate the Postgresql Database entry Double click on it Click on Start button If the service starts, a new Postmaster.pid will be created (with some data) You can check it in the path indicated above.

The good tip came from analysing the Event Viewer: Open Windows Explorer, select "Computer", right click on "Manage", select "Event Viewer", wait "Summary of Administrative Events" to load, open the "Error" node, locate "PostgreSQL" entry in the "Source" column and doubleclick on it. There was the tip informing bogus data in the Postmaster.pid file.

I hope this information can help you. Cheers! Ed.

Thirion

I have different issue but +1 for the Event Viewer tip! – Situla

Answer 3

The problem is that this postgres version under win7 can not handle local connections. I had to work with pg very urgently on my local computer so I did not care too much about security so maybe this is not the best solution for a live environment. But my local PC it was enough.

I removed all other uncommented lines from pg_hba.conf and left in only the following one:

host    all             all             ::1/0                 trust

After this I could connect via command line.

I do not understand why the manual tells no more about this issue. I am pretty sure many people have problem with this.

Burne

Thanks, this worked for me, although adding the following line including my local IP (connecting from local VM) did the trick:host all all 192.168.1.49/32 md5 – Ghirlandaio

This is the only solution that worked for me. Thanks! – Greensickness

Using this or any ipv4 variation in the conf file brings about the same error when I try to start postgres. – Selective

Answer 4

I've had the same problem. What worked for me was:

1. Open the postgresql.conf file (usually, it is located at C:\Program Files\PostgreSQL\9.4\data);
2. Comment the line number 147 ( shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll')

Martyry

Answer 5

In such a case the best thing is to analyse the OS logs. For windows, check the "Event Viewer" -> "Application". Such error can occur for various reasons. In my case a postgres was already running. I found out that information in the Windows Logs.

Grandpa

Answer 6

So, quick disclaimer, I now run PostgreSQL on Linux. But, years ago, I did run it on Windows. And I seem to remember this problem. I think the clue is in this line:

2013-05-29 15:07:00 MDT LOG:  local connections are not supported by this build

local connections is a Unix thing. You are using windows. So "not supported by this build". Try changing local to localhost and restart. Here's a enter link description here to a similar problem posted on the Postgres mailing list:

Forehead

Answer 7

“The Service on local computer started and then stopped ,Some services stop automatically if there are not in use by other services or programs.”

Now I will explain how to solve the Service on local computer started and then stopped, some services stop automatically if there are not in use by other services or programs.

To solve this problem we have two ways

First Way

Start --> Run --> Type Services.msc and click enter button now you will get all the services in your computer after that select your service and right click on that and go to Properties

After that open Select Log On tab in that select Local System Account and click ok now your problem will solve

Colfin

Thank you so much, man, this saved my day! Digging a little deeper, at least in my case this had to do with the folder permissions. On the original 'data' folder, NETWORK SERVICE has Full Control permission, while the folder created either through Windows Explorer or by pg_basebackup has a very different (and quite weird actually) set of permissions. This is on a Windows Server 2012 machine. – Bolin

Answer 8

I had made a mistake in postgresql.conf. I had done

max_prepared_transactions = 16*max_connections  # zero disables the feature

where before in this file there is,

max_connections=100

This cannot be done, and the following works:

max_prepared_transactions = 1600    # zero disables the feature

Tyranny