2

Help me please.. I have some questions about pgAgent.

[Environment]

DB : PostgreSQL 9.3

OS : Window 7

OS login ID : someid@mail.com

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 : someid@mail.com

pwd : [ someid@mail.com'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 ^^

arayo
  • 197
  • 2
  • 12
  • Windows event viewer says "Connection to testDB failed because of password." Then problem of testDB password? (NOT postgres DB that have pgAgent schema. Logon to postgres DB succeeded.) If so, how can I set the testDB password? – arayo Apr 19 '15 at 10:50
  • I changed connection type to 'remote' (from 'local') in Step configuration. And set connection String as 'user=postgres host=localhost port=5432 dbname=testDB password=[pgAdmin password for postgres]'. Now, job succeed. BUT nothing happend in testDB. Just job ended without error on Statistics panel. That's all. AND I want to set password in pgpass.conf. Any other idea ? – arayo Apr 19 '15 at 12:59
  • Because of someid@mail.com ? Should I create 'postgres' OS ID ? And logon pgAgent using that ? – arayo Apr 23 '15 at 13:37
  • Now I'm trying to make 'postgres' OS ID on my Windows 7. But I can not make that. For other accounts there's no problem. But for 'postgres' I can not make that. ha... not easy... – arayo May 01 '15 at 09:17
  • I guess pgAgent can not find pgpass.conf file. So I'm trying to make 'postgres' OS ID. – arayo May 01 '15 at 09:24
  • I don't need to make 'postgres' OS id. I solved ^^ – arayo May 01 '15 at 09:49

2 Answers2

5

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 someid@mail.com.

But someid@mail.com'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 '.\someid@mail.com'.

I guess pgAgent tried to find pgpass.conf in 'C:\Users\someid@mail.com\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 ^^

arayo
  • 197
  • 2
  • 12
1
SET PGPASSWORD=<PassWord>
pg_dump -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>

Source: https://wiki.postgresql.org/wiki/Automated_Backup_on_Windows

Smart Manoj
  • 5,230
  • 4
  • 34
  • 59
NavaneethaKrishnan
  • 1,213
  • 1
  • 9
  • 22