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?

Solution

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:

1. Create a user specifically for backups, let's call it 'backups'. You can use the createuser command from your PostgreSQL install.

2. 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.

3. 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.

4. 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 that pg_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.

Comments

[Nick Taylor]

Why don't simply use PostgreSQL-Backup to create automatically backups of your database? postgresql-backup.com/postgresql-blog/backup-tool

[voretaq7]

This solution works well for small databases, but I would generally not advise using pg_dumpall for large database installations (multi-GB tables) - it acquires a lock on the tables when it dumps them, and I've had problems in the past with the dump locks interfering with production applications.