Re: Database backup

Hi all,

> 7.3.2 has .pgpass where you can place the password.

That's a nice feature - haven't read up much on the 7.3 series as yet. It will
certainly remove my reliance on kludgy expect scripts to automate backups and
refreshes...

> Panchal, Ritesh wrote:
> > Hi,
> > I am trying to automate my backups using pg_dump. However, I am not
> > able to pass the password to the command. Has anybody sucessfully done
> > automated backup thru scripts?
> >
> > Thank you for your comments and suggestions.

As above - I've used "expect" in the past with success. Stored in a secure
directory that only postgres will have access to (with of course the exception
of root...) I have a "dump_prodDB" script which looks like the following:

========================== expect script ===============================
#!/usr/bin/expect

spawn pg_dump prodDB -f /var/lib/pgsql/tmp/prodDB.dump -U backup_user
set timeout -1

expect "Password:"
send "s3cr3tpa55w0rd\r"
expect eof
========================== expect script ===============================

(Where "s3cr3tpa55w0rd" is the appropriate password string or md5 password for
the backup user...)

This is in turn called by the primary backup script, which backs up all the
databases on the system but for those databases requiring passwords invokes the
appropriate script. Since I have only one database requiring a password I didn't
bother to make the script generic by database name but that's relatively easy.

The script that calls this then does a bzip2 on the database backup. This is
called as part of a Legato NetWorker savepnpc process, which then writes it off
to tape as part of the filesystem backup...

As I said, it's a kludge. But it works for what I need it to do :)

Cheers,

-Preston.

--
Oops.