PostgreSQL automatic backup

Question

I have been trying to set up a backup script on a windows server. I have used pgAgent (scheduling for pgAdmin), to run the backup script. No problems with the backup script.

However, my jobs are not running like they should. I have set both the schedule, and the steps.

I am fairly certain, that I am running the service under a wrong user or a user without the required permissions.

I run the service like this: "C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p secret hostaddr=127.0.0.1 dbname=pgadmin user=postgres

And I get an error, telling me that there was an error with the login information, though I know it's correct. When I go under services (controlpanel --> administration --> services), I am able to start the service with the local user.

Can this be the problem?

Where can I see or change the permissions on the postgres user?

Answer 1

I setup pgAdmin this way

pgagent.exe INSTALL pgAgent -u postgres -p secret host=localhost dbname=pgadmin user=postgres

Then you need to setup the pgpass.conf file in the postgres user directory, under winxp this is in Application Data/postgres/pgpass.conf and under win7 it should be appdata/local/postgres/pgpass.conf (not 100% sure)

Its defined here http://wiki.postgresql.org/wiki/Pgpass

hostname:port:database:username:password

this allows pgAgent to access the postgreSQL server, there would be a pgpass created for your user when you remembered the password for pgAdmin.

Without this pgpass pgAgent can not access the database.