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

On Tue, Jul 06, 2004 at 13:41:15 -0400,
Robert Fitzpatrick <robert@webtent.com> wrote:

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.

If you are using ident authentication then you can restrict usage of
various postgres usernames to various system usernames. For example
you could allow the system user root to connect as the postgres user
postgres, but not let anyone connect as postgres.