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?
Best Answer
PostgreSQL provides you all the tools necessary to back it up in it's base install. This is what I did a few weeks ago to setup backup of hot backups of PostgreSQL instances hosted on a Windows host:
Create a user specifically for backups, let's call it 'backups'. You can use the
createuser
command from your PostgreSQL install.Give the user a password and read access to everything. This can get a bit complex. Alternatively, you can also make it a PostgreSQL superuser and enforce login restrictions as mentioned below.
Allow it to login from localhost only by using a password (mechanism 'md5'), or if you are game setup a user on your MS Windows machine and use mechanism 'ident'. You'll need to modify the
pg_hba.conf
file to enforce either of these behaviour and the restriction to login from only localhost.Create a script to use
pg_dumpall
to backup the database. The script can be called via a job setup in Task Scheduler or via a backup scheduler like Bacula. If you chose to authenticate using a password, you can specify that as an environment variable thatpg_dumpall
will read or specify a file containing the password using a different environment variable.Details of this method can be found at http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows.
I am not sure why you are using pgAdmin for automated backups of PostgreSQL. I'd love to hear your reasons considering that PostgreSQL has a way of doing it without external tools and has a well written document on the topic.