Skip to content

Instantly share code, notes, and snippets.

@tylor
Created November 7, 2012 22:55
Show Gist options
  • Save tylor/4035145 to your computer and use it in GitHub Desktop.
Save tylor/4035145 to your computer and use it in GitHub Desktop.
Weekly backups of PostgreSQL database to Amazon S3

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
@MrOlek
Copy link

MrOlek commented Jul 1, 2016

Perfect explanation, Thanks! But I'm using this tool http://postgresql-backup.com/ to make all this job automatically on a regular basis.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment