Re: Automated database backups and authentication

> I would like to backup a set of databases from a cron job. It looks like
> pg_dumpall will do the trick. The only problem is that there doesn't seem to
> be any way to pass the database password to pg_dumpall in a non interactive
> manner. I'd like to use some kind of access control on the databases, but it
> would need to be able to function non interactively.
>
> Ideally whatever I do would be able to function across a network, so that one
> database server could backup to a second - kind of a poor man's replication.
>
> I haven't found any real answers in any of the archives, or docs. Has anyone
> here done something like this or have any thoughts?
>


I just set this up on my system.

I use expect to take the backups and transfer the files to
another system using scp. I also use some python scripts
to figure out what the names of the backup files should
be (using the date for instance).

Some issues I ran into:

* Since the password will be in the expect script, you need
  to be careful with permissions on the script.

* Also, watch your umask for any temporary files you may
  create in the process.

* Expect scripts which run fine from the command line will
  mysteriously fail from cron (and just hang around not
  doing much of anything) I found that using absolute
  paths to *everything* solved most of those problems.

* I set the expect log_user variable to 0 so that I do
  not get any mail about the process.

* I used: set env(SHELL) /bin/sh
  because I was having trouble with figuring out which
  shell was going to run, and this seemed easiest.

I am using the same system to run a daily report too.

I had never used cron, and it was tough to get this
working, but now it seems pretty good.