Weekly backups of PostgreSQL database to Amazon S3

pg-aws-backups.md

Weekly backups of PostgreSQL database to Amazon S3

1. I did this as root: $ sudo su, although it may not be necessary.
2. Download AWS command line tools: $ curl https://raw.github.com/timkay/aws/master/aws -o aws
3. Install:
   • Automatically: $ perl --install aws
   • Manually: $ mv aws /usr/local/bin/ && chmod +x /usr/local/bin/aws
4. Put your AWS credentials in ~/.awssecret with the Access Key ID on the first line and the Secret Access Key on the second line: $ vi ~/.awssecret. For example:

   1B5JYHPQCXW13GWKHAG2
   2GAHKWG3+1wxcqyhpj5b1Ggqc0TIxj21DKkidjfz

5. Test AWS: $ aws ls should show existing buckets. $ aws mkdir bucket to make a new bucket.
6. Create a backup script at: $ vi ~/s3backup.sh, you may need to adjust paths and flags:

   #!/bin/bash
   TIMESTAMP=`date +%Y-%m-%d`
   DATABASE_NAME=database
   BUCKET_NAME=bucket
   /usr/bin/pg_dump --user=postgres --host=localhost ${DATABASE_NAME} > /tmp/${DATABASE_NAME}_${TIMESTAMP}.sql
   /usr/bin/aws put ${BUCKET_NAME}/${DATABASE_NAME}_${TIMESTAMP}.sql /tmp/${DATABASE_NAME}_${TIMESTAMP}.sql
   /bin/rm /tmp/${DATABASE_NAME}_${TIMESTAMP}.sql

7. Make executable: $ chmod +x ~/s3backup.sh. Test by running ~/s3backup.sh.
8. Setup crontab: $ crontab -e with contents to run a weekly backup:

   0 0 * * 0 /root/s3backup.sh