couldn't get a connection to the database - Postgres Job Scheduling issued (Windows based machine)

Question

I search in the internet about this issue. But couldn't find a proper answer. This link is bit closer to the solution, but it is not worked for me.

I scheduled a job in pgadmin. but the job is always got failed due to "couldn't get a connection to the database" error. When creating a job using PGAdmin, initially it is asked name, job class, host agent etc... In first stage I left blank for the field "Host Agent". (My schedule works in every minute) PGAdmin right side panel has a tab called "Statistics". Minute by minute it is updated as "failed".

Then i changed host agent to postgres. (login user)

Still the job is not working properly. (even failed status also not logged after changing host agent) Step has a simple insert query. Query does not have an issue.

Anyone can show me a direction for doing this?

Answer 1

Select connection type as "Remote" in Step and brows for server and db name. Default it is giving only below part.

user=<username> host=localhost port=5433 dbname=<dbname> 

We should have to add password manually.

password=postgres

Final connection string is like below.

user=<username> host=localhost port=5433 dbname=<dbname> password=<my password>

This works for me.....

Answer 2

I assume you are using Windows OS to run postgresql. Then on server locate the connections file C:\Users\[pgagent service username]\AppData\Roaming\postgresql\pgpass.conf and make sure that the connection string is there.

If you provided connection to the default postgres database while installing pgAgent then you won't be able to run jobs on other databases even if the user has access rights. At least in my experience I had to add connection strings for every database.