Blog

Viewing posts tagged 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/"