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

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.

Answer 2

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.

Answer 3

Postgres is very flexible - there is more than one way to grab a good, usable backup (and many of the best don't require you to use pgAgent - you can script them with regular OS tools).

nearora already described using pg_dumpall which may be viable for you.

The Postgres manual describes two other options which can be automated on Windows with a little scripting.

Option 2: Filesystem-Level Backup

Normally this is done by shutting down the server and grabbing the PGDATA directory.
If you can't shut down the server then contrary to what the manual says here, you can get a usable backup without shutting down the server -- Simply to take a "base backup" as you would for setting up a WAL/PITR Slave.

The result of this backup should be a copy of the PGDATA directory, plus a few WAL segments, stored in a separate location on the server that your normal filesystem-level backup processes can pick up.
You must ensure that the base backup completes prior to grabbing the files with your regular filesystem backup process, otherwise you might wind up getting an unusable backup.

---

Option 3: Backing up a slave

With this method you will need to create a slave server, either log-shipping or hot standby, as described in the Postgres docs.
When it's time to back up your cluster, shut the slave down and back up its PGDATA directory as you would for a regular filesystem backup, then restart the slave and let it catch up with the master again.

This is by far my favorite option for backing up a Postgres cluster - If you dedicate a specific slave server to be the "backups slave" you can perform backups of your cluster with zero impact on production applications using your database.

The major downsides are that it requires a slave server, and you must make sure that Postgres is stopped on the slave before you grab the PGDATA directory, and is not started again until you're done grabbing the files.
You also need to ensure that backups complete in a reasonable timeframe so the WAL segments you need to catch up to the master are still available. In practice that will only be an issue for clusters with EXTREMELY high write loads, or if you do something like a VACUUM FULL on the master while the slave is being backed up..