Simple Script

Unlike MySQL the Postgres backup program (pg_dump) doesn't allow you to supply a password as a command line argument making automatic pg_dump commands tricky. To supply a password you have to use the -W switch which produces a password prompt. This is all well and good most fo the time when you are working from a command prompt but becomes an issue if you want to do automated backups using a cron job. The solution is to use a simple expect script which will wait for the required prompt and then supply the password.

A basic script is shown below:

#!/usr/bin/expect -f
spawn pg_dump -U foo -W -f /your/file somedb
expect "Password: "
send yourPassWord\r
wait

This script is executed like this " expect script.exp" replacing the script.exp with the name of your script or if your shell supports it and your have marked the file as executable simply ./script.exp will run it. If you need to debug the script use the -d switch like this "expect -d script.exp". Since this file will have the database password in plain text it is imperative that it is only readable by the owner (or the group it was intended for). The wait command at the end of the script is necessary because other wise the expect script will terminate as soon as it has given your password to pg_dump. This has the negative side effect of killing all child processes (eg the running pg_dump). You also have to end your password with \r so that a carriage return is sent after your password (don't use \n - this is new line and doesn't trigger the password prompt). The password can be in quotes or not it doesn't seem to matter. I haven't tested escaping but I presume it follows the standard \\ to escape \.

Advanced Script

I didn't like havign to have the password in the expect script. I would much rather keep the passwords in one place that I can then protect with my life :o). For that reason I enhanced the script a little so that it accepts four arguments specifying the file to backup to, the database to backup, the user to back up as and the password to use.

#!/usr/bin/expect -f
set file [lindex $argv 0]
set database [lindex $argv 1]
set username [lindex $argv 2]
set password [lindex $argv 3]
send "Backing up database $database to file $file\n\r"
spawn /usr/bin/pg_dump -U $username -h compost -W -f $file $database
expect "Password: "
sleep 1
send "$password\r"
wait

This script is used like this "./script.exp file.sql database username password". If you have any characters that are interpreted by the shell wrap the argument in single quotes.