Created
March 5, 2014 08:02
-
-
Save shacker/9363025 to your computer and use it in GitHub Desktop.
Get list of all dbs on remote server, dump each to its own dir on this server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# Get list of all dbs on remote server, dump each to its own dir on this server | |
export PGPASSWORD="secret" | |
HOST='example.com' | |
DBUSER='someuser' | |
BASEDIR='/var/backups/postgres/' | |
temp1='/tmp/dbdata_tmp1.txt' | |
temp2='/tmp/dbdata_tmp2.txt' | |
# ================ | |
# Get list of all dbs on this server | |
echo "\list" | psql -h $HOST -d postgres -U $DBUSER > $temp1 | |
# Extract first column of output | |
cat $temp1 | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d' > $temp2 | |
# Remove top three lines and bottom line of tempfile (garbage); extract db names into array | |
let endline=$(cat $temp2 | wc -l)-1 | |
dbnames=$(cat $temp2 | sed -n 4,${endline}p) | |
# Go for it | |
for db in $dbnames ; do | |
echo "$db" | |
outdir=$BASEDIR$db/ | |
mkdir -p $outdir # Create dir if doesn't exist | |
pg_dump -h $HOST -U $DBUSER -d $db | gzip > $outdir`date +'%m-%d-%Y'`.sql.gz | |
# Delete backups in this dir older than timestamp x | |
find $outdir -type f -mmin +4320 -delete | |
done | |
# Clean up | |
rm $temp1 $temp2 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
this looks good. if you want compression and encryption you could use a ssh tunnel to transfer the file.