Wednesday, June 19, 2013

dumping postgres database via backup script

I came across an issue when trying to backup a database via pg_dump. The issue was that I needed to type the password. mysql will let me dump the database with the password on the command line, but postgresql does not and requires a prompt or a variable. You can use PGPASSWORD or .pgpass and I used the environmental variable.

In my BASH script, backup.sh, I have:
TIMEATSTART=$(date +%Y-%m-%d-%s) 
DIRECTORY_ARC=$USER_HOME/archive
FILENAME_DBBACKUP=db-$TIMEATSTART.gz

export PGPASSWORD=dbpassword
pg_dump -U dbuser database_name | gzip -c > $DIRECTORY_ARC/$FILENAME_DBBACKUP
The script makes a convenient backup of my database_name database to an archive/db-00000000000.gz file in my home directory.

No comments:

Post a Comment