Help me please.. I have some questions about pgAgent.
[Environment]
DB : PostgreSQL 9.3
OS : Window 7
OS login ID : [email protected]
pgAdmin login ID : postgres
pgAgent user : postgres
pgAgent schema belongs to: postgres DB
DB that I'll treat : testDB (In Step configuration, I specified 'testDB' as Database.
Connection type is 'local'. Connection string is empty.)
DB server : local machine (my laptop computer)
I scheduled pgAgent job, but it fails to connect database.
Output message ( pgAdmin>Statistics )
[Warning] Couldn't get a connection to the database (pgAgent)
(What database? There are 2 DBs. 'postgres' and 'testDB'. And I'll treat 'testDB'. Then couldn't get a connection to which database?)
windows event viewer
Failed to create new connection to database 'testDB':'fe_sendauth: no password supplied'
My pgpass.conf
localhost:5432:postgres:postgres:[pgAdmin password for postgres]
localhost:5432:testDB:postgres:[pgAdmin password for postgres]
My pg_hba.conf
@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local all all @authmethodlocal@
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local replication @default_username@ @authmethodlocal@
#host replication @default_username@ 127.0.0.1/32 @authmethodhost@
#host replication @default_username@ ::1/128 @authmethodhost@
My pgAgent command option ( Service>pgAgent>Property>General tab )
C:\Program Files (x86)\pgAgent\bin\pgagent.exe RUN pgAgent host=localhost port=5432 user=postgres dbname=postgres
pgAgent service Logon ID ( Service>pgAgent>Property>Logon tab )
id : [email protected]
pwd : [ [email protected]'s password ]
[Conclusion]
Password setting in pgpass.conf seems wrong for anything I know. Or pg_hba.conf Or logon ID in Services>pgAgent>Logon Actually.. I really don't understand what's wrong.^^
Thank you for your help in advance ^^
Beautiful day... I solved it ^^
[Solution]
I made other OS login ID. (NOT need to be 'postgres' !!)
And put pgpass.conf in that ID's AppData\Roaming\postgresql
[Reason]
pgAgent could not find pgpass.conf in login ID's directory.
I said my OS login ID is [email protected].
But [email protected]'s real name(folder name in C:\Users) was 'SOME NAME TEST'.
That's DIFFERENT !!
When I checked logon ID in services, pgAgent's logon ID was '.\[email protected]'.
I guess pgAgent tried to find pgpass.conf in 'C:\Users\[email protected]\AppData\Roaming\postgresql'.
But there was no such directory.!!
My pgpass.conf was in 'C:\Users\SOME NAME TEST\AppData\Roaming\postgresql'
I made a other OS ID 'anyname' in casual way. (control panel>account management.)
login ID : anyname
real name : anyname ( C:\Users\anyname )
That's SAME !! ^^
And placed pgpass.conf in 'C:\Users\anyname\AppData\Roaming\postgresql'
Then changed the pgAgent's logon ID to '.\anyname' in services.
Now it works well ^^