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