Re: backup using cron

Robert Fitzpatrick wrote:
> I'm sure this has been discussed many times. I can find references to
> the problem in the archives, but decided to query the list here instead
> of sifting through archives all day.
>
> Is there a way to automate backup (perhaps using cron)? Of course, I am
> having problems when requiring passwords. I do not want to provide
> passwordless access via pg_hba.conf, then anyone could see the database
> if they know the user name, correct? I am running version 7.4.3.

I have something like this in my crontab:

$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.2882 installed on Tue Mar 2 22:49:07 2004)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
1 1 * * * /usr/local/bin/pg_db_backup

$ cat /usr/local/bin/pg_db_backup
#!/bin/sh
pg_dump --format=c --file=/home/promise/backup/promise.dump \
-Upromise promise
vacuumdb --analyze --verbose -Upromise promise \
2>/home/promise/backup/vacuum.log

You can place password details in a file (~/.pgpass) - see the manuals
(client interfaces/libpq/files) for details.

HTH
--
Richard Huxton
Archonet Ltd