pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

All we need is an easy explanation of the problem, so here it is.

I try to configure pgAgent on Windows server 2008 to make automatic backup on the database.
pgAgent is installed on the server and the service is running normally.
I use PgAdmin3 and PostgreSql 9.3

I have already planned query using the connection string with the password in it, and it works perfectly.

Now, I try to make it by specifying the password in pgpass.conf (as said in the doc) but I always got the “Couldn’t get a connection to the database !” error.

My pgpass.conf is something like this :

175.35.105.15:5432:*:admin:***
175.35.105.15:5432:*:postgres:***
localhost:5432:*:postgres:***
localhost:5432:*:admin:***
127.0.0.1:5432:*:admin:***

I know that some line are rhetorical, it’s just that I’ve tried a lot of things.

I usually try to connect on localhost, but even in remote connection mode it doesn’t work.

Here some examples of what I’ve tried with remote connection in step configuration :

user=admin host=175.35.105.15 port=5432 dbname=db
user=postgres host=175.35.105.15 port=5432 dbname=db
user=admin host=127.0.0.1 port=5432 dbname=db
user=postgres host=127.0.0.1 port=5432 dbname=db

EDIT : in Postgres log, pgagent is connected as adminpostgres. This username isn’t in the login role…

pgpass.conf is in c:/user/Application Data/postgresql

pgAgent was installed with StackBuilder as Admin

I’ve tried to fill the “host agent” field with the content of “pga_jobagent.jagstation” but it doesn’t work either

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’ve found it !

In Windows Service, i found that it was “adminpostgres” who was running the pgAgent service.

I just had to edit the pgpass.conf in c:/user/adminpostgres/AppData/postgresql with my old config to make it works !

Method 2

This answer was perfect! I found that the service was running under the Windows user postgres, so I went into that user’s app data folder to the file (C:\Users\postgres\AppData\Roaming\postgresql\pgpass.conf), right-clicked and edited it with Notepad++, and added the line:

localhost:5432:CPLit_Tables:postgres:****

AND that was all that was needed to get rid of the error “Couldn’t get a connection to the database!”, and get on with having a running job.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply