Blog

Viewing posts for the category RDS

Custom PostGreSql version ( 9.6 ) pg_dump and restore from and to remote host

Backup PostgreSQL Remote DB:

Bash Shell Script to backup RDS/EC2 PostgreSQL DB and upload to S3 weekly

#!/bin/bash
# Run as sudo. for weekly backup of db. and upload to s3 bucket.
DBHOME="/home/priyank/crontabs/dbbackups/"
BUCKETNAME="yourAWSbucket"
SCRIPTNAME="$(basename $BASH_SOURCE)"
SCRIPTFULLPATH="$(pwd)/$(basename $BASH_SOURCE)"
mkdir -p $DBHOME
chown -R postgres:postgres $DBHOME
cp $SCRIPTFULLPATH $DBHOME
SCHEMA_BACKUP="$DBHOME/$(date +%w).sql"
sudo -u postgres touch $SCHEMA_BACKUP
sudo -u ubuntu echo "" > $SCHEMA_BACKUP
sudo -u postgres PGPASSWORD="yourPGpassword" pg_dump -h localhost -p 5432 -U postgres -F p -b -v --column-inserts --data-only -f $SCHEMA_BACKUP "yourDBname"
CRONPATH="$DBHOME$SCRIPTNAME"
chmod +x $CRONPATH
FLAGCHK=0
crontab -l | grep -q "$SCRIPTNAME" && FLAGCHK=1 || (crontab -l | { cat; echo "00 23 * * * $CRONPATH"; } | crontab -)
if [ $FLAGCHK -eq 0 ]
then
apt-get install s3cmd
s3cmd --configure
fi
s3cmd put $SCHEMA_BACKUP "s3://$BUCKETNAME/dbbackups/"

Bash Script to backup RDS/EC2 MySQL DB and upload to S3 weekly

You may come across task to write cronjob that takes backup of db every day/week/month and upload to aws s3. 
Here is shell script to do that job. make sure to replace bucket name, credentials with yours.

#!/bin/bash
# Run as sudo. for weekly backup of db. and upload to s3 bucket.
DBHOME="/home/ubuntu/priyank/crontabs/dbbackups/"
BUCKETNAME="yourAWSbucket"
SCRIPTNAME="$(basename $BASH_SOURCE)"
SCRIPTFULLPATH="$(pwd)/$(basename $BASH_SOURCE)"
mkdir -p $DBHOME
chown -R ubuntu:ubuntu $DBHOME
cp $SCRIPTFULLPATH $DBHOME
SCHEMA_BACKUP="$DBHOME/$(date +%w).gzip"
sudo -u ubuntu touch $SCHEMA_BACKUP
sudo -u ubuntu echo "" > $SCHEMA_BACKUP
sudo -u ubuntu mysqldump -P <yourDBport> -h <yourDBHost> -u <yourDBUser> -p<yourDBpassword> --force --opt --databases <yourDBName> | gzip -c > $SCHEMA_BACKUP
CRONPATH="$DBHOME$SCRIPTNAME"
chmod +x $CRONPATH
FLAGCHK=0
crontab -l | grep -q "$SCRIPTNAME" && FLAGCHK=1 || (crontab -l | { cat; echo "00 23 * * * $CRONPATH"; } | crontab -)
if [ $FLAGCHK -eq 0 ]
then
apt-get install s3cmd
s3cmd --configure
fi
s3cmd put $SCHEMA_BACKUP "s3://$BUCKETNAME/dbbackups/"